A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup field from linked tables - can it be done?



 
 
Thread Tools Display Modes
  #1  
Old October 30th, 2008, 10:37 AM posted to microsoft.public.access.tablesdbdesign
Astrid
external usenet poster
 
Posts: 17
Default 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  
Old October 30th, 2008, 05:12 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old October 30th, 2008, 05:50 PM posted to microsoft.public.access.tablesdbdesign
Astrid
external usenet poster
 
Posts: 17
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.