Union Query Help Please

[Lou]

I have created this union query, I am running a report
based of this information. I dont understand how to write
SQL into this for sorting options like I do in query
design mode. Below is my union query when I run I also get
totaling results I didn't ask for.

SELECT [HistoryID], qryWeek1.CouponID,
qryWeek1.CouponType, qryWeek1.CouponName,
qryWeek1.DateReceived, qryWeek1.NumberReceived,
qryWeek1.CouponTag
FROM qryWeek1

UNION
SELECT [HistoryID], qryWeek2.CouponID,
qryWeek2.CouponType, qryWeek2.CouponName,
qryWeek2.DateReceived, qryWeek2.NumberReceived,
qryWeek2.CouponTag
FROM qryWeek2

UNION
SELECT [HistoryID], qryWeek3.CouponID,
qryWeek3.CouponType, qryWeek3.CouponName,
qryWeek3.DateReceived, qryWeek3.NumberReceived,
qryWeek3.CouponTag
FROM qryWeek3

UNION SELECT [HistoryID], qryWeek4.CouponID,
qryWeek4.CouponType, qryWeek4.CouponName,
qryWeek4.DateReceived, qryWeek4.NumberReceived,
qryWeek4.CouponTag
FROM qryWeek4;

ACCESS 2002
Thank you.

 

[John Vinson]

I have created this union query, I am running a report
based of this information. I dont understand how to write
SQL into this for sorting options like I do in query
design mode.

To sort the outpupt of the UNION query add an ORDER BY clause in the
last of the SELECT blocks:

UNION SELECT [HistoryID], qryWeek4.CouponID,
qryWeek4.CouponType, qryWeek4.CouponName,
qryWeek4.DateReceived, qryWeek4.NumberReceived,
qryWeek4.CouponTag
FROM qryWeek4
ORDER BY DateReceived, CouponTag; <<< for example

Below is my union query when I run I also get
totaling results I didn't ask for.

well... no, you would only get what you *asked* for; it's not clear
from this what you want nor how to change the query so that you're
asking for what you want! Could you explain?

Also... rather than running four weekly totals queries and running a
UNION query, I wonder if you might do better to run a single query and
use a Report's Sorting and Grouping feature to get the results you
want.

 

[Lou]

John Thank you,

On the single query standpoint I have 4 seperate weeks of
Data I am trying to Subtotal together by seperate weeks. I
have a form right now that the user inputs the 4 different
weeks and then go from there. I am going to take a stab at
sorting it on the report now that you mention it.

Thank you.

-----Original Message-----

I have created this union query, I am running a report
based of this information. I dont understand how to write
SQL into this for sorting options like I do in query
design mode.

To sort the outpupt of the UNION query add an ORDER BY clause in the
last of the SELECT blocks:

UNION SELECT [HistoryID], qryWeek4.CouponID,
qryWeek4.CouponType, qryWeek4.CouponName,
qryWeek4.DateReceived, qryWeek4.NumberReceived,
qryWeek4.CouponTag
FROM qryWeek4
ORDER BY DateReceived, CouponTag; <<< for example

Below is my union query when I run I also get
totaling results I didn't ask for.

well... no, you would only get what you *asked* for; it's not clear
from this what you want nor how to change the query so that you're
asking for what you want! Could you explain?

Also... rather than running four weekly totals queries and running a
UNION query, I wonder if you might do better to run a single query and
use a Report's Sorting and Grouping feature to get the results you
want.



.