Use Result of count for more calculation (1 Viewer)

Dear Team,
I have used 'count', to count the number of times some records appeared in a column. Now, I want to do some calculations from the results I got from the query count. How do I do that please? Thank you.
E.g I want to add INFUSED + DAMAGED (169884+3)

EpisodeUnitStatus Count
BATCH PROD ERROR 211
DAMAGED 3
IN TRANSIT 1
INFUSED 169884
ISSUED 401
MISCELLANEOUS 9

Much appreciated.

If that is a static query based on using GROUP BY, you could do this in any of several ways.

1. Slow but unlikely to break: Do two DLookups on the query that gave you those counts. Let's call it MyCounts (because you didn't actually name the query). In VBA, this might look like:


2. More complex: You could open the query as a recordset and read it one record at a time to store variables in a VBA sequence. This would imply a lot more code and you would have to define variables ahead of time for the information you wanted to keep.

3. Less complex (maybe) and slower, but easier conceptually: You could have just used a DCount to dynamically count the information you wanted from your main table, as

Dear The_Doc_Man,
Thank you very much for your reply. My knowledge of access is very limited. I haven't created any VBA in Access. Do i need to go in "build>query> then put the above code ?
Thank you. Much appreciated.

That wasn't a query.

Where are you displaying those numbers? On a form or report? Or was that the raw "counting query" output? Without more context, I can't answer your question.

I am very new to access. I have a created a database on blood stock. Then I have used "design a query", did a "count Query". That's where I got the results I showed you. I do not use form or report. Is it better to use form or report, because after I have pulled this data, I export it to Excel.

It is not a matter of "better" or "worse" - thought it might be a matter of "more" or "less" appropriate for the task.

Here is the issue. Your wizard-built "COUNT" query gives you, in essence, the COUNT of records for each value of your status field using a GROUP BY clause (which is a type of sorting). Now you want something that ISN'T a simple grouping - because it sums TWO simple groups. You can't do that all at once in the same simple query because "simple grouping" and "not-so-simple grouping" makes it an apples and oranges situation.

You COULD write a query similar to this (assuming the first query is called Query1 for lack of more inspired names...)


You have to watch out for names, though. If your count-query's count field name actually is COUNT, that is a reserved word and you need to use the AS syntax to give it a new name in the original query. Then in the second query you could use the assigned name for the summation.

If you are not up on the AS syntax, here is how you might use it.


The assigned name is then going to appear in the appropriate column when you display the query AND you can refer to that name in subsequent queries that use your counting query as a data source.