Unique Records

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?
 
K

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top