VB DCount per ID instead of whole table?

[chris-uk-lad]

Hi all,

Im trying to count the number of records i have within a table (per unique ID) where the content = "Y".

Ive not been having much luck though, i was trying DCount but it only counts the number of ALL records within the table where Archieve = "Y". I actually want to count all the "Y" occurances(SEQNUM) for each group of Reference Numbers (REFNUM).

E.g I have columns REFNUM, ARCHIEVE, SEQNUM. Archieve is Y or N input, REFUNM is a Unique case id (holds duplicates), and SEQNUM displays the count of the duplicates.

I currently use a recordset to insert the values into .Field("Name").value records, but im tryingto create a column that shows the max per REFNUM and cannot use a count / dcount

All help appreciated

 

[vbaInet]

You can still use the DCount, use the GROUP BY in the domain. Then in the where clause set it to Y, that will remain constant. Then to get each group you loop through every distinct occurence and apply it to the DCount then return the value.

Do you get the idea?

You could also use a recordset, which would be faster.