Query

  • Thread starter Thread starter Desmond
  • Start date Start date
D

Desmond

I have a database that is designed as follows: there are
two big tables: one with demographic information about
people, another that specifies encounter information each
time these people come to the office. Each person has a
primary key, and the two tables are linked by this key.

I am trying to write a query that will let me know how
many unique people we see, as opposed to how many total
office visits.

I have written a query where the number I want to report
ends up being the number of records in the resulting
query table, but I can't seem to get the query to
actually give me that number.

Please help
 
SELECT COUNT(*)
FROM Demographic as D
WHERE D.PersonKey IN
(SELECT E.PersonKey
FROM Encounter as E
WHERE E.SomeField = SomeCriteria)

You may not need the where clause if all you want is to count all persons that
have ever had a visit.

Another way to do this is to use an Exists clause.

And yet another is to nest or stack queries.

QueryOne:
SELECT DISTINCT D.PersonKey
FROM Demographic as D INNER JOIN Encounter as E
On D.PersonKey = E.PersonKey

Save that and then use it in a summary query.

SELECT Count(Q.Personkey)
FROM QueryOne as Q
 
Untested:

SELECT Count(PersonID]
FROM
(
SELECT DISTINCT PersonID
FROM tblEncounter
)

should give you the "distinct" count of PersonIDs that
exist in tblEncounter.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top