Merging queries by column in access

X

Xmas

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
 
V

vanderghast

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top