[AccessD] Matching Names & Qualifications

Now I can bet you knew I was going to ask this question next :-)

To make a form where they can select the Engineer so it will show all their
qualifications.

I make a combo box from tblEngineer (EngineerID & Engineer:FirstName&"
"&LastName)

A subform with:
SELECT DISTINCTROW tbl_Qual.Qual, tbl_Qual.QualID, tbl_Engineer.EngineerID
FROM tbl_Qual INNER JOIN (tbl_Engineer INNER JOIN tbl_EngQual ON
tbl_Engineer.EngineerID = tbl_EngQual.EngineerID) ON tbl_Qual.QualID =
tbl_EngQual.QualID;

Hopefully when I select the Engineer from the combobox it will list all the
Qualifications for that engineer in the subform.

Virginia

-----Original Message-----
From: Lembit.Soobik at t-online.de [mailto:Lembit.Soobik at t-online.de]
Sent: Saturday, March 15, 2003 7:36 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Matching Names & Qualifications


Virginia,
with 3 tables you can have a many to many relationship:

tblEngineers
IDengineer
EngineerName

tblQualifications
IDqualification
Qualification

tblEngineerQualification
IDenqual
IDEngineer
IDQualification

in tblEngineers you have
1   Jack
2   Fred
3   Louis

in tblQualifications you have
1   SeniorDesigner
2   JuniorDesigner
3   Reviewer

and in tblEngineerQualification you can put as many combinations as you like
1   1   3
2   2   1
3   1   2

means Jack is a Reviewer and a Junior Designer
while Fred is a Senior Designers

Lembit Soobik

----- Original Message ----- 
From: "Hollis,Virginia" <HollisVJ at pgdp.usec.com>
To: <accessd at databaseadvisors.com>
Sent: Saturday, March 15, 2003 2:11 PM
Subject: RE: [AccessD] Matching Names & Qualifications


> So even though I will have duplicate qualifications listed this is OK?
> 
> Senior Eng, eng name 1
> Senior Eng, eng name 2
> Reviewer, eng name 1
> Reviewer, eng name 3
> 
> -----Original Message-----
> From: Mwp.Reid at Queens-Belfast.AC.UK
> [mailto:Mwp.Reid at Queens-Belfast.AC.UK]
> Sent: Saturday, March 15, 2003 7:05 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Matching Names & Qualifications
> 
> 
> Create a Link Table
> 
> QualificationHeld
> 
> This is used to link the Employee to teh Qual
> 
> Martin
> 
> Quoting "Hollis,Virginia" <HollisVJ at pgdp.usec.com>:
> 
> > I need to make a table that lists the name of the engineer and their
> > qualifications. To do this should I make a table of qualifications and
> > another table of engineers - then how would I brink them together? Or
> > have a
> > list of qualifications and link to an engineer table?
> >  
> > tblQual (lists the qualifications and brings in the eng from the
> > engineer
> > table).
> > Senior eng, Me
> > Senior eng, You
> > Reviewer, Me
> >  
> > The final result they are wanting is if they select a qualification, it
> > will
> > list all the engineers with the selected qualification. Also, if they
> > select
> > an engineer, it will show what they are qualified to do.
> >  
> > Virginia
> > 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com