bringing info in from 2 tables

[Guest]

I have 2 separate tables-Counseling session info and trainee attendee. They are both linked to the client info table (which has each clients demographic info). What I need to do is find out the total # of clients (counseled and trained) from a quarter. I am trying to develop a query or a report that will combine this info but I am having troubles designing it. I want clients, unduplicated (many may be trained or counseled more than once a quarter) pulled together so that I have the total clients that we served for a quarter. When I try to do it, I end up with only the clients that are listed as both trained and counseled rather than all of them. Once I can get a query or a report to list them together, then I need to use the client information table to pull the demographics on each client. I do have dates in the client info table, but I can't use that for the total clients because many clients may be counseled or trained while they first became a client 2 years earlier and that is the date on their intake (client information). If anyone can help me I would really appreciate it.
The field that are the primary in the client information and what I am trying to bring together are named Client_Number
The tables are: Counseling_Info & Training_Attendees_

 

[Dan McClelland]

Ok, I'm going to make a couple of assumptions. If my
assumptions are right, this should work.

Each row in your clients table represents 1 client and
each row is unique from the rest in some way. Perhaps you
have a client number field. Each row in your clients
counselled table represents a session of counselling for a
client. The two tables are linked by using a common
field, such as client number.

The query should be based on the clients table only, and
need not include the clients counselled table. If at this
point, you included a count on client number, your result
should be a total count of ALL your clients. In order to
have it only count clients who were counselled, you need a
subquery in your criteria.

Here's what the SQL would look like (be sure to change it
so the table and field names match yours):
SELECT Count(Clients.ClientNumber) AS CountOfClientNumber
FROM Clients
WHERE ((Clients.ClientNumber) In (SELECT ClientNumber FROM
ClientsCounselled))


This causes your count of clients to be limited to only
those which appear in the clients counselled table. If
you further want to limit your counselling sessions to a
particular time frame, you can add a WHERE clause at the
end of the SELECT statement (or build the subquery
separately and copy/paste the SQL.

Then do another query to count clients trained.

-----Original Message-----
I'm trying to set up queries to count...but when I set

the Total section to count for the Client Number, it
brings back a table with numbering 1-9 (some listed more
than once), but there are a lot more than 9 different
clients that were counseled. I'm confused as to how I'm
supposed to have it count the number of clients,
eliminating the duplicates.

 

[Guest]

I tried to get the query to work before entering in the date specification. But when I try to run it, it asks for a specific client number to look for. If I enter in no number, it brings back a 0 response. If I enter in a certain Client Number, then it brings back however many of those there are. What am I doing wrong? This is what I have

SELECT Count (ClientList.ClientNumber) As CountOfClientNumbe
FROM [ClientList
WHERE ((ClientList.ClientNumber) In (SELECT ClientNumber FROM CounselingSessionInformation)

 

[Dan McClelland]

Typically, when you attempt to run a query and are
prompted with a parameters box (client number in your
case), it is because it does not recognize client number
as a field. Be sure you've referenced this field
properly, and that it exists.

You referenced the client number field twice, in the
ClientList table and the CounselingSessionInformation
table. Does this field exist in both tables? Does it
perhaps have a different name or spelling in one of the
tables? I suspect the SELECT statement in your WHERE
clause is where the problem is. That SELECT statement
must be able to be run by itself in order for it to be a
subquery in your WHERE clause.

-----Original Message-----
I tried to get the query to work before entering in the

date specification. But when I try to run it, it asks for
a specific client number to look for. If I enter in no
number, it brings back a 0 response. If I enter in a
certain Client Number, then it brings back however many of
those there are. What am I doing wrong? This is what I
have.

SELECT Count (ClientList.ClientNumber) As CountOfClientNumber
FROM [ClientList]
WHERE ((ClientList.ClientNumber) In (SELECT ClientNumber

FROM CounselingSessionInformation))

 

[John Vinson]

I went back through and double checked the spelling of my tables and fields and that fixed the prompt for a Client Number. But now I tried to set it to specify a date range and I get the same prompt for a date...and I know the tables and field names are right. This is where i put the date specification. Is it in the wrong place?

SELECT Count(ClientList.ClientNumber) AS CountOfClientNumber
FROM ClientList
WHERE ((([ClientList].[ClientNumber]) In (SELECT ClientNumber FROM CounselingSessionInformation))) And ((CounselingSessionInformation.[CounselingDate]) Between #10/01/2003# And #12/31/2003#)

Almost right. Your Subquery is mixed up with your main query. I'll get
rid of some of Access' extra parentheses to clarify (Access will put
them back if you go into the query grid view):

SELECT Count(ClientList.ClientNumber) AS CountOfClientNumber
FROM ClientList
WHERE [ClientList].[ClientNumber] In
(SELECT ClientNumber FROM CounselingSessionInformation WHERE
CounselingSessionInformation.[CounselingDate] Between #10/01/2003# And
#12/31/2003#);

 

[Guest]

That Appears to have worked...thank you very much!

 

[Guest]

Actually-I have 1 more question. That works for my Counseled Clients. My trained clients work a little differently and I just wanted to verify how I would set up the trained clients. I have training sessions that are linked to a separate table which just contains the client number, unique training attendee number, edmis number, and training session number. In the form, it's a subform of the training session form. The subform/table is where the actual clients are listed. Should I set the orginal table as the client info table with the look up in the training attendee table and search for the date list in the training session table?

 

[John Vinson]

Actually-I have 1 more question. That works for my Counseled Clients. My trained clients work a little differently and I just wanted to verify how I would set up the trained clients. I have training sessions that are linked to a separate table which just contains the client number, unique training attendee number, edmis number, and training session number. In the form, it's a subform of the training session form. The subform/table is where the actual clients are listed. Should I set the orginal table as the client info table with the look up in the training attendee table and search for the date list in the training session table?

I'm not at all sure I understand you here. The Forms are *totally
irrelevant* with regard to how the tables are related; and Lookup
fields are to be avoided like the plague they are (see

http://www.mvps.org/access/lookupfields.htm

for a critique). If the Trained table is structured like the Counseled
table (with a foreign key ClientID), with a one to many relationships
to the client table, you'ld set up the queries in the same way.

 

[Guest]

I figured out how to count the trained clients, it was set up similarily. But as I figured these out, I realized that this isn't solving my problem because it's counting counseled and trained clients separately. There are many clients that are both counseled and trained in a quarter but I only want to count them once, not twice. And I need to do more than just count them, I somehow need to get a list of all the client numbers in one place so that I can run a query to pull the client demographic information on each. Because after I have a count, I need to know how many of that count are each type of race, are business owners, are male or female, etc... Is there a way to have a query pull (in 1 listing) all clients from both counseled and training attendee tables but to sort out the duplicates? This is really what I need. Once I can get that, I can count by client number...But I somehow need to get Access to generate these all together.

 

[Guest]

I got the Union Query to 1/2 work. It pulls in the counseling information, but it doesn't recognize the date specification for the training. I need to pull the attendees from the attendee table but the date is in the Training Session Table which is linked to the attendees table by the 2 unique training numbers. This is what I have. I double checked spelling of fields and tables. Can you see anywhere that might be created wrong? Thanks

SELECT ClientNumber FROM CounselingSessionInformation WHERE (CounselingSessionInformation.[CounselingDate]) BETWEEN #10/01/2003# AND #12/31/2003
UNIO
SELECT ClientNumber FROM TrainingSessionAttendees WHERE (TrainingSessionInformation.[StartTrainingDate]) BETWEEN #10/01/2003# AND #12/31/2003#