Primary Key and table relationships

[dm23]

I'm working on an Access database for an accounting department that will keep track of historical store data for about 20 locations. I have created a table for each of the line items that will go on the report such as sales revenue for each product, expenses, etc. Each location already has a unique store code that is used by the accounting department so I have been setting the primary key to be the store code for the locations so the tables are set up like this:
Month1 Month2...
StoreCode Location1
StoreCode Location2
etc..


The purpose of the report is to set up a comparison between stores. because all the tables are based on location and store code, they all have the same primary key. I'm looking into setting up relationships with tables to help with queries and updates, but I'm not sure how the relationships should work in this case. Would it be better to create new primary keys for each table to try to set up 1 to many realtionships? Or should I use one to one relationships because all the data is based on the location and the store code? Please help!
Thanks

[June7]

Are these 'line item' tables identical in field structure? Should be one table with another field for line item code.

[orange]

I agree with June7, I think you have some table design/structure issues.
I think you should review this tutorial that will hopefully help you with database concepts.

http://www.rogersaccesslibrary.com/T...lationship.zip

[dm23]

I looked at the file in the link orange, I get what its saying, but the way the report and the available data is set up I'm still not sure how the relationships should work in my database. Here is how the report has to be structured:

	Store code	Store code	Store code	Store code	Store code	Store code	Store code	Store code	Store code
	Location1	Location2	Location3	Location4	Location5	Location6	Location7	Location8	Location9	Total Stores
Statistics
## of Bill Payments
Cash	#	#	#	#	#	#	#	#	#	#
Check	#	#	#	#	#	#	#	#	#	#
Credit Card	#	#	#	#	#	#	#	#	#	#
Money Order	#	#	#	#	#	#	#	#	#	#
Total	#	#	#	#	#	#	#	#	#	#

And below is how the data is structured in the tables. I need to have at least one year of historical data and each month the numbers are sent in separately for each category(cash, check, etc)

TableCash		Month1	Month2	Month3		TableCheck		Month1	Month2	Month3
StoreCode	Location1	#	#	#		StoreCode	Location1	#	#	#
StoreCode	Location2	#	#	#		StoreCode	Location2	#	#	#
StoreCode	Location3	#	#	#		StoreCode	Location3	#	#	#
StoreCode	Location4	#	#	#		StoreCode	Location4	#	#	#
StoreCode	Location5	#	#	#		StoreCode	Location5	#	#	#
StoreCode	Location6	#	#	#		StoreCode	Location6	#	#	#
StoreCode	Location7	#	#	#		StoreCode	Location7	#	#	#
StoreCode	Location8	#	#	#		StoreCode	Location8	#	#	#
StoreCode	Location9	#	#	#		StoreCode	Location9	#	#	#

Since there is no additionl information for each category do I still need to come up with a different primary key for each table? Or do I not have to worry about many to many relationships in this case? Or would this be a case of one to one relationships?

[June7]

That report arrangement is showing the data transposed as would be done with a CROSSTAB query.

If you don't import the data into a normalized structure (one table for all line items), will have to use UNION query to merge them into a single dataset for the report's RecordSource. UNION essentially results in the dataset the data should be in to begin with.

Or use a subreport for each table. However, get the grand total for all line items will be trickier.