How to create columns in a query based on a date input by the end user

[sm928]

Hello Access Guru,

I am currently stuck on a query and am hoping to get some help. I have no real training in access just what I have learned out of books. Having said that, I am sure there are easier/more efficient ways to reach my end result. Anyway, here is my issue:



I currently have one table which houses on hand inventory for different item numbers. This data is run on the last day of each month to capture what was the on hand inventory at that specific time. Each month the new data is then added to the table with a column titled 'Inventory Month' <-- this differentiates the data in order to know what was on had as of 9/30/2014 compared to 8/31/2014, 7/31/2014, etc.

There is another table which houses the standard costs of each item in a similar format (every month the standard costs are pulled as of the last day of the month in order to capture what was the historical cost...this table also includes an 'Inventory month' column). Standards do change through out the years so this allows us to analyze the on hand quantity on 7/31/2014 using the standard cost as of 7/31/2014 (as opposed to having a table that only keeps track of current standards).

I build a query which uses both the tables mentioned above in order to bring in the Item number (Inventory table), Item description (inventory table), quantity on hand (inventory table), extended cost (formula which multiplies the quantity on hand from the inventoy table with the respective standard cost from the standard cost table). In addition to this I also bring in the 'Inventory Month' column from the inventory table and enter a prompt [Enter month MM/YYYY] in order for the end user to dictate what data they would like to view based on the inventory month.

ISSUE:
In addition to the columns above I would like there to be 5 additional columns which would look at the historical extended value for the 5 previous months. For example if the end user enters '06/2014' I would like for the query pull the on hand quantity and extended cost as of 6/30/2014 using the standards as of 6/30/2014 AND also have 5 more columns which have the extended cost for 05/2014 (using the on hand quantities and standards as of 5/2014), a column showing the extended cost for 04/2014 (using the on hand quantities and standards as of 04/2014), etc through 01/2014.

Is there any way to do this using a query? I do not have much experience with Forms but if that is the better option please let me know and I will read more on how to manage and create them.

Thank you in advance for all your help!!!

[ranman256]

You would do the 1 query to gather the current month and all previous historical months. (vertical data)
THEN make a crosstab query to put the months as columns