BUILDING UNION QUERY

J

JB

I HAVE 2 QUERIES FROM 2 SEPARATE TABLES
ONE HAS THE TREATMENT SALES FOR THERAPIST BY DATE
ONE HAS THE RETAIL SALES FOR THERAPIST BY DATE
I JOIN THE INFORMATION FROM BOTH QUERIES INTO ONE USING UNION QUERY. I HAVE
TRIED USING THIS BELOW:
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblRetailSales.[Transaction Date], tblRetailSales.[Retail
Sales]
FROM tblTherapistInformation INNER JOIN tblRetailSales ON
tblTherapistInformation.TherapistID = tblRetailSales.EmployeeID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblRetailSales.[Transaction Date]) Between [BeginDate] And [EndDate]))
UNION ALL
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblWork.[Transaction Date], tblWork.[Treatment Sales]
FROM tblTherapistInformation INNER JOIN tblWork ON
tblTherapistInformation.TherapistID = tblWork.TherapistID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblWork.[Transaction Date]) Between [BeginDate] And [EndDate]));

I ONLY GET THE INFORMATION FROM THE FIRST QUERY AND NOT THE 2ND QUERY
HELP
 
J

John W. Vinson

I HAVE 2 QUERIES FROM 2 SEPARATE TABLES
ONE HAS THE TREATMENT SALES FOR THERAPIST BY DATE
ONE HAS THE RETAIL SALES FOR THERAPIST BY DATE
I JOIN THE INFORMATION FROM BOTH QUERIES INTO ONE USING UNION QUERY. I HAVE
TRIED USING THIS BELOW:
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblRetailSales.[Transaction Date], tblRetailSales.[Retail
Sales]
FROM tblTherapistInformation INNER JOIN tblRetailSales ON
tblTherapistInformation.TherapistID = tblRetailSales.EmployeeID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblRetailSales.[Transaction Date]) Between [BeginDate] And [EndDate]))
UNION ALL
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblWork.[Transaction Date], tblWork.[Treatment Sales]
FROM tblTherapistInformation INNER JOIN tblWork ON
tblTherapistInformation.TherapistID = tblWork.TherapistID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblWork.[Transaction Date]) Between [BeginDate] And [EndDate]));

I ONLY GET THE INFORMATION FROM THE FIRST QUERY AND NOT THE 2ND QUERY
HELP
Click to expand...

First off... please turn off your CAPS LOCK. It's hard to read and looks like
SHOUTING.

Then... if you open the second query by itself, not as part of the UNION, do
you see the tblWork results?
 
A

Afrosheen via AccessMonster.com

Pay attention to John closely. He's help me out with developing union queries.


Thanks John.
I HAVE 2 QUERIES FROM 2 SEPARATE TABLES
ONE HAS THE TREATMENT SALES FOR THERAPIST BY DATE
Click to expand...
[quoted text clipped - 20 lines]
I ONLY GET THE INFORMATION FROM THE FIRST QUERY AND NOT THE 2ND QUERY
HELP
Click to expand...

First off... please turn off your CAPS LOCK. It's hard to read and looks like
SHOUTING.

Then... if you open the second query by itself, not as part of the UNION, do
you see the tblWork results?
Click to expand...
 
J

John Spencer

I suspect that you have a parameter problem and that your date parameters are
not being correctly interpreted. I would try declaring the parameter types.

PARAMETERS [ENTER TYPEOFTHERAPIST] Text(255), [BeginDate] Datetime
, [EndDate] DateTime;
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist
, [LastName] & " " & [FirstName] AS [Therapist Name]
, tblRetailSales.[Transaction Date]
, tblRetailSales.[Retail Sales]
FROM tblTherapistInformation INNER JOIN tblRetailSales ON
tblTherapistInformation.TherapistID = tblRetailSales.EmployeeID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblRetailSales.[Transaction Date]) Between [BeginDate] And [EndDate]))
UNION ALL
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist
, [LastName] & " " & [FirstName] AS [Therapist Name]
, tblWork.[Transaction Date]
, tblWork.[Treatment Sales]
FROM tblTherapistInformation INNER JOIN tblWork ON
tblTherapistInformation.TherapistID = tblWork.TherapistID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblWork.[Transaction Date]) Between [BeginDate] And [EndDate]));

If that does not work then try changing the where clauses to enforce the data
type.
Between CDate([BeginDate]) And CDate([EndDate])

