Odd query behavior

[Antonio]

I have a query (qryMain) that pulls data from a table (tblRequest - 30
records) and two other queries based on two other tables. The sub queries
(qsub1- 2 records, qsub2 - 2 records) calculate fields based on records that
exist in the sub queries and the values in them. However the joins are set to
show all the records from tblRequest and only those from qsub1 and qsub2 that
have corresponding, unique, account numbers.

My problem - The data field from qsub2 is populating every fied in qryMain,
even though there are only two records while the data field from qsub1 is
populating the query field for only those two records.. SO

Instead of:
qryMain.Acct qryMain. Name qsub1.Status qsub2.status
V789098789 Joe Pending
V987987987 Sally Pending Complete
V324546544 Tim Complete
V584564544 Rick
V213215641 John

I get this: (Even though records for Joe, Rick, and John don't exist in qsub2)

qryMain.Acct qryMain. Name qsub1.Status qsub2.status
V789098789 Joe Pending Complete
V987987987 Sally Pending Complete
V324546544 Tim Complete
V584564544 Rick Complete
V213215641 John Complete

I dont know why its working correctly for qsub1 and not qsub2, even though
the joins are set the same between all queries.

 

[KARL DEWEY]

Post the SQL of your query qryMain.

 

[Antonio]

Thank you for the reply Karl. Here is the SQL...

SELECT tblTARRequest.Name, tblTARRequest.Acct,
qryCaseManagementStatus.CaseManagementStatus, qryCCSDayApprvlStatus.Status,
qryCCSStatus.CCSDetStatus, qryMiscStatus.MiscStatus
FROM (((tblTARRequest LEFT JOIN qryCaseManagementStatus ON
tblTARRequest.Acct = qryCaseManagementStatus.Acct) LEFT JOIN
qryCCSDayApprvlStatus ON tblTARRequest.Acct = qryCCSDayApprvlStatus.Acct)
LEFT JOIN qryCCSStatus ON tblTARRequest.Acct = qryCCSStatus.Acct) LEFT JOIN
qryMiscStatus ON tblTARRequest.Acct = qryMiscStatus.Acct;

Antonio

 

[Antonio]

Karl...I thought I should clarify a bit...
The two offending queries are qryCCSDayApprvlStatus and qryMiscStatus. It is
these two queries that, by themselves show only the two records in the
associated tables. The queries qryCaseManagementStatus and qryCCSStatus and
their assciated "status" fields are the ones that are showing correctly. (the
two records in each of the tables are displaying on the associated record in
qryTARRequest. All of the other records display blank data fields for these
two columns.

TIA

Antonio