importing excel files to access

[NTxp]

hello everyone,

i am a journalist. i wanted to create a namebased databank for my archive.
years ago i started to collect names
i have a book/document contains names and pick up the names, entering the all names in excell format in 4 columns

name
surname
who
which page

for classify i am giving the title of the book to excel file

i entered 55 books i mean i have 55 excel files (total 32.000 names-by the way what is the limit of access)

i could not manage to import these excel files to access
i had the following problems

a person may appear in 6 different books
a person may have different titles (like dr, german, veteran)
a person may have exacly same name with the other person
do you have any access template i can use for namebasing

how can i prevent the problems
what do you suggest me

thanks everyone

best regards

 

[shelron]

wellll, if your saying that you have distinct individuals with the same name, not duplicate records, then:

I would merge all the workbooks into one book, then create a unique identifier for each row by adding an "autonumber" field, (don't think you can actually do that in excel, except by starting with 1 then copy down)

then import the worksheet into access and when the wizard prompts for a primary key, use the autonumber field you created.

ron

 

[NTxp]

hi shelron,
thanks for your attention
the problem has duality faces

we have JOHN SMITH.
he is a soldier and mp (i mean he seems a soldier in book1 and seems senator in book 2)
preventing this looking:

john-smith-soldier-book1pg.23
john-smith-senator-book14pg.67

(i mean is there any chance for merging like that)

john-smith-soldier/senator-book1pg.23/book14pg.64

in additionally

we have one more JOHN SMITH (or more) but he is no corelation with the previous one, he is farmer
how can i distinct them?

i hope i can describe my problem

thanks again

 

[GDGarth]

I'd try this:

Give each individual an identifier (unique number)

Create a table (NAMES) in Access listing ID Numbers & Names (ID Number as Key Field)

Create another table (OTHER DATA)listing the rest of your data, along with the ID Number (ID Number has to allow duplicates, in this table, so it can't be the Key Field).

The relationship between NAMES and OTHER DATA is one to many.

You can then create queries to combine the data as needed.

 

[vistor]

What a big task!
Bring all your workseets into one.
In the single sheet, Maybe have your Excel columns like this: Assign a number to each contact, when you import the Excel file have Access define the primary key, which will become an Auto number field.

Contact_ID - a number
First_name - text
Middle_Name - text
Last_name(Sirname)- text
Role or Type - (who - do you mean their role or as you said title - farmer, senator?)- text
Book - text or however you want to identify them
Page_number - number

In Excel you can use the Text to Column function to break up one column into more than one. If the Name is now all in one column, as Joe D. Smith, you can break it up into 3.

You are ging to want to have separate tables in Access. So after you get your single Excel spread sheet, you are going to have to create Seperate excel files, one for each table you need in Access.

Each contact will have a separate row in the excel file for each role or type they have. Following is an example for John D. Smith

Type file - will become type table in Access.
Contact_ID Role
1 Farmer
1 Senator

Contact file - will become Contact table in Access.
Contact_ID - a number
First_name - text
Middle_Name - text
Last_name(Sirname)- text

Type file
Role or Type - (who - do you mean their role or as you said title - farmer, senator?)- text

Book file - will become book table in Access
Book - text or however you want to identify them
Page_number - number

Is this making any sence to you. Do you have an experience with Excel or Access? You may need hands on help. Hire a college student?

Look at this Contacts database template for ideas.

http://search.officeupdate.microsoft.com/TemplateGallery/result.asp?qu=contacts

 

[NTxp]

thanks all of you