Totalling a Union Query

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

Guest

Hi,

I have a need to write my query in a function and I don't want to create a
Query object if it is possible. I have created a UNION query statement which
executes and does everything correctly. In the result set of the UNION query
I have two records for the same 'person' but I would like to have it so that
the query statement I create groups and totals each 'persons' results into
one record entry. I effectively need to group my UNION query but I don't know
how I would go about writing this 'nested' query statement.

Any advice would be greatly appreciated. I can get around this in another
way but that would be a little more involved than my idea above (that is if
it is possible).

Thanks in advance...
 
***Untested SQL***
SELECT USQ.PersonID, Sum(USQ.[Whatever]) ...
FROM
(
SELECT T1.PersonID, T1.[Whatever] ...
FROM Table1 As T1
UNION ALL
SELECT T2.PersonID, T2.[CorrespondingToWhatever]
FROM Table2 As T2
) AS USQ

WHERE {criteria if required}
GROUP BY PersonID
******

USQ: abbreviation for Union SubQuery
 
Thank you Thank you Thank you!! It worked. A little different but it worked.
All I didn't use was the ") AS USQ" before the WHERE or GROUP BY part of the
sql statement. Thanks again for showing me something new. I think I will be
able to clean up a lot more queries now that I know the way this is done.
I've been creating query objects and then writing sql statements to total the
query objects but I have never liked this way of doing it. I guess you have
to to what you need to do inorder to meet deadlines. I'm glad I asked the
question this time around.

Have a great day! I know mine's gotten better already!!! :-)

Van T. Dinh said:
***Untested SQL***
SELECT USQ.PersonID, Sum(USQ.[Whatever]) ...
FROM
(
SELECT T1.PersonID, T1.[Whatever] ...
FROM Table1 As T1
UNION ALL
SELECT T2.PersonID, T2.[CorrespondingToWhatever]
FROM Table2 As T2
) AS USQ

WHERE {criteria if required}
GROUP BY PersonID
******

USQ: abbreviation for Union SubQuery

--
HTH
Van T. Dinh
MVP (Access)


PSI-Access said:
Hi,

I have a need to write my query in a function and I don't want to create a
Query object if it is possible. I have created a UNION query statement
which
executes and does everything correctly. In the result set of the UNION
query
I have two records for the same 'person' but I would like to have it so
that
the query statement I create groups and totals each 'persons' results into
one record entry. I effectively need to group my UNION query but I don't
know
how I would go about writing this 'nested' query statement.

Any advice would be greatly appreciated. I can get around this in another
way but that would be a little more involved than my idea above (that is
if
it is possible).

Thanks in advance...
Click to expand...
Click to expand...
 
Back
Top