And if none of this works, answer John Vinson's questions.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I HAVE 2 QUERIES FROM 2 SEPARATE TABLES
ONE HAS THE TREATMENT SALES FOR THERAPIST BY DATE
ONE HAS THE RETAIL SALES FOR THERAPIST BY DATE
I JOIN THE INFORMATION FROM BOTH QUERIES INTO ONE USING UNION QUERY. I HAVE
TRIED USING THIS BELOW:
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblRetailSales.[Transaction Date], tblRetailSales.[Retail
Sales]
FROM tblTherapistInformation INNER JOIN tblRetailSales ON
tblTherapistInformation.TherapistID = tblRetailSales.EmployeeID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblRetailSales.[Transaction Date]) Between [BeginDate] And [EndDate]))
UNION ALL
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblWork.[Transaction Date], tblWork.[Treatment Sales]
FROM tblTherapistInformation INNER JOIN tblWork ON
tblTherapistInformation.TherapistID = tblWork.TherapistID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblWork.[Transaction Date]) Between [BeginDate] And [EndDate]));

I ONLY GET THE INFORMATION FROM THE FIRST QUERY AND NOT THE 2ND QUERY
HELP
Click to expand...
 
J

John W. Vinson

I suspect that you have a parameter problem and that your date parameters are
not being correctly interpreted.
Click to expand...

<d'oh!>

Thanks John. That has bitten me enough I should remember it!
 
J

JB

John,
Yes when I run both queries separately I get the correct results.
Thanks
JB

John W. Vinson said:
I HAVE 2 QUERIES FROM 2 SEPARATE TABLES
ONE HAS THE TREATMENT SALES FOR THERAPIST BY DATE
ONE HAS THE RETAIL SALES FOR THERAPIST BY DATE
I JOIN THE INFORMATION FROM BOTH QUERIES INTO ONE USING UNION QUERY. I HAVE
TRIED USING THIS BELOW:
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblRetailSales.[Transaction Date], tblRetailSales.[Retail
Sales]
FROM tblTherapistInformation INNER JOIN tblRetailSales ON
tblTherapistInformation.TherapistID = tblRetailSales.EmployeeID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblRetailSales.[Transaction Date]) Between [BeginDate] And [EndDate]))
UNION ALL
SELECT tblTherapistInformation.TherapistID,
tblTherapistInformation.TypeOfTherapist, [LastName] & " " & [FirstName] AS
[Therapist Name], tblWork.[Transaction Date], tblWork.[Treatment Sales]
FROM tblTherapistInformation INNER JOIN tblWork ON
tblTherapistInformation.TherapistID = tblWork.TherapistID
WHERE (((tblTherapistInformation.TypeOfTherapist)=[ENTER TYPEOFTHERAPIST])
AND ((tblWork.[Transaction Date]) Between [BeginDate] And [EndDate]));

I ONLY GET THE INFORMATION FROM THE FIRST QUERY AND NOT THE 2ND QUERY
HELP
Click to expand...

First off... please turn off your CAPS LOCK. It's hard to read and looks like
SHOUTING.

Then... if you open the second query by itself, not as part of the UNION, do
you see the tblWork results?
Click to expand...
 
J

John W. Vinson

John,
Yes when I run both queries separately I get the correct results.
Thanks
Click to expand...

Very odd. I can't see any reason that the query would lose data - you have
UNION ALL so it won't be trying to remove dups.

Try copying and pasting the SQL into Notepad; delete the query. If these
tables are linked from a backend, delete the link. Compact and repair the
database; relink the tables if needed; create a new query and copy and paste
the SQL. Something's evidently slightly corrupted!
 
J

JB

Well I did that and still have the problem

John W. Vinson said:
Very odd. I can't see any reason that the query would lose data - you have
UNION ALL so it won't be trying to remove dups.

Try copying and pasting the SQL into Notepad; delete the query. If these
tables are linked from a backend, delete the link. Compact and repair the
database; relink the tables if needed; create a new query and copy and paste
the SQL. Something's evidently slightly corrupted!
Click to expand...
 
J

John W. Vinson

Well I did that and still have the problem
Click to expand...

I'm baffled then!

If the data is not confidential (or if you'll trust me with it), and the
database isn't too huge, I'd be willing to take a look at it. Compact the
database (the backend if it's split), put it in a zipped folder, and email it
to jvinson <at> wysard of info <dot> com (make the obvious edits) and I'll see
if I can figure out what's wrong.
 
J

JB

John,
I emailed you the access file.
Thanks
JB

John W. Vinson said:
I'm baffled then!

If the data is not confidential (or if you'll trust me with it), and the
database isn't too huge, I'd be willing to take a look at it. Compact the
database (the backend if it's split), put it in a zipped folder, and email it
to jvinson <at> wysard of info <dot> com (make the obvious edits) and I'll see
if I can figure out what's wrong.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM in a UNION query 2
Getting only the last transaction for each customer 2
Union query 5
can't find the right query 0
Query Question 3
Union Query Edit 1
Another query too complex problem 3
Join on a UNION query 2

Top