Import the data from Excel as is. Let's call this table AsImported.
Create a query based on this table that returns one record per customer. On
the basis of your sample data it will look like this in SQL view (the
DISTINCT keyboard corresponds to the "unique values" property in query design
view). Let's call it tempImportedCustomers:
SELECT DISTINCT [CUS ID], [CUS NAME], [CUS ACCT]
FROM AsImported;
Then create your Customers table with whatever fields are needed, including
CusID and CusName (things are simpler if you don't use spaces or special
characters in the names of fields and other objects).
Finally, create an append query that gets its data from
tempImportedCustomers and appends the records to the Customers table.
--
John Nurick
Microsoft Access MVP
jk said:
CUS ID CUS NAME CUS ACCT INV NUM INV AMT INV DETAILS EXP aCCT
10030 MONI 20023 M2503 $100.00 TRVEL 30025
20011 YESI 12500 M2600 $250.00 ENTER 10025
12530 WHASI 30025 M2606 $375.00 RENTAL 20025
10030 MONI 20023 M3000 $1,500.00 RENTAL 16402
I have inserted a sample in that the rows in excell are repeated many times
like CUSID 10030. In access, i beleive it to be three tables, one for cus,
one for invoice,one for exp acct. The import recognizes the dup in the CUSID
column but it should be the unique id so what is the best way on importing
all data while ensuring that primary key is CUSID?