using same queries and talbes scenario...

[koolage]

here is my scenario,

I have tables and queries designed for an insurance project that I am currently working on. Every month I import an excel file which lists the employee, type of coverage, particular plant the employee works for (there are three plants) and the amount the employee contributes. I create the same monthly reports using this data. (NOTE: the categories are always the same(e.g. employee, type of coverage) the only changes that can occur every month are either new/deleted employees and contribution amounts.

Question:

How can I use the same queries and tables on a monthly basis that includes the updated info (per NOTE above) ?

thanks in advance for any help in the right direction,

 

[randy700]

Establish a link to the excel file.
Design 3 queries to update the data in your tables.
Query 1 will remove employees that are in your table but not in your spreadsheet.
Query 2 will add new employees from the spreadsheet.
Query 3 will update contribution amounts.

That said, I would suggest adding an additional field to your table to indicate if the employee is "active". Rather than remove them, change the status to "inactive". This will come in handy if you are ever audited.

Use the query wizards to look for and remove unmatched records.


Randy