Union Query??

  • Thread starter Thread starter Ernst Guckel
  • Start date Start date
E

Ernst Guckel

Hello,

I am having a bit of trouble here... I have 3 queries that are Identical
with a few exceptions but the design is the same... Each looks like this:

qryDayPart1
SELECT tblSales.SalesDate, Sum([Sales]/100) AS DayPart1,
Sum(tblSales.Transactions) AS DayPartCust1
FROM tblSales
WHERE (((tblSales.SalesTime) Between DayStart(1) And DayStop(1)))
GROUP BY tblSales.SalesDate;


qryDayPart2 and 3 are the same except DayStart and DayStop call with 2 and 3
and the Field names are DayPart2 and DayPart3. I am looking to combine this
into a query that results in:

SalesDate, DayPart1, DayPart2, DayPart3...

Any ideas?

Ernst.
 
No you don't want a union query. You need to join the three queries in one
query and use a normal Select query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 INNER JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
INNER JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;
 
And, in case there might not be matching records in the second and third
queries:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 LEFT JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
LEFT JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;

--

Ken Snell
<MS ACCESS MVP>



Ken Snell (MVP) said:
No you don't want a union query. You need to join the three queries in one
query and use a normal Select query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 INNER JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
INNER JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;
--

Ken Snell
<MS ACCESS MVP>





Ernst Guckel said:
Hello,

I am having a bit of trouble here... I have 3 queries that are
Identical
with a few exceptions but the design is the same... Each looks like this:

qryDayPart1
SELECT tblSales.SalesDate, Sum([Sales]/100) AS DayPart1,
Sum(tblSales.Transactions) AS DayPartCust1
FROM tblSales
WHERE (((tblSales.SalesTime) Between DayStart(1) And DayStop(1)))
GROUP BY tblSales.SalesDate;


qryDayPart2 and 3 are the same except DayStart and DayStop call with 2
and 3
and the Field names are DayPart2 and DayPart3. I am looking to combine
this
into a query that results in:

SalesDate, DayPart1, DayPart2, DayPart3...

Any ideas?

Ernst.
Click to expand...
Click to expand...
 
This works great but when I expanded the query to include more dayparts I
think i forgot something... Here is the new query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2, qryDayPart2.DayPartCust2,
qryDayPart3.DayPart3, qryDayPart3.DayPartCust3,qryDayPart4.DayPart4,
qryDayPart4.DayPartCust4,qryDayPart5.DayPart5,
qryDayPart5.DayPartCust5,qryDayPart6.DayPart6, qryDayPart6.DayPartCust6,
qryDayPart7.DayPart7, qryDayPart7.DayPartCust7,qryDayPart8.DayPart8,
qryDayPart8.DayPartCust8

FROM (qryDayPart1 INNER JOIN qryDayPart2 ON qryDayPart1.SalesDate =
qryDayPart2.SalesDate) INNER JOIN qryDayPart3 ON qryDayPart1.SalesDate =
qryDayPart3.SalesDate
INNER JOIN qryDayPart4 ON qryDayPart1.SalesDate = qryDayPart4.SalesDate
INNER JOIN qryDayPart5 ON qryDayPart1.SalesDate = qryDayPart5.SalesDate
INNER JOIN qryDayPart6 ON qryDayPart1.SalesDate = qryDayPart6.SalesDate
INNER JOIN qryDayPart7 ON qryDayPart1.SalesDate = qryDayPart7.SalesDate
INNER JOIN qryDayPart8 ON qryDayPart1.SalesDate = qryDayPart8.SalesDate;


Ernst.

Ken Snell (MVP) said:
No you don't want a union query. You need to join the three queries in one
query and use a normal Select query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 INNER JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
INNER JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;
--

Ken Snell
<MS ACCESS MVP>





Ernst Guckel said:
Hello,

I am having a bit of trouble here... I have 3 queries that are Identical
with a few exceptions but the design is the same... Each looks like this:

qryDayPart1
SELECT tblSales.SalesDate, Sum([Sales]/100) AS DayPart1,
Sum(tblSales.Transactions) AS DayPartCust1
FROM tblSales
WHERE (((tblSales.SalesTime) Between DayStart(1) And DayStop(1)))
GROUP BY tblSales.SalesDate;


qryDayPart2 and 3 are the same except DayStart and DayStop call with 2 and
3
and the Field names are DayPart2 and DayPart3. I am looking to combine
this
into a query that results in:

SalesDate, DayPart1, DayPart2, DayPart3...

Any ideas?

Ernst.
Click to expand...
Click to expand...
 
You don't say what is "wrong" when you expanded the query. Did you try my
LEFT JOIN version?

--

Ken Snell
<MS ACCESS MVP>


Ernst Guckel said:
This works great but when I expanded the query to include more dayparts I
think i forgot something... Here is the new query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2, qryDayPart2.DayPartCust2,
qryDayPart3.DayPart3, qryDayPart3.DayPartCust3,qryDayPart4.DayPart4,
qryDayPart4.DayPartCust4,qryDayPart5.DayPart5,
qryDayPart5.DayPartCust5,qryDayPart6.DayPart6, qryDayPart6.DayPartCust6,
qryDayPart7.DayPart7, qryDayPart7.DayPartCust7,qryDayPart8.DayPart8,
qryDayPart8.DayPartCust8

