How to construct query to match field content in one table to field name in another

[ekierce]

Hi Everybody, I'm hoping that someone can help a fairly new Access user out with a query.

This is to track employee training on documents.
In one table, the person who configured the original database structured it so it has fields for the document name and information, then a separate field for each job title. In those fields, it's a simple y/n checkbox.
In another table, which list employees, there is a field for job title where the content of that field is the same as the name of the field on the other table.
What I need to is generate a report that will allow me to pick a name from the "Employee Table" and list all documents from the "Documents Table" that have a check for the field that is their job title.
As an example, in the Documents Table, you might see the following:

Title	Rev.	QA Analyst	RA Auditor	Production Technician
Doc. Prep	3	x	x
Doc Submission	1	x		x
Filing SOP	2	x	x	x

Then in the Employee Table, you might see:

Name	Title
Jane Doe	QA Analyst
John Smith	Production Technician
Mike Smith	RA Auditor

I would like to create a report that would (as an example) generate a list like:

Training Requirements for Jane Doe

Title	Rev
Doc. Prep	3
Doc Submission	1
Filing SOP	2

But for Mike Smith, the requirements would be:

Training Requirements for Mike SMith

Title	Rev
Doc Submission	1
Filing SOP	2

If anyone can help me figure this out, I'd be very grateful.

[June7]

A minor inconsistency in your example: Mike Smith would have Doc Prep and Filing SOP.

Multiple fields for similar data is an indication of non-normalized data structure. This will complicate querying.

A normalized structure for Documents would be like:

ID	Rev	Task	Role
1	3	Doc Prep	QA Analyst
2	3	Doc Prep	RA Auditor
3	1	Doc Submission	QA Analyst
4	1	Doc Submission	Prod Tech
5	2	Filing SOP	QA Analyst
6	2	Filing SOP	RA Auditor
7	2	Filing SOP	Prod Tech

This will allow table to join with Employees on Role and Title fields.

Dealing with your data as is can be done but is tricky. Are there only the 3 roles to be concerned with? A UNION query can normalize the data. There is no wizard for UNION, must be typed (or copy/paste) in SQL View of query designer:

SELECT Rev, Title, "QA Analyst" AS Role FROM Documents WHERE [QA Analyst] = True
UNION SELECT Rev, Title, "RA Auditor" FROM Documents WHERE [RA Auditor] = True
UNION SELECT Rev, Title, "Prod Tech" FROM Documents WHERE [Production Technician] = True;

Now use that query in a query that joins with Employees table. Build report.


Advise not to use spaces or special characters/punctuation (underscore is exception) in names. If used, enclose in [], like: [Rev.]

[ekierce]

Unfortunately, there are more like 87 roles that I have to contend with.

The person who designed this database to begin with was just winging it unfortunately, so I'm just having to deal with the fallout.

Thanks for the tip, I'll give it a try in the morning and see if I can make it work.

[June7]

So there are 87 fields? UNION query has limit of 50 lines.

This data structure is not optimized and what you want is nearly impossible. The only recourse I can see is VBA code and a temp table.

[ekierce]

Okay, I'll have to give this some thought.

I may just tell them they'll have to do a report by job title and skip having the employee name on the report, that way I don't have to worry about the super complex query and can just set up a menu for them that will run the report for the needed job title.

[June7]

I've never tried to UNION two UNION queries that would together have more than 50 lines but that might be possible.