Microsoft Access - Changing the recordsource or dynamically hiding columns of a query

Asked By Jason on 26-Jun-12 01:34 PM
So my problem is that I have a query that has fields based on what month we are in.  For instance, since it's June right now I would need 6 fields in this query.  When it turns to July, I would want 7 fields to show.  I'm not sure how to accomplish this however.  I should mention this query will be used in a graph in a report.  My ideas so far have to either hide the fields based on what month it is in vba or to change the recordsource of the graph in vba and make 12 queries.  Anyone have any other ideas or know how to make the ideas I have become possible?
wally eye replied to Jason on 26-Jun-12 06:34 PM
You could build the query in VBA, it would be a bit slower. 

Would a cross-tab query work?  Just put your selection criteria in and use the month for column headers.
Jason replied to wally eye on 27-Jun-12 10:09 AM
No unfortunately the dates span the entire year.  So a crosstab query would show the full year.
Pat Hartman replied to Jason on 28-Jun-12 08:29 PM
Selection criteria would limit the rows selected so if your data is normalized, the crosstab solution will work.  It sounds like you have 12 columns in the same row rather than 12 rows so that means more work as is usual for a non-normalized schema.  You either need 12 queries and decide which to run based on the month or you build the SQL string with VBA depending on the month.