Unique Records

  • Thread starter Thread starter Steve the Cheater
  • Start date Start date
S

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?
 
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.
 
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
 
Back
Top