import existing excel data in new access table

B

bb

Hi,
I have a problem.
I have some existing data in Excel sheets. For each employee I have one
Excel sheet with dates and various results per date.
now I have tables in Access in wich I want to import data from the
Excel sheets. In my Access table the field employee is an look up
table. Also the date field is an look up table.
now is my question how to import these data from Excel into Access with
holding the information per date (a look-up table) and employee (a look
up table).
I would appriciate if someone could help me out with this.
Or could point me to a existing topic on this.

Thanks in advance, Bernadette
 
K

Ken Snell [MVP]

I recommend that you import the data from one sheet into an ACCESS table.
Then you'll need to devise and run various append queries to copy the data
into the correct tables, and in some cases to replace a value with the value
from a lookup table. Doing this is a bit of an "art" and depends entirely on
the table/data setup of your database.
 
J

jahoobob

You can import one sheet into an Access table and continue importin
sheets into the same table (they will append to it) as long as th
sheets are identical i.e. the name is in the same cell and everythin
is in the same row in the sheet. You can then create a Find duplicate
query on the new table looking for duplicate names, change it to a Mak
table query and run it. Delete the Duplicates found column in the ne
table and you have your lookup for employee. You need to do this s
the names are the same. If you have a current table that has Adams
James and he is listed in new table imported from Excel as Adams, Ji
you won't have an exact match. You would have old data stored a
Adams, James and new data stored as Adams, Jim.
Do the same for the dates table (find duplicates), although I don'
quite understand why you want to do this. A calendar would be a lo
better way to look up dates.
Hope this helps,
 
B

bb

Thanks,
and can you tell me how I should run those append queries? And how to
put some "art" in this?
Bernadette
 
K

Ken Snell [MVP]

Without knowing something about your data, I really can't give you any
specific suggestions. Some generic steps are
-- fill the parent tables first so that you'll have the values for the
linking fields in the children tables
-- fill the children tables "one step" down from the parent table next
-- continue until all children tables are filled

Append queries are just SELECT queries with an added clause to INSERT INTO
the new table name. ACCESS QBE window makes this fairly easy to design.
 
B

bb

Thanks again. Later this day I will try all the suggestions given.
I will let you know if I was able to implement it.
 
Top