Union Query - Dynamic Changes

  • Thread starter Thread starter hafeez
  • Start date Start date
H

hafeez

The following is a simplified example of my Union Query

SELECT F.Group1 AS G1, Count(F.Name) AS C1, 0 AS C2
FROM qryF AS F
WHERE (CRITERIA 1)
GROUP BY Group1;

UNION
SELECT F.Group1 AS G1, 0 AS C1, Count(F.Name) AS C2
FROM qryF AS F
WHERE (CRITERIA 2)
GROUP BY Group1;

Is there a way I can change all occurances of 'Group1'-
'Group2' (which is a different field in 'qryF')?
Click to expand...

I've tried using the 'PARAMETERS' but instead of
reading 'F.Group2 AS G1' it reads it as '"F.Group2" AS G1'
and that's not what I want.

TIA
Hafeez
 
parameter b as bit
SELECT iif(b,F.Group1,f.Group2) AS G1, Count(F.Name) AS C1, 0 AS C2
FROM qryF AS F
WHERE (CRITERIA 1)
GROUP BY iif(b,F.Group1,f.Group2);

it will run slow....
(david)
 
Thanks for the quick reply David!
The only problem with this solution is that the Groupb By
will be a parameter passed by the user.

Hafeez Esmail
 
The only problem with this solution is that the Groupb By
will be a parameter passed by the user.
Click to expand...

I thought that was what you asked for? You can't select Group2
and group by Group1 --- If you select Group2 you must group by
Group2. The group has to exactly match the select.

(david)
 
Sorry David I should have been more clear.
I understand the group has to exactly match the select.
What I meant was that in your solution, you hardcoded the
two possible Groupings ....Group1 or Group2.
My issue here is that it could be Group 3/4/5.....
(whatever the user passes in as a parameter).

Hafeez
 
Hmm.... obviously, no simple good solution.
Most people would use VBA to rebuild the query,
but you can do the same kludge using a SWITCH
or CHOOSE function in the SQL if the situation
justifies it.

(david)
 
Back
Top