Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

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

    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...

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  2. Importing from Excel 2007 into Access 2007
    By jk1809 in forum Import/Export Data
    Replies: 8
    Last Post: 09-06-2012, 09:46 AM
  3. Issue with importing excel data
    By Jrbeene86 in forum Import/Export Data
    Replies: 0
    Last Post: 03-27-2012, 08:32 PM
  4. Importing Excel 2007 spreadsheet into Access 2002
    By jhjr in forum Import/Export Data
    Replies: 1
    Last Post: 06-17-2010, 02:05 PM
  5. MS Access 2003+Ms Excel 2007 Issue
    By putnum in forum Access
    Replies: 3
    Last Post: 12-20-2009, 09:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums