wrong value in query returned

[Rick Stahl]

I have a query consisting of 4 tables and 2 subqueries that returns a wrong
value in a given field. This is when no fields are linked where the value
is derived; if I try to link fields in the table then no records are
returned at all.

The field in question is tblNon-TaxDeductions.curMaxDomRel
Also included in this table is strFirstName, strLastName, dtmEffectiveDate,
dtmEndingDate, curOtherWeeklyDeduction

What I have is:
SELECT . . . . (a whole list of items not necessary to list here)
FROM tbl20078PayrollTaxRates, [tblNon-TaxDeductions],
((tblWeeklyHoursAttendance INNER JOIN qryJobTitlesRatesAttendance ON
(tblWeeklyHoursAttendance.strLastName =
qryJobTitlesRatesAttendance.strLastName) AND
(tblWeeklyHoursAttendance.strFirstName =
qryJobTitlesRatesAttendance.strFirstName) AND
(tblWeeklyHoursAttendance.dtmWorkEndingDate =
qryJobTitlesRatesAttendance.dtmWorkEndingDate)) INNER JOIN
qry20078EmployeeFedTaxAllowance ON (qryJobTitlesRatesAttendance.strLastName
= qry20078EmployeeFedTaxAllowance.strLastName) AND
(qryJobTitlesRatesAttendance.strFirstName =
qry20078EmployeeFedTaxAllowance.strFirstName)) INNER JOIN tblPersonalInfo ON
(tblWeeklyHoursAttendance.strLastName = tblPersonalInfo.strLastName) AND
(tblWeeklyHoursAttendance.strFirstName = tblPersonalInfo.strFirstName)
ORDER BY tblWeeklyHoursAttendance.dtmWorkEndingDate DESC;

Any help is greatly appreciated ! Thank you.

 

[Guest]

Try turning the INNER JOIN's to LEFT JOIN's or RIGHT JOIN's. It may take some
testing to find the right combination.

 

[Rick Stahl]

Will give it a try. Thank you so much. Have a Happy Thanksgiving !

Try turning the INNER JOIN's to LEFT JOIN's or RIGHT JOIN's. It may take
some
testing to find the right combination.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query consisting of 4 tables and 2 subqueries that returns a
wrong
value in a given field. This is when no fields are linked where the
value
is derived; if I try to link fields in the table then no records are
returned at all.

The field in question is tblNon-TaxDeductions.curMaxDomRel
Also included in this table is strFirstName, strLastName,
dtmEffectiveDate,
dtmEndingDate, curOtherWeeklyDeduction

What I have is:
SELECT . . . . (a whole list of items not necessary to list here)
FROM tbl20078PayrollTaxRates, [tblNon-TaxDeductions],
((tblWeeklyHoursAttendance INNER JOIN qryJobTitlesRatesAttendance ON
(tblWeeklyHoursAttendance.strLastName =
qryJobTitlesRatesAttendance.strLastName) AND
(tblWeeklyHoursAttendance.strFirstName =
qryJobTitlesRatesAttendance.strFirstName) AND
(tblWeeklyHoursAttendance.dtmWorkEndingDate =
qryJobTitlesRatesAttendance.dtmWorkEndingDate)) INNER JOIN
qry20078EmployeeFedTaxAllowance ON
(qryJobTitlesRatesAttendance.strLastName
= qry20078EmployeeFedTaxAllowance.strLastName) AND
(qryJobTitlesRatesAttendance.strFirstName =
qry20078EmployeeFedTaxAllowance.strFirstName)) INNER JOIN tblPersonalInfo
ON
(tblWeeklyHoursAttendance.strLastName = tblPersonalInfo.strLastName) AND
(tblWeeklyHoursAttendance.strFirstName = tblPersonalInfo.strFirstName)
ORDER BY tblWeeklyHoursAttendance.dtmWorkEndingDate DESC;

Any help is greatly appreciated ! Thank you.