AOB
Registered User.
- Local time
- Today, 20:49
- Joined
- Sep 26, 2012
- Messages
- 627
Hi guys,
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.
This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Here is the piece of the code where the count is calculated / incremented :
Any suggestions why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
Thanks
Al
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.
This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Here is the piece of the code where the count is calculated / incremented :
Code:
Public lngNewBalancesTBI As Long [COLOR=green]' Defined in a separate module...[/COLOR]
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
....
strSQL = "SELECT [COLOR=red]DISTINCT[/COLOR] [tblBalanceHolding].[BalanceDate], [tblBalanceHolding].[AccountID] " & _
"FROM (([tblBalanceHolding] " & _
"INNER JOIN [tblAccounts] ON [tblBalanceHolding].[AccountID] = [tblAccounts].[AccountID]) " & _
"INNER JOIN [tblCurrencies] ON [tblAccounts].[CcyID] = [tblCurrencies].[CcyID]) " & _
"INNER JOIN [tblRates] ON [tblBalanceHolding].[BalanceDate] = [tblRates].[RateDate] " & _
"AND [tblAccounts].[CcyID] = [tblRates].[CcyID] " & _
"WHERE " & _
"([tblCurrencies].[Emerging] = True " & _
"AND " & _
"(Abs([tblBalanceHolding].[Amount]*[tblRates].[FXRate])) >= " & _
DLookup("[Threshold]", "[tblThresholds]", "[CurrencyGroup]='Emerging'") & ")" & _
" OR " & _
"([tblCurrencies].[Emerging] = False " & _
"AND " & _
"(Abs([tblBalanceHolding].[Amount]*[tblRates].[FXRate])) >= " & _
DLookup("[Threshold]", "[tblThresholds]", "[CurrencyGroup]='NonEmerging'") & ")"
Set dbs = CurrentDb
With dbs
Set rst = .OpenRecordset(strSQL)
With rst
If .RecordCount > 0 Then
.MoveLast
lngNewBalancesTBI = lngNewBalancesTBI + .RecordCount
End If
.Close
End With
End WithAny suggestions why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
Thanks
Al
)
