Importing Excel sheets

[Guest]

I have a workbook with 4 sheets of data that has columns to identify invoice
information, customer information, expense acct information. I would like to
have the customerID as the primary key since it is unique but on importing i
receive the error it cant be done due to duplicates. The many rows have
repeating info so how do you import this?

 

[Guest]

This is an MS Acees newsgroup. Try an Excel newsgroup

 

[Guest]

My mistake..........I am trying to do this import into access and since the
customer Id would be repeated in many rows, how do you import this
information while ensuring the customerID will be the primary key?

 

[Guest]

You must importing to an existing table. Check your table an how you set up
your key/unique fields. Try importing to a new table. It may be easier to
identify the problem.

 

[John Nurick]

It sounds as if the CustomerID is *not* a unique identifier in this data
- and if the data includes information on invoices I wouldn't expect it
to be, because in most circumstances there is (at least potentially)
more than one Invoice per Customer.

A good approach in this sort of situation is

1) Use File|Get External Data|Link to create a linked table connected to
each of your four worksheets.

2) Make sure you understand your data and what your database needs to
do, and work out a normalised data structure to suit.

3) Create tables and relationships accordingly.

4) Use a series of append queries to extract the data from the linked
tables and insert it into the normalised tables.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

Hello John,

I have three tables, one for invoices, one for customers and one for expense
accounts. There are no autonumbers in any of these tables but i have to ask
you have described an append query to one table though my spreadsheet will
have data three tables. I do not undertand the concept of creating one table
to do an append query.Can you assist a little more?

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

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?