FROM (qryDayPart1 INNER JOIN qryDayPart2 ON qryDayPart1.SalesDate =
qryDayPart2.SalesDate) INNER JOIN qryDayPart3 ON qryDayPart1.SalesDate =
qryDayPart3.SalesDate
INNER JOIN qryDayPart4 ON qryDayPart1.SalesDate = qryDayPart4.SalesDate
INNER JOIN qryDayPart5 ON qryDayPart1.SalesDate = qryDayPart5.SalesDate
INNER JOIN qryDayPart6 ON qryDayPart1.SalesDate = qryDayPart6.SalesDate
INNER JOIN qryDayPart7 ON qryDayPart1.SalesDate = qryDayPart7.SalesDate
INNER JOIN qryDayPart8 ON qryDayPart1.SalesDate = qryDayPart8.SalesDate;


Ernst.

Ken Snell (MVP) said:
No you don't want a union query. You need to join the three queries in
one
query and use a normal Select query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 INNER JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
INNER JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;
--

Ken Snell
<MS ACCESS MVP>





Ernst Guckel said:
Hello,

I am having a bit of trouble here... I have 3 queries that are
Identical
with a few exceptions but the design is the same... Each looks like
this:

qryDayPart1
SELECT tblSales.SalesDate, Sum([Sales]/100) AS DayPart1,
Sum(tblSales.Transactions) AS DayPartCust1
FROM tblSales
WHERE (((tblSales.SalesTime) Between DayStart(1) And DayStop(1)))
GROUP BY tblSales.SalesDate;


qryDayPart2 and 3 are the same except DayStart and DayStop call with 2
and
3
and the Field names are DayPart2 and DayPart3. I am looking to combine
this
into a query that results in:

SalesDate, DayPart1, DayPart2, DayPart3...

Any ideas?

Ernst.
Click to expand...
Click to expand...
Click to expand...
 
Actually I got it. Type on my part. Thanks for the help.

Ernst.


Ken Snell (MVP) said:
You don't say what is "wrong" when you expanded the query. Did you try my
LEFT JOIN version?

--

Ken Snell
<MS ACCESS MVP>


Ernst Guckel said:
This works great but when I expanded the query to include more dayparts I
think i forgot something... Here is the new query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2, qryDayPart2.DayPartCust2,
qryDayPart3.DayPart3, qryDayPart3.DayPartCust3,qryDayPart4.DayPart4,
qryDayPart4.DayPartCust4,qryDayPart5.DayPart5,
qryDayPart5.DayPartCust5,qryDayPart6.DayPart6, qryDayPart6.DayPartCust6,
qryDayPart7.DayPart7, qryDayPart7.DayPartCust7,qryDayPart8.DayPart8,
qryDayPart8.DayPartCust8

FROM (qryDayPart1 INNER JOIN qryDayPart2 ON qryDayPart1.SalesDate =
qryDayPart2.SalesDate) INNER JOIN qryDayPart3 ON qryDayPart1.SalesDate =
qryDayPart3.SalesDate
INNER JOIN qryDayPart4 ON qryDayPart1.SalesDate = qryDayPart4.SalesDate
INNER JOIN qryDayPart5 ON qryDayPart1.SalesDate = qryDayPart5.SalesDate
INNER JOIN qryDayPart6 ON qryDayPart1.SalesDate = qryDayPart6.SalesDate
INNER JOIN qryDayPart7 ON qryDayPart1.SalesDate = qryDayPart7.SalesDate
INNER JOIN qryDayPart8 ON qryDayPart1.SalesDate = qryDayPart8.SalesDate;


Ernst.

Ken Snell (MVP) said:
No you don't want a union query. You need to join the three queries in
one
query and use a normal Select query:

SELECT qryDayPart1.SalesDate, qryDayPart1.DayPart1,
qryDayPart1.DayPartCust1, qryDayPart2.DayPart2,
qryDayPart2.DayPartCust2, qryDayPart3.DayPart3,
qryDayPart3.DayPartCust3
FROM (qryDayPart1 INNER JOIN qryDayPart2
ON qryDayPart1.SalesDate = qryDayPart2.SalesDate)
INNER JOIN qryDayPart3
ON qryDayPart1.SalesDate = qryDayPart3.SalesDate;
--

Ken Snell
<MS ACCESS MVP>





Hello,

I am having a bit of trouble here... I have 3 queries that are
Identical
with a few exceptions but the design is the same... Each looks like
this:

qryDayPart1
SELECT tblSales.SalesDate, Sum([Sales]/100) AS DayPart1,
Sum(tblSales.Transactions) AS DayPartCust1
FROM tblSales
WHERE (((tblSales.SalesTime) Between DayStart(1) And DayStop(1)))
GROUP BY tblSales.SalesDate;


qryDayPart2 and 3 are the same except DayStart and DayStop call with 2
and
3
and the Field names are DayPart2 and DayPart3. I am looking to combine
this
into a query that results in:

SalesDate, DayPart1, DayPart2, DayPart3...

Any ideas?

Ernst.
Click to expand...
Click to expand...
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 Query 1
SUM in a UNION query 2
Union of several queries 1
Union query 8
Union query not displaying select statement 1
Quarterly Sales Tax Query 2
Union Query with division 2
Displaying data using crosstab query or other method 1

Back
Top