Joined Queries causing inaccurate data

[Curtis A]

1. First I want to say thank you to those already whom helped me get thus far for Qry1 through Qry3 in my sample db from another site in creating the queries using Max function. https://www.utteraccess.com/topics/2.../posts/2796801

2. The issue in this current db sample, as attached below: in my Qry4 (qryMax04FitTestDetails) that I created, I have hoped to have joined the three queries appropriately, (the results of Qry3 + qryReqInds + qryReqFields); which I believe is not correct, my error!

3. Problem A: In tblDTHCEmployees, EmployeeID 17, has a FTID 209, where field [Remarks] has a notation. However, in Qry4, for EmployeeID 17, nothing in the [Remarks] field is displayed. This also applies to EmployeeID 32; having a FTID 98, where field [Remarks] has a notation; in Qry4, nothing in the [Remarks] field is displayed. This also applies to EmployeeID 46, 77, and maybe others.

4. Problem B: In tblDTHCEmployees, EmployeeID 24, has a FTID 250, where field [MedClearedYN] is actively checked, however, in Qry4, the record for EmployeeID 24, the field [MCY] is displaying a 0 and [MCN] is displaying a 1; it should be reversed. I’m sure my two fields [MCY] and [MCN] in Qry4 are proper.

5. Problem C: I have attached screen shots in rptScreenShots to illustrate issues referring to Qry4: Screen shot A: for record EmployeeID 17, field [MCN] has a valid 1 (since that ind was not cleared), however, when I use the filter as at screen shot B, that 1 is not a choice. For the same record EmployeeID 17, field [RptStatus] displays Not Medically Cleared, however, when I use the filter as at screen shot D, that choice is not available. Yet, when I choose “Unknown Status” from the list as at screen shot D, the two choices at screen shot E appear; I should get only “Unknown Status”. Problems A through C may be caused by the relationship issue in Qry4. Hopefully, the field [RptStatus] in Qry4 resolves itself when issues above are resolved.

Building Query sequence: How I got to this point!
Qry1 (qryMax01DateCleared) created to get the Max [DateCleared] data.
Qry2 (qryMax02FitTestDate) created to get the Max [DateFitTested] data.
Qry3 (qryMax03DateClearedFitTest) combines Qry1 and Qry2; I was hoping that when this query opens, only those [ReqYN] as “true” would display. However, when [ReqYN] is selected, the correct 126 individuals are displayed.
qryReqInds was initially created to pull the 126 inds since Qry3 [ReqYN] displays all db individuals; I want only those 126 [ReqYN] inds from tblDTHCEmployees. This query may not be needed if there is another way to capture those 126 inds (note: there are other demographic data in my real db).


qryReqFields was initially created to display the required fields I need for my reporting. This query may not be needed if there is another way of pulling these required fields from tblRespiratorFitTestData in relationship with the results of Qry3 [ReqYN] is true.

6. My simple goal: In my Qry4, (qryMax04FitTestDetails) which may need to be amended if needed (by those that have more SQL skills than I) I need to display for reporting both the [DateCleared] and [DateFitTested] data from the 126 [ReqYN] “true” individuals as listed in Qry3 and adding the required fields noted in qryReqFields with the inds listed in qryReqInds. If I don’t need to use either of the queries “qryReqInds” nor “qryReqFields” that’s fine; just as long I get the required fields and individuals data pulled and displayed in this Qry4 which will be the source of my Report.

Thank you! Curtis.

RESP FitTest CAL 03222022 DB.zip

[ArviLaanemets]

This query returns EmployeeID, DateCleared, and DateFitTested:

SELECT empl.EmployeeID, test1.DateCleared, test2.DateFitTestedFROM (tblDTHCEmployees empl 
LEFT JOIN (SELECT test01.EmployeeID, test01.DateCleared FROM tblRespiratorFitTestData test01 WHERE test01.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test01.EmployeeID AND DateCleared Is Not Null)) test1 ON test1.EmployeeID = empl.EmployeeID)
LEFT JOIN (SELECT test02.EmployeeID, test02.DateFitTested FROM tblRespiratorFitTestData test02 WHERE test02.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test02.EmployeeID AND DateFitTested Is Not Null)) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = TRUE;

To get additional fields, you have to decide, from which subquery you must get them, add appropriate fields into this subquery, and in main query read this field from subquery. An example:
As data for a person are read from 2 different entries of table tblRespiratorFitTestData (cleared and fit-tested entries), you either must have 2 Remarks fields, or select one of them. Let's assume you want the remark for cleared entry displayed. You have to edit the query like

SELECT empl.EmployeeID, test1.DateCleared, test2.DateFitTested, test01.Remarks FROM (tblDTHCEmployees empl 
LEFT JOIN (SELECT test01.EmployeeID, test01.DateCleared, test01.Remarks FROM tblRespiratorFitTestData test01 WHERE test01.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test01.EmployeeID AND DateCleared Is Not Null)) test1 ON test1.EmployeeID = empl.EmployeeID)
LEFT JOIN (SELECT test02.EmployeeID, test02.DateFitTested FROM tblRespiratorFitTestData test02 WHERE test02.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test02.EmployeeID AND DateFitTested Is Not Null)) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = TRUE;

There are some employees which don't have valid testing data in tblRespiratorFitTestData. For those employees all fields except EmployeeID (and any other fields read from tblDTHCEmployees) are empty.

[Curtis A]

