Need help building a directory.

[dezza9995]

I have made an Access database containing contract names and addresses. I need to have each of these contracts linked to another table which shows the allocated engineer for the particular contract. I used a "one 2 many" relationship using a field "LinkID" to do this. Each engineer has a unique "LinkID" in the engineer table but the engineer changes each day. The engineer for each particular day comes via an excel spreadsheet. The way it works at the moment is that the engineer name has to be manually changed each day, and this takes time. I have tried importing a linked excel table which looked like it would work, but was not able to use the linked table in the same "one 2 many" related table format as above.

What i need to do, is find a way for the fields containing the engineer name to be automatically updated somehow, possible by reading the values from the excel sheet.

Anybody know of any way for this to be done?

Thanks, and hope this makes sense got its got me baffled.


*** Edit **** Thinking about it, i could have an imported execl sheet which contains all of the engineers for each day. Would there be a way using a macro, to update the data in the engineers table that is related to the contracts, by overwriting it with the data from the linked spreadsheet?

 

[FoFa]

One or multiple queries should be able to handle that. How ever if I understand correctly, you are losing the history of an engineer that was assigned to something. Might not be important, but a simple cros reference table (tying contacts to engineers) with a date and yiou could have history also.

 

[dezza9995]

The history of the engineer is not a problem, as long as it is the correct engineer showing for that particular day.

 

[dezza9995]

So what i have got so far is the main contract database linked to a separate engineer table. I have an linked excel sheet which contains all the daily engineers. The linked excel data and the engineer table are identicle in format, but how do i make it so the data from the linked excel table is copied over the data in the engineer table? I can do it manually using standard copy/paste technique but want it to be automated.

 

[FoFa]

I don't think overlaying is what you want to do, you just want to update the links betwen the two tables is all (change the foreign key). If the foreign key in the Contacts table is all, just update it with a query from your spreadsheet to point to the proper engineer.