Adding up counted records to get a total (1 Viewer)

[UltimateNeo]

Hello, I am trying to add up records with the quantity of and then if the quantity is greater than 1 add that to the count. So the single record is counted as many times as the quantity says. If i have 12 records all with quantity 1 then that would be 12 and then if i had 1 record that had a quantity of 4 that would be counted 4 times making the total 16 not 13. I am trying to put this in a textbox on a from using dcount and dsum or dlookup to no avail.

in the query i am looking for a name from my combobox and a task from my textbox formula so it is filling in the criteria for the query and then displaying the result.

Any help with the totals is much appreciated

=DCount("*","Daily","[ID] = '" & [Forms]![CompletedStatsF]![cboinspector] & "' AND [Tasks] = 'Visual Inspection'") I have not got my current formula for doing the qty here. This is what i started with and tried a few things to no avail.

 

[June7]

What result do you want - 16 or 13?

If you want 13 then you need to build a query that returns DISTINCT something. Then you would do DCount() on that query.

Provide sample data and desired output. You can build tables in post (click the 3 dots on toolbar to see more options) or copy/paste from Excel or Access.

 

[UltimateNeo]

Not sure how to ad da database file, i want to add up all the finals which would be 5 even though there is 3 records. Normally i count the records to provide a total, but if the qty is >1 then that will count in this case as 3 records instead of 1. Hope this helps clarify things

 

[Gasman]

You cannot use DCount() can you?, that would just count the number of records. The clue is in the name.
You would need DSum()
Put the criteria into a string variable, then you debug.print it until you get it correct, then use that as the criteria in the function.

 

[theDBguy]

View attachment 113917

Not sure how to ad da database file, i want to add up all the finals which would be 5 even though there is 3 records. Normally i count the records to provide a total, but if the qty is >1 then that will count in this case as 3 records instead of 1. Hope this helps clarify things

Hi. Welcome to AWF!

I'm not sure I follow. Why is the total 5 not 6 or 4?

 

[Gasman]

View attachment 113917

Not sure how to ad da database file, i want to add up all the finals which would be 5 even though there is 3 records. Normally i count the records to provide a total, but if the qty is >1 then that will count in this case as 3 records instead of 1. Hope this helps clarify things

None of those are Visual Inspection either?

 

[cheekybuddha]

Not sure how to ad da database file, i want to add up all the finals which would be 5 even though there is 3 records. Normally i count the records to provide a total, but if the qty is >1 then that will count in this case as 3 records instead of 1. Hope this helps clarify things

=DSum("Qty", "YourQueryName", "Tasks='Final'")
(Should return 5)

 

[UltimateNeo]

You cannot use DCount() can you?, that would just count the number of records. The clue is in the name.
You would need DSum()
Put the criteria into a string variable, then you debug.print it until you get it correct, then use that as the criteria in the function.

I tried with iifs dsum and then a dcount as i want the total number of records but it had issues with the formula

 

[plog]

I tried with iifs dsum and then a dcount as i want the total number of records but it had issues with the formula

It's like your mind is stuck on doing this with a DCount and you can't actually see what you truly want. The number of records is irrelevant, you really just want the sum of the Qty field of all records meeting your criteria. I defy you to provide an example that can counter that last sentence.

Use a Dsum. And there's no need for an IIf, use the criteria argument of the Dsum.

 

[UltimateNeo]

=DSum("Qty", "YourQueryName", "Tasks='Final'")
(Should return 5)

Thanks i will give it a try

 

[UltimateNeo]

It's like your mind is stuck on doing this with a DCount and you can't actually see what you truly want. The number of records is irrelevant, you really just want the sum of the Qty field of all records meeting your criteria. I defy you to provide an example that can counter that last sentence.

Use a Dsum. And there's no need for an IIf, use the criteria argument of the Dsum.

Thanks i think i have it now. it was because i was adding up records then added a qty field and got stuck on counting records

 

[UltimateNeo]

Hi. Welcome to AWF!

I'm not sure I follow. Why is the total 5 not 6 or 4?

Because i was only counting the tasks that equal final, sorry about not making that clear