Joining querys help

[oxicottin]

Hello, I have this database and I’m having trouble figuring out this query that works with the unload event of my [frm_ShiftDay]. Here is how it’s supposed to work…. I open the [frm_ShiftDay] which I select a shift and date and technician and in its subform [frm_ShiftMachinesRanSubform] I enter an employee and a machine then on its subform [frm_MachineOutputSubForm] which is a continuous form I start entering product data which is usually only a few records then I select another machine and enter whatever product data was ran on it.

Now, I close the form and in the form On Unload Event it loops through [qry_FindNullRecords] and detects there is null or missing data and gives a choice to close or stay. This works fine….
The part I’m having the trouble with is the query, the two querys that are joined to the [qry_FindNullRecords] are giving me the correct information but I can’t get them to show in the [qry_FindNullRecords] UNLESS the same employees name is used in all the records like the attached example.
If you open my example [frm_ShiftDay] you will see two sections highlighted in red. Notice machine 1 of 2 and Employee “Hu, Bruce” now if you move to machine 2 of 2 the employee is “Hu, Bruce” now close the form and a message will come up saying missing data for “Hu, Bruce”. Now if you open the form back up and change one of the employees the message won’t appear and it should.

Now if you look at the [qry_FindNullRecordsAllOther] and the [qry_FindNullRecordsSeven] that are joined to the [qry_FindNullRecords] there are null records and if you put the employees name back to “Hu, Bruce” on both machines and look at [qry_FindNullRecords] it shows records. So my question is where in this query am I going wrong?

SQL [qry_FindNullRecords]

