=> SQL Insert from Temp table

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

I would like to know if there is a neater way to do
what I am currently doing.

The User needs to be able to 'Copy and Paste' from an
excel spreadsheet into an Access datasheet. I need to
take this data and make sure that the insert includes
the ID numbers for the Supplier and Customer. I am
therefore calling a macro with losts of insert queries.

For example:

DATA IMPORTED INTO DATASHEET:
=============================

[Title]DeliveryDate Cust1 Cust2 Cust3 ....
[Imported] 16/09/03 2 3 1
16/09/03 1 4 0

The important bit is the date and the pallet quantity
delivered to each customer.

I then grab this information and assign a customer and
supplier ID based on it's placement in the temporary
table.

SQL QUERY USED TO SAVE INTO DELIVERIES TABLE
============================================

INSERT INTO tblDeliveries ( supplierID, customerID,
deliveryDate, palletQty )
SELECT 1 AS supplierID, 15 AS customerID, [tblImport].
[DeliveryDate], [tblImport].Customer1PalletQty
FROM tblImport
WHERE Customer1PalletQty>0;


INSERT INTO tblDeliveries ( supplierID, customerID,
deliveryDate, palletQty )
SELECT 5 AS supplierID, 12 AS customerID, [tblImport].
[DeliveryDate], [tblImport].Customer2PalletQty
FROM tblImport
WHERE Customer2PalletQty>0;

etc... a query for each customer

Can I create one insert statement or approach the problem
differently.

Thank you for any suggestions.

Kind Regards
Rhonda
 
Back
Top