Querying by Primary Key SSN with Count

I have created a form keeping track of "Clients", primary keyed by SSN.
Another form was created to keep track of "Client Cases" each time a
client comes in seeking help. This Cases form is primary keyed by an
autonumber.

Because the Client Cases Form already have autonumber, at any given
point, I can have the total number of cases up to date. However, the
ultimate goal of this DB is to be able to keep track of the total
number of unique, non-repeating CLIENTS that have a CASE within a
certain time frame.

This is where I am stumbling. I am not sure what I can do to be able to
query exactly what I wanted as mentioned above. Any help would be
appreciated!

Dear Wendy:

The details are a bit sparse, but I'll try to construct a typical query for
this. I'm going to query just the Clients table and filter to those with a
Case between selected dates.

SELECT SSN
FROM Clients
WHERE EXISTS (SELECT *
FROM Cases
WHERE Cases.SSN = Clients.SSN
AND Cases.CaseDate BETWEEN
[Enter starting date:] AND [Enter ending date:]

This doesn't give you any information about the cases, not how many, what
their dates are, nothing like that. You'd need to JOIN to get that. It
just lists the SSN of every client satisfying the contiditon that they have
a Case within the range of dates. You could add more information to this.
It's a good thing to have a starting point at least (I hope you'll think
so).

You need to check and correct the table and column names I've used.

Tom Ellison


"Wendy Lee" wrote in message
ups.com...
I have created a form keeping track of "Clients", primary keyed by SSN.
Another form was created to keep track of "Client Cases" each time a
client comes in seeking help. This Cases form is primary keyed by an
autonumber.

Because the Client Cases Form already have autonumber, at any given
point, I can have the total number of cases up to date. However, the
ultimate goal of this DB is to be able to keep track of the total
number of unique, non-repeating CLIENTS that have a CASE within a
certain time frame.

This is where I am stumbling. I am not sure what I can do to be able to
query exactly what I wanted as mentioned above. Any help would be
appreciated!

You really should set up your table relationship first with your table of
clients related one-to many to a table of cases, since each client may have
more than one case. Include your SS# as the forign key in your cases table
keeping your auto number primary key in the cases table.

"Wendy Lee" wrote:

I have created a form keeping track of "Clients", primary keyed by SSN.
Another form was created to keep track of "Client Cases" each time a
client comes in seeking help. This Cases form is primary keyed by an
autonumber.

Because the Client Cases Form already have autonumber, at any given
point, I can have the total number of cases up to date. However, the
ultimate goal of this DB is to be able to keep track of the total
number of unique, non-repeating CLIENTS that have a CASE within a
certain time frame.

This is where I am stumbling. I am not sure what I can do to be able to
query exactly what I wanted as mentioned above. Any help would be
appreciated!

Hi Jeff,

Thanks for your helpful reply!

Rgds,
Wendy

Dear Tom,

Thanks for your help! I knew I was a little vague on the details, it
was some efforts on my side trying not to write a whole term paper in
all. x) I've set up 2 tables, 1 to enter client information and another
to enter case information. My requirements are to find out how many
unique clients I have, keeping track of all the cases at the same time.
Because I've set the primary key to the client's table as non
duplicating, which is the client's SSN, I realized I can just keep
track of the non-repeating clients referring back to that table.
Originally, my logic was referring back to the cases table, thinking
how it is possible that I can pull out all the unique SSN's. Well, if
you or anyone else have other suggestions, please do share them.

Much appreciated,
Wendy