How to handle union query with a condition

[Jack]

Hi,
I got a union query which works fine. However it needs to change based on
the current month. Thus it needs to be dynamic in nature.

I am not aware how to do this. However the concept would be somewhat like
the following.
I appreciate any help for resolution of this issue. Thanks.

If Month(Date()) = 7 Then


select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, Null, Null, Null, Null, Null, Null
from Row4Sample1_Variance_transformed;

If Month(Date()) = 8 Then


select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, month07, Null, Null, Null, Null, Null
from Row4Sample1_Variance_transformed;


If Month(Date()) = 9 Then

select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, month07, month08, Null, Null, Null, Null
from Row4Sample1_Variance_transformed;


If Month(Date()) = 10 Then

select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, month07, month08, month09, Null, Null,
Null
from Row4Sample1_Variance_transformed;


If Month(Date()) = 11 THen


select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, month07, month08, month09, month10, Null,
Null
from Row4Sample1_Variance_transformed;

If Month(Date()) = 12 Then

select * from Row1Sample_LastYearSales1_transformed
union all
select * from Row2Sample_LastYearBudget1_transformed
UNION ALL select * from Row3Sample1_ThisYearsForecast_tranformed;
union all select slsman, custnum, name, grandtotal, month01, month02,
month03, month04, month05, month06, month07, month08, month09, month10,
Month11, Null
from Row4Sample1_Variance_transformed;

 

[Dale_Fye via AccessMonster.com]

Well, I regret to tell you that by creating tables that look like
spreadsheets, you have made this task significantly more difficult.
Relational databases function best when you store your data in columns, not
in rows. In your case, you have stored Month data as column headers, instead
of having a SalesMonth column and storing the month in that column. Had you
done it that way, this would have been as simple as adding a WHERE clause to
the union query:

WHERE [SalesMonth] < Month(Date())

Then, you could have created a crosstab query based on the union query to
display only the months that you want.

Not so easy with the structure that you have.

I guess the important question is how do you intend to use this query?

If it is a report, you could use the Format event associated with the header
and details sections to hide the columns you don't need.

If you want to display the results in a form (either continuous or datasheet),
you could probably do something similar but it would probably require some
additional coding.

HTH
Dale