Two personnel members in 1 query, HELP!

[RobertSanders]

While making a dbase for a housekeeping department of a hotel I stumbled on the following problem.

I have a table called personnel, with every member of housekeeping in it. Now the dbase also has a table with roomchecks. In these roomchecks, there is always a field checker and cleaner, whom both come from the table personnel. This is because usually the maids are the cleaners, but not always...
Now every personnel member has a function category assigned, like: Maid, Supervisor or Management. But when I run a query to see the roomcheck that have only been cleaned people from the category Maids, it doesn't work. Probably because there are two fields from the same table.

Whatever I do, the query shows all roomchecks CHECKED by Maids, not Cleaned by Maids...

Any ideas??

 

[mp9]

Please post the SQL from your query. Thanx.

http://www22.brinkster.com/accessory/

 

[RobertSanders]

As is said before every personnel member has a functioncode [Function ID] assigned from a table with a field called Function group.
The SQL is as follows whereas [*FORMULA*] stands for the formula used to calculate the Grade field

SELECT Roomchecks.[Roomchecker ID], Roomchecks.[Cleaner ID], Functions.[Function group], Roomchecks.Date, *FORMULA* AS Grade
FROM (Functions INNER JOIN Personnel ON Functions.[Function ID] = Personnel.Function) INNER JOIN Roomchecks ON Personnel.[Personnel ID] = Roomchecks.[Cleaner ID];