COUNTs

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

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


On Thu, 30 Sep 2004 16:29:35 -0700, "mark r"
wrote:

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

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