Union Query of Queries

[joave]

Hi:

I am using a query of two queries - those 2 queries come up with the same
4 columns. Here is my problem - the first query determines which people have
sent a certain thing and connected it, counts how many were sent and
connected (different columns), and puts them in order by how many they sent
and then how many they connected.

The table itself has one entry each time someone sends one (with a column
for connected, but no column for sent - they only get in the table if it was
sent - this is the cause of my issue but I have no control over the raw
data!)

The second query determines what people from the same tables do not have
any of that thing sent or connected (a zero query per se). I am trying to
put the data in the same order between the two individual queries but am not
having any luck. This is because you cannot ORDER BY from both queries in the
union, only one of them. Therefore the data from the second table is ending
up in the middle of this query instead of at the end (should be at the end
because they are all zeroes).

Here is the query of queries:


SELECT ID, Name, Sent, Connected from Location1MonthlyQuery
UNION ALL SELECT ID, Name, Sent, Connected from Location1ZeroSentQuery
ORDER BY Location1MonthlyQuery.Sent DESC, Location1MonthlyQuery.Connected;

Any help would be appreciated.

Thank you,

Dave

 

[Marshall Barton]

I am using a query of two queries - those 2 queries come up with the same
4 columns. Here is my problem - the first query determines which people have
sent a certain thing and connected it, counts how many were sent and
connected (different columns), and puts them in order by how many they sent
and then how many they connected.

The table itself has one entry each time someone sends one (with a column
for connected, but no column for sent - they only get in the table if it was
sent - this is the cause of my issue but I have no control over the raw
data!)

The second query determines what people from the same tables do not have
any of that thing sent or connected (a zero query per se). I am trying to
put the data in the same order between the two individual queries but am not
having any luck. This is because you cannot ORDER BY from both queries in the
union, only one of them. Therefore the data from the second table is ending
up in the middle of this query instead of at the end (should be at the end
because they are all zeroes).

Here is the query of queries:

SELECT ID, Name, Sent, Connected from Location1MonthlyQuery
UNION ALL SELECT ID, Name, Sent, Connected from Location1ZeroSentQuery
ORDER BY Location1MonthlyQuery.Sent DESC, Location1MonthlyQuery.Connected;

SELECT 1 As Q, ID, Name, Sent, Connected
FROM Location1MonthlyQuery
UNION ALL
SELECT 2, ID, Name, Sent, Connected
FROM Location1ZeroSentQuery
ORDER BY Q, Location1MonthlyQuery.Sent DESC,
Location1MonthlyQuery.Connected

 

[joave]

That was PERFECT, thank you. How does that work? Lastly, I have one more
questions regarding this Union query:

I have an [Enter Start Date] and [Enter End Date] in each of the queries
that this Union query uses. How can I make it so that I only enter the Start
Date and End Date once (instead of twice)?

Thank you again,

Dave

 

[Marshall Barton]

It just uses a calculated (simple constants 1 or 2) field
named Q to distinguish one select query from the other.
Sorting on that field will then keep each select queries'
records together.

I would expect(?) that if the parameter prompt strings are
**exactly** the same you would be prompted only once each.
OTOH, I never use prompt strings so I can't be sure.

A preferred approach is to create an unbound form with a
text box for each parameter and probably a command button to
do whatever with the query. If the text boxes are named
txtStart and txtEnd, the WHERE clause would be like:
WHERE datefield Between Forms!theform.txtStart And
Forms!theform.txtEnd
--
Marsh
MVP [MS Access]

That was PERFECT, thank you. How does that work? Lastly, I have one more
questions regarding this Union query:

I have an [Enter Start Date] and [Enter End Date] in each of the queries
that this Union query uses. How can I make it so that I only enter the Start
Date and End Date once (instead of twice)?

SELECT 1 As Q, ID, Name, Sent, Connected
FROM Location1MonthlyQuery
UNION ALL
SELECT 2, ID, Name, Sent, Connected
FROM Location1ZeroSentQuery
ORDER BY Q, Location1MonthlyQuery.Sent DESC,
Location1MonthlyQuery.Connected

 

[joave]

Yep, you got that spot on too. Thank you again for all your help

Dave

It just uses a calculated (simple constants 1 or 2) field
named Q to distinguish one select query from the other.
Sorting on that field will then keep each select queries'
records together.

I would expect(?) that if the parameter prompt strings are
**exactly** the same you would be prompted only once each.
OTOH, I never use prompt strings so I can't be sure.

A preferred approach is to create an unbound form with a
text box for each parameter and probably a command button to
do whatever with the query. If the text boxes are named
txtStart and txtEnd, the WHERE clause would be like:
WHERE datefield Between Forms!theform.txtStart And
Forms!theform.txtEnd
--
Marsh
MVP [MS Access]

That was PERFECT, thank you. How does that work? Lastly, I have one more
questions regarding this Union query:

I have an [Enter Start Date] and [Enter End Date] in each of the queries
that this Union query uses. How can I make it so that I only enter the Start
Date and End Date once (instead of twice)?

SELECT 1 As Q, ID, Name, Sent, Connected
FROM Location1MonthlyQuery
UNION ALL
SELECT 2, ID, Name, Sent, Connected
FROM Location1ZeroSentQuery
ORDER BY Q, Location1MonthlyQuery.Sent DESC,
Location1MonthlyQuery.Connected

joave wrote:
I am using a query of two queries - those 2 queries come up with the same
4 columns. Here is my problem - the first query determines which people have
sent a certain thing and connected it, counts how many were sent and
connected (different columns), and puts them in order by how many they sent
and then how many they connected.

The table itself has one entry each time someone sends one (with a column
for connected, but no column for sent - they only get in the table if it was
sent - this is the cause of my issue but I have no control over the raw
data!)

The second query determines what people from the same tables do not have
any of that thing sent or connected (a zero query per se). I am trying to
put the data in the same order between the two individual queries but am not
having any luck. This is because you cannot ORDER BY from both queries in the
union, only one of them. Therefore the data from the second table is ending
up in the middle of this query instead of at the end (should be at the end
because they are all zeroes).

Here is the query of queries:

SELECT ID, Name, Sent, Connected from Location1MonthlyQuery
UNION ALL SELECT ID, Name, Sent, Connected from Location1ZeroSentQuery
ORDER BY Location1MonthlyQuery.Sent DESC, Location1MonthlyQuery.Connected;