Challenging Query....please help, boss is counting on me

[JTQ911]

The boss believes I can help him with access, im not too sure, i dont really even know access. There is a table with fields for each month of the year for 3 years

Jan 06 Feb 06 .......Dec 06....Dec 07....Dece08

Can i have a query that prompts the user to enter the month, and then returns that month plus the next 12 months in order......its for a man hour labor schedule.....its a 13 month rolling calender. I appreciate all the help I can get, im an engineer, not a database expert. It should be mentioned that I have no experience in VBA etc, just using the query design view.

Lets just assume that the table is called "RollingCalender"

THANKS IN ADVANCE!

 

[CraigDolphin]

I think this is problematic because your data is not stored 'correctly'. Instead of having a field for each month, you should instead have a date field and then whatever other field is necessary to contain the value in your table's 'cells' and another field to identify whatever your rows represent. Three fields total and maybe an autonumber primary key field as well. To paraphrase, you are using a spreadsheet approach to storing your data and not a normalized data structure.

Tables in databases should be tall and narrow rather than short and wide like spreadsheets.

This makes performing queries like this very challenging since the /fields/ that you select in your query will change depending on the parameter you input.

The only way that I know of that you might look into to resolve this without fixing your data structure is to dynamically generate the SQL of your query using VBA. I would imagine you would take the user input (presumably month and year) and use code to increment the month and year values to match the relevant field names while constructing your SQL statement, then run the SQL using Docmd.runsql. You could either apply the sql to a premade query, or else make your sql into a make table statement and use that table as the source for your report.

Edit: If you can reorganize your data as mentioned above, then the query itself is static except that you can use your user-input as a criteria in the date field to limit the result to the time period of interest.