Union Queries

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

Guest

I have two queries that I want to make into one query so that the information
can be pulled into a form. I have read some information on the Union query
and was wondering if this would work for what I need to do. Here are the two
queries that I have and that I need to try to merge into one query. Can
someone please advise what I need to do from here?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID] = [Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]));


SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE NAME],
VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]));
 
Since your field sets match, open one of the queries in SQL design view and
paste the other query's SQL as such:

SELECT ...
UNION SELECT ...;

I have two queries that I want to make into one query so that the information
can be pulled into a form. I have read some information on the Union query
and was wondering if this would work for what I need to do. Here are the two
queries that I have and that I need to try to merge into one query. Can
someone please advise what I need to do from here?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID] = [Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]));

SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE NAME],
VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]));
Click to expand...
 
Ok this is what I have - but it is asking me to Enter the Date received
twice. Is there a way that I can have it ask only one but still only give me
the what I need for both queries?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID]=[Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]) AND
(([Correspondence Volumes].[VOLUME RECEIVED])>0));
UNION ALL SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE
NAME], VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]) AND
((VOLUMES.[VOLUME RECEIVED])>0));

kingston via AccessMonster.com said:
Since your field sets match, open one of the queries in SQL design view and
paste the other query's SQL as such:

SELECT ...
UNION SELECT ...;

I have two queries that I want to make into one query so that the information
can be pulled into a form. I have read some information on the Union query
and was wondering if this would work for what I need to do. Here are the two
queries that I have and that I need to try to merge into one query. Can
someone please advise what I need to do from here?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID] = [Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]));

SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE NAME],
VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]));
Click to expand...
Click to expand...
 
Try making both parameters the same, i.e. [Enter Date]. If that doesn't work,
I think you may need to make a form to trigger the query based on a single
date control.
Ok this is what I have - but it is asking me to Enter the Date received
twice. Is there a way that I can have it ask only one but still only give me
the what I need for both queries?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID]=[Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]) AND
(([Correspondence Volumes].[VOLUME RECEIVED])>0));
UNION ALL SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE
NAME], VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]) AND
((VOLUMES.[VOLUME RECEIVED])>0));
Since your field sets match, open one of the queries in SQL design view and
paste the other query's SQL as such:
Click to expand...
[quoted text clipped - 19 lines]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]));
Click to expand...
Click to expand...
Click to expand...
 
That worked when I made the parameters the same. Thank you so much for your
help.

kingston via AccessMonster.com said:
Try making both parameters the same, i.e. [Enter Date]. If that doesn't work,
I think you may need to make a form to trigger the query based on a single
date control.
Ok this is what I have - but it is asking me to Enter the Date received
twice. Is there a way that I can have it ask only one but still only give me
the what I need for both queries?

SELECT [Correspondence Volumes].[DATE RECEIVED], [Correspondence
Volumes].[QUEUE ID], [Correspondence Queues].[QUEUE NAME], [Correspondence
Volumes].[VOLUME RECEIVED]
FROM [Correspondence Volumes] INNER JOIN [Correspondence Queues] ON
[Correspondence Volumes].[QUEUE ID]=[Correspondence Queues].ID
WHERE ((([Correspondence Volumes].[DATE RECEIVED])=[Enter Date]) AND
(([Correspondence Volumes].[VOLUME RECEIVED])>0));
UNION ALL SELECT VOLUMES.[DATE RECEIVED], VOLUMES.[QUEUE ID], QUEUES.[QUEUE
NAME], VOLUMES.[VOLUME RECEIVED]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]) AND
((VOLUMES.[VOLUME RECEIVED])>0));
Since your field sets match, open one of the queries in SQL design view and
paste the other query's SQL as such:
Click to expand...
[quoted text clipped - 19 lines]
FROM QUEUES INNER JOIN VOLUMES ON QUEUES.ID = VOLUMES.[QUEUE ID]
WHERE (((VOLUMES.[DATE RECEIVED])=[Enter Date Received]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top