Appending from Excel to Access

I am trying to import the above excel file using "append" to my invoice_details table where Invoice is looked up from Invoices, an order is looked up from the orders table, and a unit is looked up from the units table.  The order already exists in the orders table, the invoice already exists in the invoices and the unit already exists in the units table. 

What you need to do is create an append query in which the imported 'master' table is joined to the Invoices, Orders, and Units tables on the non-key columns.  You can then append primary key columns of  Invoices, Orders, and Units to the foreign key columns in Invoices_Details.  The following query from my demo is analogous to this.  It inserts data into two rather than three foreign key columns, but the principle is the same:

INSERT INTO ContactEmployers (ContactID, EmployerID)

SELECT Contacts.ContactID, Employers.EmployerID

FROM (Contacts INNER JOIN MasterTable

ON (Contacts.Address=MasterTable.Address)

AND (Contacts.LastName=MasterTable.LastName)

AND (Contacts.FirstName=MasterTable.FirstName))

INNER JOIN Employers ON MasterTable.Employer=Employers.Employer;

What if the Excel file does not get data from a combo box, it does not look up but simply entered? 

My demo illustrates exactly that situation.  If the value is that of a non-key column in a referenced table in Access, which is referenced by a numeric foreign key column in a referencing table, then the value of the primary key of the referenced table for that row has to be obtained in Access and inserted into the foreign key column in the relevant row in the referencing table.  This is what the queries in my demo do.  Download and study it first, and then get back to us with any further questions.

Excel data is usually not organized in a Relational way. For example if you import an InvoiceDetail record, the InvoiceID must exist in the Invoice table.

It is often better to Link the Excel file, then run several append queries to put the data where it goes: first create some Invoice records, then add the InvoiceDetail records.

In Access a foreign key column which references the primary key of a referenced table in a one-to-many relationship type will, in most cases, be a long integer number data type.  The primary key of the referenced table will, in most cases, be an autonumber, which is also a long integer, but whose value is automatically inserted when a row is added to a table.  By setting the width of the first column of a combo box which is used to look up a value in such a foreign key column to zero, the number is hidden and you see the value from a non-key column in the referenced table.

To be able to import values from a column in Excel which also looks up a value would therefore require the column in Excel to have exactly the same numeric values as those in Access.  This will not be the case, which is why you get the errors.

Importing data from Excel into a set of related tables in Access is far from straightforward.  It can be done, however, by firstly importing the non-normalized data as a single table, and then decomposing it into a set of correctly normalized tables in Access.  You'll find an example of how this is done in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file data is imported from an Excel worksheet into a temporary non-normalized 'master' table in Access.  This data is then decomposed into a set of correctly normalized related tables by executing a series of 'append' queries in a specific order.  The rule of thumb is that rows are first inserted into the referenced tables in each relationship before rows can be inserted into the referencing tables in the relationship.  My demo steps through these queries, with a brief explanation of each as it is executed by means of a button in a form.

Regarding the other response to this thread, this forum is regularly plagued by spammers, using a name followed by 'Expert' and a number.  You'll note that Jack Expert857 has only been a member of this forum for the last twenty minutes.  The post has now be taken down, but you may well have received an email notification of the reply before this was done.