DLookup Problem

[Col]

Hi all,

Probably one of the many having fun with Dlookup!

Wonder if anyone can help.

Trying to produce some data on whether certain members of staff have the
correct training for a certain duty based on up to five different
qualifiers.

My query works fine if fed with say a parameter with just one staff member
but when additional staff members are added the Dlookup formula I wrote goes
wrong.

I have five fields in a table named;

Skill01

Skill02

Skill03

Skill04

Skill05

Which is the base for the query, plus another master table which Dlookup
refers listing all training done and the name of the skill in the field
[Skill].

The formula is

SK1 Look: IIf([Skill 01] Is Null,"N/A",IIf([Skill
01]=DLookUp("[Skill]","Training","[Skill] = '" & [Skill 01] &
"'"),"Yes","No"))

I'm thinking that Dlookup can't handle multiple instances of the same lookup
criteria and it just returns the first one it sees.

If so, would there be another way of producing the data, for example if I
have a list of staff to query, is it possible to run the query just on the
first row (i.e. staff name) and then again on the second etc, all the time
appending results into another table?

Thank you for any help.

Colin.

 

[Jason Lepack]

A wise idea would be to store your data in this fashion:

tblEmployee:
employeeID - Primary Key
employeeName
etc. (any other information about the specific employee)

tblSkills:
skillID - Primary Key
skillName
etc. (any other specific information about the skill)

tblTraining:
skillID - Primary Key, Foreign Key
employeeID - Primary Key, Foreign Key

Then under Tools -> Relationships, you would add these three tables and
join:
employeeID to employeeID
skillId to skillID
and enforce referential integrity in both cases.

Then to see what employees were trained you would create a crosstab
query with the employeeName as the row heading, the skillName as the
column heading, and Count(tblTraining.skillID) as the value.
The cells with 1 mean that the employee is trained, the cells with
blanks are not trained.

Cheers,
Jason Lepack

 

[Jeff Boyce]

If your underlying data has repeating fields ("Skill1", "Skill2", ...) you
have a spreadsheet, not a well-normalized relational database table. You
appear to be trying to use Access' features and functions, but feeding it
'sheet data. Both you and Access will have to work much harder unless you
normalize your data.

Another table design, one that will make it easier to use Access, would be
something like:

tblPerson
PersonID
LName
FName
DOB
... (other person-only info)

tlkpSkill
SkillID
SkillName
... (other skill-only info)

trelPersonSkill
PersonSkillID
PersonID (this is a foreign key, pointing back to which person)
SkillID (this is a foreign key, pointing back to which skill)
DateAchieved (or some such...)
... (other data specific to the unique combination of person and
skill)

Note that this design will have one PersonSkill record for each valid
combination of person and skill. Someone with 10 skills will have 10
records in this table. Someone with no skills will have no records.

If you keep your design, it will require a complete re-modeling of tables,
queries, forms, macros, reports, code ... if the number of skills is changed
(even just to 6 instead of 5). This could be a maintenance nightmare!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP