Lookup Question - with a difference!

[commandolomo]

Hello all

I have been tasked with organising and developing some HR databases, where currently there are 8 or 9 different databases, all with duplicate personal info, but used for different issues - e.g. disciplinary, role details, attendance etc. I plan to merge and reduce this number to about 4 databases overall.

I have no problem in sorting out these databases, and have recently learned the joys of using combo boxes to auto fill other fields!

However, what I would like to do is for my lookups to pull info back from a file that is not within the db, from a separate location. For example, the file, (excel or access) would have personal info, name address, dob, address etc, which will be changing daily, and the 4 databases could all pull data from this one source, instead of having to update all 4 databases with a daily changing file.

Would anyone have any idea how to do this? Is it possible? Can Access manage it through queries, or is VB the way forward? Is the solution as easy as having an linked table to an excel sheet?

Thank you very much for any ideas

 

[KenHigg]

My suggestion would be to push as hard as possible to get all the data into one database or at least in to Access databases.

If you have users doing data into spreadsheets and you get it in Access by linking or importing, you will always have to have someone baby sit the spreadsheets to make people don't muck up the format or add or delete rows, cols, etc. If one thing changes in the xls then you need someone with your Access skills to straighten it up...

Just my opinion...

kh

 

[commandolomo]

kh

Many thanks for your reply. Users will be using Access to review, input and look at the data, and users will not have access to the source data. This data will have stuff like name, address, dob, which will be needed by the various different databases - I am trying to make duplication of data entry is kept to a minimum.

The source file is be updated daily, and so if I can link the various database fields to this one file, then I do not have to update all of the db's, only this one file.

With this in mind, is anyone aware as to how I can link fields/combo boxes to cells/fields in another db/spreadsheet?

Regards

 

[WayneRyan]

c,

Use: Files --> Get External Information --> Link Tables

This will create a link to a table in another database. Use it just like any
other table.

Wayne