Access 2007 Importing from Excel with several many to many relationships...issue

[IrishRose044]

I am new to this forum and don't know if anyone else has had this issue... I used Access and Excel many many years ago, probably Access 97 or before so Access 2007 is kind of new to me with all the changes. I have an excel 2007 spreadsheet that tracks the groceries I buy and I want to import it to Access 2007. This table has 5- many to many relationships and I am having difficulty importing it so I can create a form to enter each transaction and update every table. I keep track of the date of purchase, the transaction number, the store product purchased from, the category of the product, the brand of the product, the unit price and the quantity per unit. I want to generate monthly reports showing my grocery spending and a grocery shopping list by category or by store. My many to many relationships are as follows:


1 product can be purchased at many stores
1 store can have many products
1 product come in many brands
1 brand can include many products
1 transaction can include many products
1 product can be on many transactions
1 product can have many unit quantities
1 unit quantity can include many products
1 product can have many unit prices
1 unit price can include many products
I have 2 one to many relationships as follows:
1 Category can have many products but a product can only be in one category.
1 store can have many transactions but a transaction can come from only one store.

The import wizard can only work with 1 many to many relationship and I am having a hard time getting this information imported from excel into access so I can work with it. Any suggestions? I have been reading and watching tutorials about relationships and importing data but I can't seem to get the gist when there are several many to many relationships.

I want to be able to look up a product and see which stores I have purchased it from, what brands, the unit quantity and the unit price from each different store. I hope this all makes sense to someone

Thank you in advance for any help in this...

[ranman256]

No need for the wizard. Build generic names for this repeating import...

Save wb to the generic file Inv1.xls
Link your excel sheet as a table: xlInv1
Build a query to append the data, qaImportInv, to import data from xlInv1 to the target data table.

Everytime you get a new excelbook,
1. save the xl file to the same generic file INV1.xls
2. run import qry.
As long as you are not missing keys, relationships wont stop the import.