COUNTs

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

Table1 is a "child table". It has 50 records.
Fields are:

ID
date
detail

A special consideration for me is that more than one
record could have the same ID. 36 records have a date
equal to forms!form1!dateholder but 4 records have
duplicate IDs.

I want to count the unique-ID-records and get one single
number returned (32). This query will be a source for a
form which only displays the number of records equal to
forms!form1!dateholder

Select DISTINCTROWS ID, Count(table1.ID)
from Table1
Where date = forms!form1!dateholder

This doesnot seem to work. How do I code this? I don't
understand COUNT and DISTINCTROW.


Thanks
 
Dear Mark:

SELECT ID, Count(table1.ID) AS CountForID
FROM Table1
Where date = forms!form1!dateholder
GROUP BY ID

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top