How to use primary key to import more data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a db using Office 2000 on a WinXP machine, importing
data from Excel. I have Excel files with at least 5000 entries and about 75
column headings/Field names.

I understand that the primary key needs to be set up for each individual,
however, some individuals have two or three listings because their names have
changed (Marriage, legal name change), and some individuals have numerous
entries due to repetitive entries under one Field (several dates, multiple
awards, etc.)

I tried to import all the data at once, but the list of errors was massive.
Everything from a blank space in a cell to cells improperly formatted. Due
to the size of the spreadsheet, I decided to start off by importing only the
Name Fields in order to get a PK for each individual.

I did this by sorting the names in Excel, and adding two columns. In one
column I numbered each entry sequentially using this as my control to get
back to the same order, and copied that column to the second one.

Then I went through the whole list, and in the second column deleted all
numbers of those entries which were repeats of the same person. Thus when I
sorted using the second column, I had a list of all individuals without
repeats. Then I added a third column and numbered each entry sequentially
for those second column entries. I imported the names and the third column
numbers into Access using the Wizard, and created a PK out of the third
column.

And now I have reached the Peter Principle (at the end of my knowledge).
How do I attack the importation of the rest of the data, i.e. both the
multiple names for the same individuals, and the rest of the columns of data
which belong to those individuals?

And a related question: If I import two columns of data (Awards and the PK
number for those individuals who got the awards) into a new table and
normalize it, do I set up a relationship between the PK of the names tbl and
the PK of the Awards tbl?

I guess what causes me great confusion is that I must have a PK that is
unique, yet I have three people who are all the same person. My apologies
for my obtuseness, and my thanks for your help and patience.
 
I think your trying to hard in a spreadsheet to normalize your data. First
trick is import the whole spreadsheet into access in one table. Once it is
in access you can use a series of queries to normalize it.

Not sure what errors you got when you imported the file. Are you making a
table and then
trying to import the spreadsheet into that table? Much easier to just
import as new table and then manipulate it in Access.

This will help prevent data type conversion errors. Also will help to keep
all portions of
related data together. Keep the new table as is after the import. Copy it
into another table and leave the original as is. Gives you a backup if you
mess up while normalizing tables. Also keep a backup of the spreadsheet at
the time you import. Don't use the one still being updated as if you need
to compare records later between the spreadsheet and what you have in access
it won't be as confusing.

Primary Key fields will be done after you organize your tables and fields.

Before starting you should get a stubby pencil out and draw your tables,
fields, keys and relationships. Not saying what you draw will be exactly
same as finished poduct but will sure help
keep you orginized.

Prep your spreadsheet to be imported.
1. Simplify column titles, I eliminate spaces with _ in titles remove
symbols. Remove any forced returns within single cells. Access doesn't
like them.
2. Make sure first row of data is complete, if first row of data following
the colum headings has a blank and next row has a date access will format it
as text and probally give you the serial date number in a text field in your
new table.
3. Name the range or ranges you are going to import and use them to do the
import.
4. Unhide all columns and rows in the spreadsheet so you can review all data
before importing.
5. If columns have different data types in them (date, time, text) import it
as text and straighten it out after it is imported. Use the import
specification within access.

Pete D.
 
Thank you Pete, I will follow your directions and let you know what happens.
Two things you mention jumped out at me. The first line has many of the
columns blank. I see the logic of setting up an "example" individual with
something in all columns. The other thing is that I have to learn how to use
queries to do the normalization.
Thanks again for your time. R-
 
OK Pete, here is what happened.

I imported the range of alldata from Excel. Access warned me that not all
data could be imported.
Question: How do I know what data did not get imported?
There is also an Import Error table with a thousand items of Type Conversion
errors, all of which are blank cells in Excel.
Question: Are these errors in this table the same data that could not be
imported?

Secondly, if I let Access assign a PK to the entries, I lose the connection
between those people who are listed more than once.
Example: Joe won 6 awards and thus is listed in Excel 6 times in the Awards
and Name columns (At this point, only their personal ID or my sequential
numbering system indicates they are the same individual.), and Access assigns
him 6 PKs. This is especially critical when there are numerous individuals
named John or Jane Smith.
On the other hand, if I try and assign the PK based on my first column of
sequential numbers (or their personal ID), Access refuses because that
assignment would create duplicate values. ("Change the data in the field or
fields that contain duplicate data, remove the index, or redefine the index
to permit duplicate entries, and try again. ")
Question: How do I get Access to accept numerous entries for the same
individual without having the primary data (Names) repeated?

This was my problem in the first place and why I went through the business
with two columns of sequential numbers to get a PK for each individual.

Again, thanks for your help and patience.
 
Do the import in several steps
First Import to a Temp Table "As Is"

From There Append the Unique values to a Employee table, or whatever
You can then add the non-unique data to an EmployeeDetails table if you need
it.

HTH

Pieter
 
Back
Top