Thank you for your professional response. Q: in your post you mentioned adding additional fields from my subquery and then using the “main query”. First, I used the 2^nd coding in your post and let’s say I name it Qry5; is this Qry5 (your subquery) replacing Qry1 through Qry3 to get the MAX date fields? Just trying to understand the query sequence. Now, if I want the required data fields from qryReqFields (my subquery) as with the results of your new query Qry5, how do I ensure the proper [FTID] for each [EmployeeID] is being pulled; for in qryReqFields, some [EmployeeID]s have two [FTID]’s. And, in what query do I create my Calculations has I have done in qryMax04 which will be the final query as the source for the Report? For me, a coding as an example does well as I try to visualize the proper sequence. Hopefully, I have not misunderstood your points. Thank you!

[ArviLaanemets]

Thank you for your professional response. Q: in your post you mentioned adding additional fields from my subquery and then using the “main query”.

Not your subquery but my subquery! You may generalize my main query as

SELECT expr1, expr2, ... 
FROM (tblDTHCEmployees empl 
LEFT JOIN (Subquery1) test1 ON test1.EmployeeID = empl.EmployeeID) 
LEFT JOIN (Subquery2) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = True

I.e. the main query reads a list of valid employees from tblDTHCEmployees, and connects this list with 2 subqueries, which respectively return latest test clearance return info and latest test validation info from tblRespiratorFitTestData.

First, I used the 2^nd coding in your post and let’s say I name it Qry5; is this Qry5 (your subquery) replacing Qry1 through Qry3 to get the MAX date fields? Just trying to understand the query sequence.

Yes, the subqueries in brackets after LEFT JOIN get records with latest FTID's and any additional info from those records depending how you design those subqueries (which fields you include). And what you refer as 2^nd coding is not another query - it is same query with one additional field added. As I didn't have an idea about real data in your table, I presented simply an example how fields must be added - it is up to you to do it!

Now, if I want the required data fields from qryReqFields (my subquery) as with the results of your new query Qry5, how do I ensure the proper [FTID] for each [EmployeeID] is being pulled; for in qryReqFields, some [EmployeeID]s have two [FTID]’s.

You ensure it by placing FTID field into proper subquery, and reading it from there in main query. Any field read from tblRespiratorFitTestData can have 2 different values, so either you ensure by design that right one is returned, or you must have both of them returned and have to rename those returned fields (you can't have 2 fields with same name returned), e.g. TestFTID and FitFTID. But really there is no need to display FTID at all. PK is meant for use by DB only, and there is no need for users to see it at all. There is a lot of more useful info to be displayed instead!

And, in what query do I create my Calculations has I have done in qryMax04 which will be the final query as the source for the Report? For me, a coding as an example does well as I try to visualize the proper sequence. Hopefully, I have not misunderstood your points. Thank you!

??? After you edit the query so it returns all essential info, you can create your report based on this same query - it will be quite reportable :-)

[Curtis A]

Good evening. Sorry for the delay! I got stuck!

1. I used your qryALMax02 as the base for the Max dates.
2. I created qryCALMax03, using the results of qryALMax02 and the qryReqFIelds; joining [EmployeeID]s and [DateFitTested]; I believe this may not correct.
3. As I open qryCALMax03, the correct 126 ind’s appear.
   
   1. Error A: I see under my calculated field [MCN], no-one falls under this, however, in “tblRespiratorFitTestData”; Records 17, 32 and 46 have been screened, they have a [DateScreened] completed, with a false [MedClearedYN]; these three records should be “Not Medically Cleared” in my field [RptStatus]. Q: is my coding inappropriate for [MCN]?
   2. Error B: I believe field [MedClearedYN] does not show the proper results: To verify this in my qryCALMax03, I filter a “1” under field [EmpScrnCt] to select all the ind’s that were screened, true results = 101 records; then under field [MCY] I select a “0” for the false records which = 20 records (3 ref to 3a above), and notice that [EmployeeID]’s 24, 77, 275, 280, 339 and etc, have no entries under field [MedClearedYN]; however in the respective “tblRespiratorFitTestData”; there are valid check marks in this field for these records. Where is my error?
   
   
4. Am I missing a subquery step someplace before creating my “qryCALMax03”? Thank you much! Hopefully the current db got attached!

[ArviLaanemets]

I haven't time to process whole querying process, but at lest with your "Error A" it started in qryALMax02. The query must be

SELECT empl.EmployeeID, empl.LastName, empl.FirstName, empl.Department, empl.Clinic, test1.DateScreened, test2.DateFitTested, test1.Remarks
FROM 
(
tblDTHCEmployees empl 
LEFT JOIN 
     (
     SELECT test01.EmployeeID, test01.DateScreened, test01.Remarks 
     FROM tblRespiratorFitTestData test01 
     WHERE test01.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test01.EmployeeID AND DateScreened Is Not Null)
     ) test1 ON test1.EmployeeID = empl.EmployeeID
)
LEFT JOIN 
     (
     SELECT test02.EmployeeID, test02.DateFitTested 
     FROM tblRespiratorFitTestData test02 
     WHERE test02.FTID IN (SELECT MAX(FTID) FROM  tblRespiratorFitTestData WHERE EmployeeID = test02.EmployeeID AND DateFitTested Is Not Null)
     ) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = TRUE;

You must get remark from subquery, not from source of subquery! Test01 there isn't joined to empl at all - so you probably get for employees 1st Remark returned instead of one from joined one!

[Curtis A]

Thank you for your help in this. I did try your updated code for "qryALMax02" and do come up with the same Error A and B when i run "qryCALMax03"; except there are 4 [EmployeeID]'s 17, 32, 46 and 356 for Error A that should have a "1" under field [MYN]; "Not Medically Cleared". And there are 13 records, 24, 77, 275, 280, etc that should have a "1" under [MCY] if my calculation is correctly stated; which should result in being "Cleared, No Fit Test on Record", if my [RptStatus] field calculation is proper. Thank you for your patience.