Re: Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 07/27/04 

Date: Mon, 26 Jul 2004 20:49:48 -0400

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

Desmond wrote:
>
> 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

Relevant Pages

  • Re: Deleting primary key leads to strange query results ?!?
    ... Do you have an ORDER BY clause in the query's SQL statement? ... the query is not sorting the data at all, but rather is defaulting to a sort ... order "from the table" (which often is the primary key field in the table, ... I have a GROUP BY query that returns results grouped ...
    (microsoft.public.access.queries)
  • Re: Fetching "n" number of records at a time in Perl DBI !
    ... I had to do this kind of thing to implement 'record paging' in ... Run a query to *only* select primary keyfrom the records ... A table can only have one primary key, so I assume you are talking about ... unless you explicitly add an 'order by' clause and list all relevant ...
    (perl.beginners)
  • Re: Sort
    ... query with the appropriate ORDER BY clause. ... you want in whatever order you want in the ORDER BY clause. ... > the primary key (keeping in mind the table is already created without ... and then use my primary key as my secondary sort field? ...
    (microsoft.public.access.dataaccess.pages)
  • Re: ADO.NET create table
    ... But I had always an index because if I looked on the Query Analyser on the ... So should I think that a PRIMARY KEY is equivalent as an INDEX? ... >> including the primary key in the where clause. ... >>> If you are working with a SQL Mobile database, ...
    (microsoft.public.dotnet.framework.compactframework)
  • RE: Using an option group to display a subform
    ... you can add the WHERE clause to your query for filtering the records ... Did you encounter any problem when ...
    (microsoft.public.access.forms)