Re: DLookup Problem

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

Col wrote:
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.

.

Relevant Pages

  • How to approach complex query
    ... I'm having a real problem getting a query and no ... Employees with employeeID (primary key), employeename, startdate being the ... completedate being the key fields. ...
    (microsoft.public.access.queries)
  • Re: How to select only most recent records?
    ... It sounds like you are using the worker's name as a primary key. ... a start date/time and a stop ... you can use a query to find the Max ... value for each EmployeeID (a Totals query, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Autonumber Fields
    ... allowing the apparent duplication in at least some cases. ... I am inclined to use autonumber or some other fixed value (e.g. Invoice ... >> format of EmployeeID, which is just a sequential 4-digit number. ... > else of a field that is currently being used as a primary key. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: help
    ... How do I bring that first query in the second one, just put it in the criteria or in the field name in design view?? ... > TableA and only those records in TableB that equal the join field in TableA. ... About the primary key, I don't have the ... > employeeID as the primary key on the Training table since I realized that ...
    (microsoft.public.access.queries)
  • Re: Autonumber Fields
    ... > format of EmployeeID, which is just a sequential 4-digit number. ... Which is why most people use completely meaningless Autonumber fields as ... of a field that is currently being used as a primary key. ... More than likely you'll eventually have to move to an Autonumber primary key ...
    (microsoft.public.access.tablesdbdesign)