BUILDING UNION QUERY

  • Thread starter Thread starter JB
  • Start date Start date
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
 
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?
 
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...
 
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!
 
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...
 
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!
 
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...
 
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.
 
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...
 
Back
Top