|
Access SQL INSERT
I'm new to SQL, so forgive my ignorance.
I have a number of related tables. Of these there are 3 which are of concern to my problem: Invoices, InvoiceItems and Products. There is a 1:M r/ship between Invoices as well as between Products and Invoice items. The Invoice items table has InvoiceItemID, InvoiceID, ProductID, Quantity and Price.
I would like to add 3 InvoiceItems as records in my InvoiceItems table and i would like to have them related to one Invoice (the last Invoice in my case) in the invoices table, that is, also the field in the InvoiceItems called InvoiceID for these 3 records should have the same InvoiceID. All the Primary Keys are set to AutoNumber.
These are the the statements i used:
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 8, 1, 0);
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 9, 4, 0);
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 10, 2, 0);
I would like to do this using some sort of INSERT SQL statements, but it's just not working. I can INSERT one InvoiceItem, but when i try to INSERT the next one i get some long error saying something about "type conversion failure","key violations", "lock violations" and "validation rule violations"
Does anyone know why this is happening and how to correct it?
Any help would be much appreciated.
|
Before you post, read our: 
