Lookup a definition/field in a query or a report

  • Thread starter Thread starter Mark Corigliano
  • Start date Start date
M

Mark Corigliano

I have what appears to be a simple issue, but cannot find
the answer. Your help is appreciated.

I have two tables. The first table, called PLEP stores a
number of codes (i.e.: S01, S02, etc.). There are ten
such fields in this table. The second table, called
SKILLS lists these codes and a description for each
(i.e.: code S01 has a description field "John is a good
listener"). I want to create a query (to be used for a
report) or a report that shows or prints the description
field from the second table. Again, there are ten such
fields in in PLEP that all reference the same SKILLS
table.

Again, your help is greatly appreciated.

Mark C.
 
Mark said:
I have what appears to be a simple issue, but cannot find
the answer. Your help is appreciated.

I have two tables. The first table, called PLEP stores a
number of codes (i.e.: S01, S02, etc.). There are ten
such fields in this table. The second table, called
SKILLS lists these codes and a description for each
(i.e.: code S01 has a description field "John is a good
listener"). I want to create a query (to be used for a
report) or a report that shows or prints the description
field from the second table. Again, there are ten such
fields in in PLEP that all reference the same SKILLS
table.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Redesign the PLEP table so that all the number codes are in one
column. The way to do that is to use the current code column names as
values in the new table. E.g.:

Old PLEP:
ID
SocietalSkill
AdaptiveSkill
MathSkill
... etc.

Example data for Old PLEP:

ID SocietalSkill AdaptiveSkill MathSkill
1 S01 S33 S55
2 S05 S30 S57

New PLEP:
ID - Assume this is an EmployeeID ?
SkillCodeID - References SkillCodes (SkillCodeID)
SkillValue - References Skills (SkillValueID)

And another table for the SkillCodes & their descriptions:

SkillCodes:
SkillCodeID AutoNumber
CodeDescription Text

Example data for SkillCodes:

SkillCodeID CodeDescription
1 Societal Skill
2 Adaptive Skill
3 Math Skill

Example data for New PLEP:

ID SkillCodeID SkillValue
1 1 S01
1 2 S33
1 3 S55
2 1 S05
2 2 S30
2 3 S57


The query to get the SkillValue descriptions (untested):

SELECT P.ID,
SC.CodeDescription As Skill,
S.SkillDescription As Assessment
FROM (PLEP As P INNER JOIN Skills As S ON P.SkillValue =
S.SkillValueID) INNER JOIN SkillCodes AS SC ON P.SkillCodeID =
SC.SkillCodeID

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFoe34echKqOuFEgEQI95wCg9CEFbAkc65yxeI1qCVbg5Dt556oAoI5M
JdSJul8Il9aA/sPafQ40okkL
=xvpf
-----END PGP SIGNATURE-----
--
 
Back
Top