jazminecat23
Programmer
Hi all - I have a strange situation. I have a db that someone else built with a whole slew of queries in it. The user needed to add a text box to all of the reports (dozens) to indicate what period they cover. So she made a single record table where she inputs the period information into one simple text field. This table only has the one record.
Then she added that field to all the queries that her reports were based on. This way she only has to change this one thing, and it will update all of the reports at once.
And for the most part, it worked. Except in the queries where she has a left join. Then the query fails. Below are two samples - one that works (the added field is in red):
and one that doesn't work (the error says there is a problem with the joins)
Can anyone here help me fix the second one so that it will not throw an error and will just add that one field to her report? Thanks!!
Then she added that field to all the queries that her reports were based on. This way she only has to change this one thing, and it will update all of the reports at once.
And for the most part, it worked. Except in the queries where she has a left join. Then the query fails. Below are two samples - one that works (the added field is in red):
Code:
SELECT Tbl_Main.Fund, Tbl_Main.[Cost Center], Tbl_Main.Employee_Name, Nz([DBA_4300],0)-Nz([P4300],0) AS D4300, Nz([DBA_4303],0)-Nz([P4303],0) AS D4303, Nz([DBA_4304],0)-Nz([P4304],0) AS D4304, Nz([DBA_4325],0)-Nz([P4325],0) AS D4325, Nz([DBA_4405],0)-Nz([P4405],0) AS D4405, Nz([DBA_4450],0)-Nz([P4450],0) AS D4450, Nz([DBA_4465],0)-Nz([P4465],0) AS D4465, [red][Benefit Dates].Date
FROM [Benefit Dates][/red], [Qry_Compare Prev Month] INNER JOIN Tbl_Main ON [Qry_Compare Prev Month].SSN = Tbl_Main.SSN
WHERE (((Nz([DBA_4300],0)-Nz([P4300],0))<>0)) OR (((Nz([DBA_4303],0)-Nz([P4303],0))<>0)) OR (((Nz([DBA_4304],0)-Nz([P4304],0))<>0)) OR (((Nz([DBA_4325],0)-Nz([P4325],0))<>0)) OR (((Nz([DBA_4405],0)-Nz([P4405],0))<>0)) OR (((Nz([DBA_4450],0)-Nz([P4450],0))<>0)) OR (((Nz([DBA_4465],0)-Nz([P4465],0))<>0));
and one that doesn't work (the error says there is a problem with the joins)
Code:
SELECT Tbl_Main.Fund, Tbl_Main.SSN, Tbl_Main.Employee_Name, Qry_4720C.Account_Number, Qry_4720C.DBA_Number, Qry_4720C.DBA_4720, Qry_4725C.Account_Number, Qry_4725C.DBA_Number, Qry_4725C.DBA_4725, Tbl_Main.[Employee No], [red][Benefit Dates].Date
FROM [Benefit Dates][/red], (Tbl_Main LEFT JOIN Qry_4725C ON Tbl_Main.SSN = Qry_4725C.SSN) LEFT JOIN Qry_4720C ON Tbl_Main.SSN = Qry_4720C.SSN
WHERE (((Qry_4720C.DBA_4720)<>0)) OR (((Qry_4725C.DBA_4725)<>0));
Can anyone here help me fix the second one so that it will not throw an error and will just add that one field to her report? Thanks!!