Limiting on a Union Query

[Guest]

I want to bring in all of the client numbers from 2 tables (Training Attendees and CounselingSessionInfo) but only those that fall in a certain date range. I was advised earlier to do a Union Query with limitations. But, the Training Attendee table is linked to the Training Session Info table and that's the table with the date in it; the training attendee table only has the client number, last name, attendee number, and training session numbers. Below is what I tried to use for this Union Query, but it brings back a prompt asking to specifiy the training start date. It brings the Counseled clients with no problem. I've doubled checked all of the spelling and everything is spelled right so I think it's in the SQL statement. If someone could take a look at this and let me know what I did wrong I would greatly appreciate it. Thanks

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

 

[Ken Snell]

In the second part of your query, you reference TrainingSessionAttendees as
the source table, but your WHERE clause uses TrainingSessionInformation.
That's why ACCESS is asking you for thie info.

You need to join the two tables in the query. Try this:

SELECT ClientNumber FROM CounselingSessionInformation WHERE
(CounselingSessionInformation.[CounselingDate]) BETWEEN #10/01/2003# AND
#12/31/2003#
UNION SELECT ClientNumber FROM TrainingSessionAttendees INNER JOIN
TrainingSessionInformation ON TrainingSessionAttendees.[AttendeeID] =
TrainingSessionInformation.[AttendeeID] WHERE
(TrainingSessionInformation.[StartTrainingDate]) BETWEEN #10/01/2003# AND
#12/31/2003#;

I've used AttendeeID as the name of the linking field; replace it with
whatever is the linking field.

--
Ken Snell
<MS ACCESS MVP>

I want to bring in all of the client numbers from 2 tables (Training

Attendees and CounselingSessionInfo) but only those that fall in a certain
date range. I was advised earlier to do a Union Query with limitations.
But, the Training Attendee table is linked to the Training Session Info
table and that's the table with the date in it; the training attendee table
only has the client number, last name, attendee number, and training session
numbers. Below is what I tried to use for this Union Query, but it brings
back a prompt asking to specifiy the training start date. It brings the
Counseled clients with no problem. I've doubled checked all of the spelling
and everything is spelled right so I think it's in the SQL statement. If
someone could take a look at this and let me know what I did wrong I would
greatly appreciate it. Thanks

SELECT ClientNumber FROM CounselingSessionInformation WHERE

(CounselingSessionInformation.[CounselingDate]) BETWEEN #10/01/2003# AND
#12/31/2003#

UNION SELECT ClientNumber FROM TrainingSessionAttendees WHERE

(TrainingSessionInformation.[StartTrainingDate]) BETWEEN #10/01/2003# AND
#12/31/2003#;

 

[Guest]

I tried the statement you gave me, but got an error that said 'Type Mismatch in Expression'. This is what I have. I have an auto generated Partner Training Number field in the Training Session Table that is linked to the Attendees table to each attendee. Where would the mismatch be?

SELECT ClientNumber FROM CounselingSessionInformation WHERE (CounselingSessionInformation.[CounselingDate]) BETWEEN #10/01/2003# AND #12/31/2003#
UNION SELECT ClientNumber FROM TrainingSessionAttendees
INNER JOIN TrainingSessionInformation ON TrainingSessionAttendees.[PartnerTrainingNum] = TrainingSessionInformation.[PartnerTrainingNum] WHERE
(TrainingSessionInformation.[StartTrainingDate]) BETWEEN #10/01/2003# AND #12/31/2003#;

 

[Guest]

Never mind, I figured it out. I have 2 unique values and they were linked on the gov't generated EDMIS # and not our client partner #, now it works. Thank you.

 

[John Vinson]

I want to bring in all of the client numbers from 2 tables (Training Attendees and CounselingSessionInfo) but only those that fall in a certain date range. I was advised earlier to do a Union Query with limitations. But, the Training Attendee table is linked to the Training Session Info table and that's the table with the date in it; the training attendee table only has the client number, last name, attendee number, and training session numbers. Below is what I tried to use for this Union Query, but it brings back a prompt asking to specifiy the training start date. It brings the Counseled clients with no problem. I've doubled checked all of the spelling and everything is spelled right so I think it's in the SQL statement. If someone could take a look at this and let me know what I did wrong I would greatly appreciate it. Thanks

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

The second clause of your UNION says you're looking for data FROM the
table named TraningSessionAttendees - but then in the WHERE clause of
the query you're asking about a different table, one that the query
knows nothing about - TrainingSessionInformation!

You will need to Join the TrainingSessionAttendees table to the
TrainingSessionInformation table in order to pick up the date field. I
don't know your table structure so I'm not sure what field you'ld use
for the join (TrainingSessionID perhaps) but give that a try:

SELECT ClientNumber FROM CounselingSessionInformation WHERE
(CounselingSessionInformation.[CounselingDate]) BETWEEN [Enter start
date:] AND [Enter end date:]
UNION SELECT ClientNumber FROM TrainingSessionAttendees INNER JOIN
TrainingSessionInformation ON
TrainingSessionAttendees.TrainingSessionID =
TrainingSessionInformation.TrainingSessionID
WHERE (TrainingSessionInformation.[StartTrainingDate]) BETWEEN [Enter
start date:] AND [Enter end date:]

(Note that I also changed the criteria so you're prompted, rather than
having to reedit the query every time the date range changes).