Merging queries by column in access

Hi, I have a series of queries which get data aggregated by year, e.g.

select year(date), min(var1), avg(var1), max(var1) from table where
(ridiculously complicated set of conditions) group by year(date)

select year(date), min(var2), avg(var2), max(var2) from table where
(entirely different ridiculously complicated set of conditions) group by
year(date)

etc etc

There are six queries like this so what I want is to amalgamate these
columns together into one results table (and also into one query so
users can just run one stored query rather than six!) as follows:

year, min(var1), avg(var1), max(var1), min(var2), avg(var2), max
(var2)...

I would have thought this is an area where a join would be effective,
but I am being defeated by Access' syntax. Have to admit I've been away
from SQL for some years and am a bit rusty! Any thoughts?

Cheers

You can try:

SELECT Year(date), min( iif( condition1, var1, null)), max( iif(condition1,
var1, null)), ... , min( iif(condition2, var2, null)) , ...
FROM ...
GROUP BY Year(date)


that is, remove the conditions from the WHERE clause which are not common to
all expressions, since they are not in the iif on which operates the
aggregations.


Vanderghast, Access MVP


"Xmas" wrote in message
news:MPG.2578f0eb2a02c23c989853@localhost...
Hi, I have a series of queries which get data aggregated by year, e.g.

select year(date), min(var1), avg(var1), max(var1) from table where
(ridiculously complicated set of conditions) group by year(date)

select year(date), min(var2), avg(var2), max(var2) from table where
(entirely different ridiculously complicated set of conditions) group by
year(date)

etc etc

There are six queries like this so what I want is to amalgamate these
columns together into one results table (and also into one query so
users can just run one stored query rather than six!) as follows:

year, min(var1), avg(var1), max(var1), min(var2), avg(var2), max
(var2)...

I would have thought this is an area where a join would be effective,
but I am being defeated by Access' syntax. Have to admit I've been away
from SQL for some years and am a bit rusty! Any thoughts?

Cheers