How to query when multiple fields source value from a single field in another table (1 Viewer)

[brharrii]

I've built a task list that allows all tasks to be assigned and then tracks completion.

main table looks like this:

tblDiscrepancy
- DiscrepancyID
- DiscrepancyDescription
- DiscrepancyCorrectiveAction
- DiscrepancyAssignedTo
- DiscrepancyCompleteDate
- DiscrepancyVerifiedBy


Both DiscrepancyAssingedTo and DiscrepancyVerifiedBy source tblAuditStaff for a list of names.

tblAuditStaff
- AuditStaffID (PK AutoNumber)
- AuditStaffName

I am constructing a report that will list out all of the open tasks and who they belong to and who has verified them. Currently the report displays only the AuditStaffID field since that is the field that ends up being stored in the main table for "Assigned to" and "verified by" values.

How can I change my query to also include the names from tblAuditStaff and get them to diferentiate between the two fields "DiscrepancyVerifiedBy" and "DiscrepancyAssignedTo"?

Thanks

Code as it is now:

SELECT tblDiscrepancy.DiscrepancyAssignedTo, tblArea.AreaDesc, tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyCorrectiveAction, tblDiscrepancy.DiscrepancyEstimatedCompDate, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyVerifiedBy, tblDiscrepancy.DiscrepancyVerifiedDate, tblDiscrepancy.DiscrepancyRootCause, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
FROM (tblArea RIGHT JOIN tblObservedHeader ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff RIGHT JOIN tblDiscrepancy ON (tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyVerifiedBy) AND (tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo)) ON tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest
WHERE (((tblDiscrepancy.DiscrepancyEstimatedCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedBy) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False))
ORDER BY tblDiscrepancy.DiscrepancyAssignedTo, tblArea.AreaDesc;

 

[brharrii]

I think I may have found an answer... it seems to do what I want it to, but I wasn't sure on the join of the new table (tblAuditStaff_1) if that was the best way to do it. Would someone give it a look over and let me know if this seems like the appropriate way to do this?

thanks

SELECT tblDiscrepancy.DiscrepancyAssignedTo, tblAuditStaff.AuditStaffName, tblArea.AreaDesc, tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyCorrectiveAction, tblDiscrepancy.DiscrepancyEstimatedCompDate, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyVerifiedBy, tblAuditStaff_1.AuditStaffName, tblDiscrepancy.DiscrepancyVerifiedDate, tblDiscrepancy.DiscrepancyRootCause, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
[B]FROM ((tblArea RIGHT JOIN tblObservedHeader ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff RIGHT JOIN tblDiscrepancy ON tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo) ON tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest) LEFT JOIN tblAuditStaff AS tblAuditStaff_1 ON tblDiscrepancy.DiscrepancyVerifiedBy = tblAuditStaff_1.AuditStaffID
[/B]WHERE (((tblDiscrepancy.DiscrepancyEstimatedCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedBy) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False))
ORDER BY tblDiscrepancy.DiscrepancyAssignedTo, tblArea.AreaDesc;