If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup field from linked tables - can it be done?
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? |
#2
|
|||
|
|||
Lookup field from linked tables - can it be done?
On Thu, 30 Oct 2008 02:37:01 -0700, Astrid
wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Lookup field from linked tables - can it be done?
Thanks John,
This is loads simpler and makes a lot more sense! Astrid "John W. Vinson" wrote: On Thu, 30 Oct 2008 02:37:01 -0700, Astrid wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|