If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Merging queries by column in access
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 |
Thread Tools | |
Display Modes | |
|
|