Union Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It was suggested I try making a Union query. I have 5 separate queries that
ask
for a date.
I'm not quite sure how to go about writing the SQL for all 5 queries.
They are aggregate queries resulting in a Sumof(txtfield) based on date.
Help in the syntax would be nice.
 
Access HELP has examples of UNION queries. The generic syntax is something
like:

SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
...

Note -- you need the same number of fields in each SELECT clause.
 
This is what I wrote
Select [SumofPT] From qrySum5Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

Union Select [SumofPT] From qrySum14Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

But get error message about syntax error
 
I'll assume the second "WHERE" clause is a typo, since you are using "-7"
days for both SELECTs.

I'll suggest that you rename your [Date] field, as this is an Access
reserved word, and only will confuse both you and Access.

Can you get a single SELECT to work correctly?

Can you get both of these working?

I don't see a semi-colon following the final SELECT statement.
 
That's my problem. I need the expression to be the same in all 5 queries.
The EnterStartDate, however will be different.
When running the queries as straight select queries I get correct results.
It's this Union thing that's got me.
 
When I add the third Union Select statement, it does not work

dar said:
That's my problem. I need the expression to be the same in all 5 queries.
The EnterStartDate, however will be different.
When running the queries as straight select queries I get correct results.
It's this Union thing that's got me.

Jeff Boyce said:
I'll assume the second "WHERE" clause is a typo, since you are using "-7"
days for both SELECTs.

I'll suggest that you rename your [Date] field, as this is an Access
reserved word, and only will confuse both you and Access.

Can you get a single SELECT to work correctly?

Can you get both of these working?

I don't see a semi-colon following the final SELECT statement.

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Drop the equals sign for one thing.

WHERE [Date] Between DateAdd("d",-7,[Enter StartDate:]) And [Enter StartDate:]

and GUESSING at what you want

PARAMETERS [Enter StartDate:] DateTime;
Select [SumofPT] From qrySum5Day
Where [Date] Between DateAdd("d",-7,[Enter StartDate:])
And [Enter StartDate:]

UNION
Select [SumofPT] From qrySum14Day
Where [Date] = Between DateAdd("d",-14,[Enter StartDate:])
And [Enter StartDate:]-8

This is what I wrote
Select [SumofPT] From qrySum5Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

Union Select [SumofPT] From qrySum14Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

But get error message about syntax error
Jeff Boyce said:
Access HELP has examples of UNION queries. The generic syntax is something
like:

SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
...

Note -- you need the same number of fields in each SELECT clause.

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Do I understand you correctly? Are you saying that you are using the same
parameter ([EnterStartDate]) in each of five queries, but using DIFFERENT
start dates in each? If so, before you can do the UNION you'll need to
rename those parameters. Otherwise, if they all have the same name, they'll
all use the same value.
 
I will re-read posts for syntax, however, I can't change the -7 to any other
# since it will always be -7 from whatever date they enter in response to the
query prompt. I also want to know if I can log on to your link, since I do
not have
Compuserve ID?

John Spencer (MVP) said:
Drop the equals sign for one thing.

WHERE [Date] Between DateAdd("d",-7,[Enter StartDate:]) And [Enter StartDate:]

and GUESSING at what you want

PARAMETERS [Enter StartDate:] DateTime;
Select [SumofPT] From qrySum5Day
Where [Date] Between DateAdd("d",-7,[Enter StartDate:])
And [Enter StartDate:]

UNION
Select [SumofPT] From qrySum14Day
Where [Date] = Between DateAdd("d",-14,[Enter StartDate:])
And [Enter StartDate:]-8

This is what I wrote
Select [SumofPT] From qrySum5Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

Union Select [SumofPT] From qrySum14Day
Where [Date] = Between DateAdd("d",-7,[Enter StartDate:]) And [Enter
StartDate:]

But get error message about syntax error
Jeff Boyce said:
Access HELP has examples of UNION queries. The generic syntax is something
like:

SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
SELECT <something> FROM <somewhere> WHERE <somefield = somevalue>
UNION
...

Note -- you need the same number of fields in each SELECT clause.

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
Click to expand...
 
That is what I am saying. Believe me I appreciate your responses, and I am
learning as I go. So I'll change the prompt in all the appropriate places
and see what happens. One other question, can I do a total of 5 UNION
statements?
 
Dar

One way to total your UNION query results would be to create a Totals query,
using the UNION query as your source.
 
Back
Top