Grouping Union query?

[geralf]

Hi all

I have the following union query:

SELECT tblDrossbruk.CID, tblDrossbruk.DROSSID
FROM tblDrossbruk
GROUP BY tblDrossbruk.CID, tblDrossbruk.DROSSID
UNION ALL
SELECT tblDrossbrukHoldeovn.CID, tblDrossbrukHoldeovn.DROSSID
FROM tblDrossbrukHoldeovn
GROUP BY tblDrossbrukHoldeovn.CID, tblDrossbrukHoldeovn.DROSSID
UNION ALL SELECT tblOmsmelt.CID, tblOmsmelt.DROSSID
FROM tblOmsmelt
GROUP BY tblOmsmelt.CID, tblOmsmelt.DROSSID;

How do I get it Grouped for the entire recordset returned, and not for each table as it does now, without using a new query?

Thanks in advancefoe any help.

 

[raskew]

Gerhard -

Believe you can pull this off using a derived query. Out of curiousity I created an application using your table and field names, then used the following to return your results grouped over the entire recordset.

Note the derived portion surrounded by brackets [] and followed by a dot. This will show up in the designed grid as a table.

The CountOfDROSSID for each table and the subsequent SumOfDROSSID just served as a test so that I could if the query was producing the desired results.

Will be interested to hear how it works with your data.

Best wishes,-Bob

SELECT 
   IDShow.CID
  , IDShow.DROSSID
  , Sum(IDShow.CountofDROSSID) AS SumOfDROSSID
FROM [SELECT
    tblDrossbruk.CID
  , tblDrossbruk.DROSSID
  , Count(tblDrossbruk.DROSSID) AS CountOfDROSSID
FROM
   tblDrossbruk
GROUP BY
   tblDrossbruk.CID
  , tblDrossbruk.DROSSID 
UNION ALL SELECT
    tblDrossbrukHoldeovn.CID
  , tblDrossbrukHoldeovn.DROSSID
  , Count(tblDrossbrukHoldeovn.DROSSID) AS CountOfDROSSID
FROM
   tblDrossbrukHoldeovn
GROUP BY
   tblDrossbrukHoldeovn.CID
  , tblDrossbrukHoldeovn.DROSSID 
UNION ALL SELECT
    tblOmsmelt.CID
  , tblOmsmelt.DROSSID
  , Count(tblOmsmelt.DROSSID) AS CountOfDROSSID
FROM
   tblOmsmelt
GROUP BY
   tblOmsmelt.CID
  , tblOmsmelt.DROSSID]. AS IDShow
GROUP BY IDShow.CID, IDShow.DROSSID;

 

[geralf]

Hi Bob

I'm very impressed Bob!

I copied your SQL directly into the SQL window, and hit the Run-button. My thoughts was:'This can't work. No way this can work in My db with my tables and fields'. It ran PERFECT and gave the desired result. AWSOME Bob. You probably remember our mutual friend at WildHare's Larry Steel who showed me how to do this with two tables, but I did not manage to learn the technique with derived queries. Another great thing is also that you actually have a Union query that shows up in Design View (as well as SQL view).

Thanks for your effort Bob. Appreciate it very much.

Have a nice weekend!

See ya....

 

[raskew]

I was remiss in not identifying Larry Steele and Giovanni Caruso as the inspiration for this solution. Here, in part, is what Larry had to say:

"..... Notice that I've taken the same union query from above, and surrounded it with brackets "[]", then termintated that with a dot. This creates a derived query. Then I added the AS CountryCount to give it a name. The derived query is treated as a table. In fact, you can open this query in design view (bye-bye SQL view!) and you'll see CountOfCountry appear as a table. Since CountOfCountry is a number, I just created an aggregate of CountryCount to sum CountOfCountry.

Works like a champ!

The derived query was the Tip of the Month, December 2001 edition of Access-VB-SQL Advisor Magazine. The tip was submitted by Giovanni Caruso. One of the things that made it the Tip of the Month is that neither Andy Baron nor Ken Getz had ever seen this documented for JET.

Larry