SELECT tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, [EmpFName] & " " & [EmpLName] AS EmployeesName
FROM tbluMachines INNER JOIN (((tbluEmployees INNER JOIN qry_FindNullRecordsAllOther ON tbluEmployees.EmployeeID = qry_FindNullRecordsAllOther.EmployeeID) INNER JOIN qry_FindNullRecordsSeven ON tbluEmployees.EmployeeID = qry_FindNullRecordsSeven.EmployeeID) INNER JOIN ((tbl_ShiftDay INNER JOIN tbl_ShiftMachinesRan ON tbl_ShiftDay.ShiftDayID = tbl_ShiftMachinesRan.ShiftDayID) INNER JOIN tbl_MachineOutput ON tbl_ShiftMachinesRan.ShiftMachineID = tbl_MachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tbl_ShiftMachinesRan.EmployeeID) ON tbluMachines.MachineID = tbl_ShiftMachinesRan.MachineID
WHERE (((tbl_ShiftDay.ShiftDate)>Date()-2))
GROUP BY tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, [EmpFName] & " " & [EmpLName];

SQL [qry_FindNullRecordsAllOther]

SELECT tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, tbl_MachineOutput.ProductID, tbl_MachineOutput.Length, tbl_MachineOutput.IndividualUPC, tbl_MachineOutput.SampleNum, tbl_MachineOutput.SLegCS, tbl_MachineOutput.SLegNCS, tbl_MachineOutput.PLegCS, tbl_MachineOutput.PLegNCS, tbl_MachineOutput.BeadNCS, tbl_MachineOutput.BeadCS, tbl_MachineOutput.Angle, [EmpFName] & " " & [EmpLName] AS EmployeesName, tbl_MachineOutput.SampleComments
FROM tbluMachines INNER JOIN (tbluEmployees INNER JOIN ((tbl_ShiftDay INNER JOIN tbl_ShiftMachinesRan ON tbl_ShiftDay.ShiftDayID = tbl_ShiftMachinesRan.ShiftDayID) INNER JOIN tbl_MachineOutput ON tbl_ShiftMachinesRan.ShiftMachineID = tbl_MachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tbl_ShiftMachinesRan.EmployeeID) ON tbluMachines.MachineID = tbl_ShiftMachinesRan.MachineID
WHERE (((tbl_MachineOutput.ProductID) Not In (21,10)) AND ((tbl_ShiftDay.ShiftDate)>Date()-2))
GROUP BY tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, tbl_MachineOutput.ProductID, tbl_MachineOutput.Length, tbl_MachineOutput.IndividualUPC, tbl_MachineOutput.SampleNum, tbl_MachineOutput.SLegCS, tbl_MachineOutput.SLegNCS, tbl_MachineOutput.PLegCS, tbl_MachineOutput.PLegNCS, tbl_MachineOutput.BeadNCS, tbl_MachineOutput.BeadCS, tbl_MachineOutput.Angle, [EmpFName] & " " & [EmpLName], tbl_MachineOutput.SampleComments
HAVING (((tbl_ShiftMachinesRan.EmployeeID) Is Null)) OR (((tbluMachines.MachineNr) Is Null)) OR (((tbl_MachineOutput.ProductID) Is Null)) OR (((tbl_MachineOutput.Length) Is Null)) OR (((tbl_MachineOutput.IndividualUPC) Is Null)) OR (((tbl_MachineOutput.SampleNum) Is Null)) OR (((tbl_MachineOutput.SLegCS) Is Null)) OR (((tbl_MachineOutput.SLegNCS) Is Null)) OR (((tbl_MachineOutput.PLegCS) Is Null)) OR (((tbl_MachineOutput.PLegNCS) Is Null)) OR (((tbl_MachineOutput.BeadNCS) Is Null)) OR (((tbl_MachineOutput.BeadCS) Is Null)) OR (((tbl_MachineOutput.Angle) Is Null)) OR ((([EmpFName] & " " & [EmpLName]) Is Null)) OR (((tbl_MachineOutput.SampleComments) Is Null));

SQL [qry_FindNullRecordsSeven]

SELECT tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, tbl_MachineOutput.ProductID, tbl_MachineOutput.Length, tbl_MachineOutput.IndividualUPC, tbl_MachineOutput.SampleNum, tbl_MachineOutput.SampleTime, tbl_MachineOutput.SLegCS, tbl_MachineOutput.SLegNCS, tbl_MachineOutput.PLegCS, tbl_MachineOutput.PLegNCS, tbl_MachineOutput.Gap, tbl_MachineOutput.SampleComments, [EmpFName] & " " & [EmpLName] AS EmployeesName
FROM tbluMachines INNER JOIN (tbluEmployees INNER JOIN ((tbl_ShiftDay INNER JOIN tbl_ShiftMachinesRan ON tbl_ShiftDay.ShiftDayID = tbl_ShiftMachinesRan.ShiftDayID) INNER JOIN tbl_MachineOutput ON tbl_ShiftMachinesRan.ShiftMachineID = tbl_MachineOutput.ShiftMachineID) ON tbluEmployees.EmployeeID = tbl_ShiftMachinesRan.EmployeeID) ON tbluMachines.MachineID = tbl_ShiftMachinesRan.MachineID
WHERE (((tbl_ShiftDay.ShiftDate)>Date()-2) AND ((tbl_MachineOutput.ProductID)=10 Or (tbl_MachineOutput.ProductID)=21))
GROUP BY tbl_ShiftMachinesRan.EmployeeID, tbluMachines.MachineNr, tbl_MachineOutput.ProductID, tbl_MachineOutput.Length, tbl_MachineOutput.IndividualUPC, tbl_MachineOutput.SampleNum, tbl_MachineOutput.SampleTime, tbl_MachineOutput.SLegCS, tbl_MachineOutput.SLegNCS, tbl_MachineOutput.PLegCS, tbl_MachineOutput.PLegNCS, tbl_MachineOutput.Gap, tbl_MachineOutput.SampleComments, [EmpFName] & " " & [EmpLName]
HAVING (((tbl_ShiftMachinesRan.EmployeeID) Is Null)) OR (((tbluMachines.MachineNr) Is Null)) OR (((tbl_MachineOutput.ProductID) Is Null)) OR (((tbl_MachineOutput.Length) Is Null)) OR (((tbl_MachineOutput.IndividualUPC) Is Null)) OR (((tbl_MachineOutput.SampleNum) Is Null)) OR (((tbl_MachineOutput.SampleTime) Is Null)) OR (((tbl_MachineOutput.SLegCS) Is Null)) OR (((tbl_MachineOutput.SLegNCS) Is Null)) OR (((tbl_MachineOutput.PLegCS) Is Null)) OR (((tbl_MachineOutput.PLegNCS) Is Null)) OR (((tbl_MachineOutput.Gap) Is Null)) OR (((tbl_MachineOutput.SampleComments) Is Null)) OR ((([EmpFName] & " " & [EmpLName]) Is Null));

HERE is a link to my ONEDRIVE database example showing the above trouble im having.


Thanks,
SoggyCashew.....

 

[oxicottin]

Oh I forgot to mention it only looks at the current record date so so if you open the dB then you have to change the "Shifts Date" to the current date or just remove the criteria in the querys...

Thanks,
SoggyCashew.....