Pulling together several tables

[morganth]

I am a bit of a novice with Access (am running Access 2010), but it seems within my company I am the most advanced user, so am a bit stuck on something and don't have anywhere to turn to! :banghead:

I have datasets in tables for some price data, which is monthly.
Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc)
I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month.
Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?

Thank you!

 

[plog]

You can't create data that doesn't exist. So, you need to create it.

If you want to report on every month, you would need a data source that lists every month you want to report on. Do you have such a table?

I can't really get my head around your table structure, could you post what the table and field names are, along with some sample data? You can use this format:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Steve, 12, 2/3/2014
David, 87, 5/18/2008
Larry, 33, 6/7/2009

 

[morganth]

Sorry for not being clear.

Hope this makes it slightly clearer on what's going on!
i60.tinypic.com/2uy6anb.jpg


Thanks!

 

[plog]

You need to get all your data into one table. You do that, then you can run a cross-tab query (http://office.microsoft.com/en-us/a...ad-by-using-a-crosstab-query-HA010229577.aspx) to produce what you want.

Instead of a new table for each month, you need a field in your table to designate which month the data is for. You do that, then you get all the date into one table.