Count Unique Records

[BBTMAMA]

I have two tables, Client Info and Client Visits. The first table has
general info that does not change so Client info is entered one time only.
The
second table records services provided at each client visit. The first table
has Client ID and the second has Visit ID and Client ID. During the month a
client may have several visits. Therefore the Client ID would occur several
times while each visit has a unique Visit ID. For a given month I want to
know how many different clients were seen as well as count the fields of
info. associated with them. At present I am able to get a total of Visits
but not of unique Clients. As you can see, I am a novice regarding ACCESS.

I don't want to know how many visits but rather how many different clients
were seen as well as the total for info such as age group, marital status,
etc.

Thank you for any suggestions you may have.

 

[Ken Sheridan]

To count the clients visited you'd use a subquery correlated to the outer
query e.g.

SELECT COUNT(*)
FROM ClientInfo
WHERE EXISTS
(SELECT *
FROM ClientVisits
WHERE ClientVisits.ClientID = ClientInfo.ClientID
AND YEAR(VisitDate) = [Enter Year:]
AND MONTH(VisitDate) = [Enter Month:]);

 

[John Spencer (MVP)]

You basic query would probably be something like the following

SELECT MaritalStatus, Count(MaritalStatus)
FROM [Client Info] as C
WHERE C.[Client ID] IN
(SELECT V.[Client ID]
FROM [Client Visits] as V
WHERE V.[Visit Date] Between #1/1/2004# and #1/31/2004#)
GROUP BY MaritalStatus