Unique Records

[Steve the Cheater]

Hi,
I am working on a fairly complex database where parent records contain
numerous sub-records (which includes a number of actions and each action has
a unique ID). I have been asked to provide a report that will provide the
number of records that have had an action and not the number of actions i.e.
unique records. I have tried setting the properties to Unique Records, but
this did not work.
Any advice?

 

[KARL DEWEY]

I assume that your subrecords share a common identity with the parent records.
Do a totals querry on the idenity field of the subrecord table. The number
of records returned will be the quanity of parent records that have
subrecords.

 

[John Spencer]

One method

SELECT Count(ParentTable.ParentID) as CountOfRecords
FROM ParentTable
WHERE Exists (SELECT * FROM SubTable
WHERE SubTable.ParentID = ParentTable.ParentID)

IF that is too slow, then post back for alternative method to try.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County