Merge two excel speadsheets 1

[fh2level]

I have a master personell spereadsheet that I have connected to an Access database. About twice a month a new personell list is given to me. This list contains the most up to date people, meaning that if someone has left they are not on the list anymore. This is my problem, because even if someone has left I still need them on the list for my database. So I can not just replace the old list with the new, I need to merge them so I do not have duplicates, keep the old and add the new.
Any suggestions?

 

[SkipVought]

Hi,

You can use MS Query to join two tables

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[DirkStruan]

fh2level

Sorry but I'm confused.

You run a personell (access) database and each month you get given a revised list of current employees (in excel) and you need to update the database to reflect new joiners and leavers, but maintain information on everyone who has worked there? How are the spreadsheet and the database connected? How are the employees uniquely identified?

(I assume you are talking big numbers of employees and need to make the process easy.)

What will happen if someone leaves and then rejoins - what information do you need?





Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[fh2level]

Sorry, im having a hard time explaining

The employees each have a unique ID number, currently the Excel table is Linked to the corresponding table in Access so it auto updates everytime that excel file is changed. The problem is the new excel file does not contain the names of the people who have left, in turn deleting them from the table in the access database. But these names need to stay in that table in the database. The amount of names is to great to go through everytime to see who is still there and who isn't so I need a way for the excel file that is linked to the access database to look at the UPDATED personell sheet sent from HR and only import the new personel. I hope that makes more sense . Thanks for the help

 

[DirkStruan]

Ok, working in Access:

I'll assume your excel sheet consists of a unique ID number and some other data.

Essentially your linked Excel worksheet is just another table. Now referred to as Etab.

I'll also assume you are starting from scratch (as I am!).

Set up a new Access table with the same titles as Etab. Leave it empty. (This is referred to as Atab)

Goto queries in design view and add both tables.

Select Employee ID in Etab and drag it to Employee ID in Atab to create a link.

Right click the link and select option 2.

Next pull all the fields from Etab down to the fields to be shown in the result (you could just use the * field at the top of the list to select them all).

Also add the Employee ID from Atab - staying with this field, put in the criteria "Is null". Switch the view to Datasheet view. This is the list of the Members in the excel sheet that do not form part of the current table.

Then change the type of query to an append query (dropdown just under the help menu) and tell it to apend to Atab.

You will now have an "append to" row down on the selected fields - delete the target field for the "is null" field.

Run the query.

The new employees have been added to Atab.

If I am right, that's all you need to do, just open access and run the query each month.



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[fh2level]

Thank you so much

 

[DirkStruan]

No worries - I think it was someone on TT who pointed me to the following site, well worth a look:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

Regards

D