Lookup field from linked tables - can it be done?

[Astrid]

Ok, in my Access 2003 database I now have 4 key skills tables;

tblEQUIPMENT - listing equipment iinformation (approx 250+ pieces)
tblMETHODS - listing analytical methods to use in conjunction with the
equipment from the EQUIPMENT table (approx 105 methods)
tblTRAINING - listing all training courses (currently 250+ courses and more
added all the time)
tblSOFTSKILLS - random stuff that is useful, eg. good at grammer, wrting
skills, maths, graphics, more hobby type stuff really (huge list of these
skills)

I also have two linked tables from a current Training Database;
tblCOURSES - listing all courses that are/have been run
tblEMPLOYEES - lists all employees

What I would like to do is this - create one table which incorperates all
the information from the tables to show what key skills each employee has;

tblEMPLOYEEKEYSKILLS
EmployeeID - from the linked employee table
EquipmentID - lookup from Equipment table
MethodID - lookup from method table
TrainingID - lookup from Training table
SoftSkillID - lookup from SoftSkills table
Trained - completed if trained
Competent - completed if competent
Expert - Completed if expert
Comments - Memo column

I am hitting trouble as I am trying to create lookup columns from the linked
tables Employees and Courses, but access wont let me.

The reason I linked the tables was to prevent having to input information
into both the Training Database AND the Key Skills Database when there is a
new employee/training course, but can just input data into one and 'update'
the linked tables.

Is what I'm trying to do possible, or is there another way?

 

[John W. Vinson]

I am hitting trouble as I am trying to create lookup columns from the linked
tables Employees and Courses, but access wont let me.

The reason I linked the tables was to prevent having to input information
into both the Training Database AND the Key Skills Database when there is a
new employee/training course, but can just input data into one and 'update'
the linked tables.

Is what I'm trying to do possible, or is there another way?

There is. Use a Form.

Working in table datasheets is VERY limiting. Lookup Fields (as you have seen)
cannot span multiple databases, and are generally considered a Bad Idea
anyway: see
http://www.mvps.org/access/lookupfields.htm
for a critique.

Instead, use combo boxes - "Lookups" if you will - on a Form. Store the
SkillID in your table, but use a Form to enter the data. You will not be able
to enforce referential integrity across the linked tables, but if the user's
only way to enter data is through the combo box on the form, you shouldn't be
at much risk.

 

[Astrid]

Thanks John,

This is loads simpler and makes a lot more sense!

Astrid