Filter out imported excel data from table

[Rai]

Hi guys and gals,
Now hope ure comfy, I have a database to manage staff on a helpdesk and every month we import stats from out telephone system into excel (this is the only format that it can write them in) then i have writted a little vb script in access to import them into a table but, in the excel file there are rows that have people in who are not from out desk that i dont want adding into the DB Table, but these have to be in the excel file.

So either somewhere during the import prosess from excel to Access or Maybe i could import the data into a temp table and then filter the information from there to the actual table.

What i want to happen is once the data is imported from excel i want a script/query to look in the name column of the imported data and then see if that name is in a table holding all the staff details (See below for tables info) and if the name is in the Staff Details table then copy that record to the Stats table and if the name is not in the Staff details move to the next one not delete it because on the next month the script to import deletes all data in the temp table anyways.

TABLES
----------

Excel Sheet
¦
Temp Table------------- Personal Information
¦ ¦
----------Script/Query------
¦
¦
Stats Table


Thanks,
Rai

 

[KeithG]

Actually if you set up you relationships write I don't think you would have a problem. Make a one to many relationship with the employee table as the primary table and enforce referential integrity. Now you will not be able to add a record for someone who is not in the employee table.

 

[Rai]

Thanks but ?

Thanks keithG but could you maybe explain how to do that i aint got a good backgroud on relation ships. if i PM'd my eail addess could you mail an example.

Thank you for your assistance so far

 

[Rai]

Referential Integrity

In response to my previous question i have found a link that shows just how to set up the link.

http://www.jmu.edu/computing/ittraining/tutorials/microsoft/access/relationships/setting.shtml

I think if anyone would like to do just what i am doing. Thanks to KeithG this is now possible.

Thank You so much Keith.