Count of specific number

[samirmehta19]

Hello, I am very new with access and I am struggling how to get a specific task done. My table has automatic ID in one column which is unique in each data entry row. There is another column which has "Batch Id" which might be repeated in some of the rows but most of the time it is unique value. I would like to get get an updated count of this "Batch id" from start to the point of entry of new entry to understand how many total batch ids entered to that point. I am open to do this in table if possible of by creating new query. Can someone help me.

[rpeare]

Create a query based on your table.

Add the BatchID
put in a criteria for the batch of >[Enter the Batch ID]
(this will find any batch ID greater than whatever you specify when you run the query)

Click the SIGMA button on your toolbar (looks like a capital E)
An additional row will be added to your query that is labeled TOTAL

Create another column that says BatchCount: batchID
in the TOTAL line put COUNT


The query will show you the batchID and how many instances of that batch ID exist in your table starting with the batchID you specify.

[samirmehta19]

Hello,
Thank you for your help. This works but partially. Actually I want to count only unique values of Batch Id and this gives me total of all batch id including duplicates. How can I do this?

Thank you in advnace.

Create a query based on your table.

Add the BatchID
put in a criteria for the batch of >[Enter the Batch ID]
(this will find any batch ID greater than whatever you specify when you run the query)

Click the SIGMA button on your toolbar (looks like a capital E)
An additional row will be added to your query that is labeled TOTAL

Create another column that says BatchCount: batchID
in the TOTAL line put COUNT


The query will show you the batchID and how many instances of that batch ID exist in your table starting with the batchID you specify.

[rpeare]

Ah so if you have

Batch_ID
11
11
11
23
23
34
34
34
34
34
34

You only want to show 3 because there are three unique batch numbers

To do that you'd have to run a summation query on just the batch_ID field which would give you this:

[code]
11
23
34
[code]

then you can either use a dcount function or another query that counts the values of this 'sub' query