Subdatasheet Query

G

Guest

I have a table of job requirements that I assign to employees through a
look-up table. The requirement numbers are unique and so I have it set so
that neither the requirements nor the employee table allows duplicates. What
I want to do is make a query of the requirements table as linked to the
employee table (there is a subdatasheet that shows whether the req number has
been assigned) so that the query result shows only the unfilled requirements.
I will then be able to do my look-up on the query and then I will only see
the unfilled reqs in my dropdown. It seems like it should be a simple
solution, but I just can't seem to figure it out. Thanks.
 
M

MGFoster

aleks2539 said:
I have a table of job requirements that I assign to employees through a
look-up table. The requirement numbers are unique and so I have it set so
that neither the requirements nor the employee table allows duplicates. What
I want to do is make a query of the requirements table as linked to the
employee table (there is a subdatasheet that shows whether the req number has
been assigned) so that the query result shows only the unfilled requirements.
I will then be able to do my look-up on the query and then I will only see
the unfilled reqs in my dropdown. It seems like it should be a simple
solution, but I just can't seem to figure it out. Thanks.

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

You don't show the design of your tables, so this is only a guess.

The following query is based on the design of 3 tables

Employees -> EmployeeRequirements <- Requirements
employee_id -> employee_id & requirement_id <- requirement_id

SELECT employee_id, <other columns>
FROM Employees
WHERE employee_id NOT IN (SELECT employee_id FROM EmployeeRequirements
WHERE requirement = XXX)

This query is based on the design of 2 tables:

Employees <- Requirements
requirement_id <- requirement_id

SELECT <column list>
FROM Employees
WHERE requirement_id IS NULL
OR requirement_id <> XXX

The XXX is the unfilled requirement ID number or word.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRE+xF4echKqOuFEgEQJ6IwCg9L1Qb4fbs0BTX1gQnBoWV3xGbyMAn3ri
Gz/Ko2Nrtvr41WOTjha/iT8P
=OxI/
-----END PGP SIGNATURE-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top