Query Counting Well - Summing Badly!

[daievans]

strSQL2 = "SELECT COUNT(" & strTable & ".[ID]) AS CountOfID, CCur((DSum('[Balance]','[" & strTable & "]'))) AS SumBal, " & strTable & ".[Processedfile] " & _
                         "INTO [tblTempC&B] " & _
                         "FROM " & strTable & " " & _
                         "WHERE (((" & strTable & ".[Processedfile]) = '" & strFile & "' ))" & _
                         "GROUP BY " & strTable & ".[Processedfile]; "

This make table query creates a temporary table that counts the number of records procesed and sums their value, all based on the name of the file processed.

It counts the number of records perfectly, but sums for all the records in the table - not only for the name of the file processed. I've got quirky logic, but I can't see what it is ...

Virtual Chocolate biscuits for any help

 

[pbaldy]

Without a criteria in the DSum(), it's doing what you've told it to. Curious why you didn't use a Sum() like you used Count()?

 

[daievans]

Thanks Pbaldy - that's how I resolved it by using SUM ...

The DSum came from my method of creating the Query in the Design mode and then "copying" the SQL to my VBA .... What can I say, I'm an Access dilettante!

Thanks again!

 

[pbaldy]

No problemo!