BUILDING UNION QUERY

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

On Fri, 19 Mar 2010 15:45:01 -0700, JB wrote:

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

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?


--

John W. Vinson [MVP]

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


Thanks John.

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

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?


--
Message posted via http://www.accessmonster.com

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

JB wrote:
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

On Sat, 20 Mar 2010 09:22:04 -0400, John Spencer wrote:

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

d'oh!

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

John W. Vinson [MVP]

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

"John W. Vinson" wrote:

On Fri, 19 Mar 2010 15:45:01 -0700, JB wrote:

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

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?


--

John W. Vinson [MVP]
.

On Mon, 22 Mar 2010 09:14:01 -0700, JB wrote:

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


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!
--

John W. Vinson [MVP]

Well I did that and still have the problem

"John W. Vinson" wrote:

On Mon, 22 Mar 2010 09:14:01 -0700, JB wrote:

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


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!
--

John W. Vinson [MVP]
.

On Mon, 22 Mar 2010 14:46:07 -0700, JB wrote:

Well I did that and still have the problem

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 W. Vinson [MVP]

John,
I emailed you the access file.
Thanks
JB

"John W. Vinson" wrote:

On Mon, 22 Mar 2010 14:46:07 -0700, JB wrote:

Well I did that and still have the problem

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 W. Vinson [MVP]
.