Union Queries involving 2 Queries

Z

Zak

I've been going through this over and over and I just need another pair
of eyes to look at this before I go nuts!!! I'm trying to combine two
queries so that I can create a crosstab table with names as row
headings, dates as column headings and the sum of the Counts as
Values...

My first query is as follows:
SELECT h.orderdate AS [Date], Count(h.invnumber) AS [Count],
LTrim(RTrim([fname]))+', '+LTrim(RTrim([lname])) AS Name
FROM dbo_npohdr AS h LEFT JOIN dbo_userlist AS u
ON h.origkey = u.userkey
GROUP BY h.orderdate, u.fname, u.lname
ORDER BY h.orderdate;

The second is:
SELECT h.receivedat AS [Date], Count(h.invkey) AS [Count],
LTrim(RTrim([fname]))+', '+LTrim(RTrim([lname])) AS Name
FROM dbo_imhdr AS h LEFT JOIN dbo_userlist AS u ON h.origkey =
u.userkey
GROUP BY h.receivedat, u.fname, u.lname
ORDER BY h.receivedat;

And the third and final one is giving me problems:

SELECT x.date, Sum(x.count) AS SumOfcount, x.name
FROM [select date, count, name
from [qry_Non-PO_Entry_by_Date]

union

select date, count, name
from [qry_PO-Match_by_Date]]. AS x
GROUP BY x.date, x.name;

I'm not sure where I went wrong? Can anyone help???
 
M

Marshall Barton

Zak said:
I've been going through this over and over and I just need another pair
of eyes to look at this before I go nuts!!! I'm trying to combine two
queries so that I can create a crosstab table with names as row
headings, dates as column headings and the sum of the Counts as
Values...

My first query is as follows:
SELECT h.orderdate AS [Date], Count(h.invnumber) AS [Count],
LTrim(RTrim([fname]))+', '+LTrim(RTrim([lname])) AS Name
FROM dbo_npohdr AS h LEFT JOIN dbo_userlist AS u
ON h.origkey = u.userkey
GROUP BY h.orderdate, u.fname, u.lname
ORDER BY h.orderdate;

The second is:
SELECT h.receivedat AS [Date], Count(h.invkey) AS [Count],
LTrim(RTrim([fname]))+', '+LTrim(RTrim([lname])) AS Name
FROM dbo_imhdr AS h LEFT JOIN dbo_userlist AS u ON h.origkey =
u.userkey
GROUP BY h.receivedat, u.fname, u.lname
ORDER BY h.receivedat;

And the third and final one is giving me problems:

SELECT x.date, Sum(x.count) AS SumOfcount, x.name
FROM [select date, count, name
from [qry_Non-PO_Entry_by_Date]

union

select date, count, name
from [qry_PO-Match_by_Date]]. AS x
GROUP BY x.date, x.name;
Click to expand...


The [subquery] syntax does not permit embedded [ ]s. It
doesn't appear that you have any spaces or other funny
characters in the names so try getting rid of the inner
square brackets. Even if you change to the (subquery)
syntax, the inner [ ]s will probably cause troubleafter you
save the query

I hope you tried to simplifiy your real queries for this
post, but even here it is a really bad idea to use all those
reserved words as field names.

Note that Access has a Trim function, so you shouldn't need
to use LTrim(RTrim(...

Why not union the two original Select statements in one
query (without the ORDER BY clauses? This would make the
final query a whole lot simpler.
 
Top