Query Not Returning Correct Amount of Records

  • Thread starter Thread starter blanch2010
  • Start date Start date
B

blanch2010

I have 6 tables that I have built 6 different queries on. Individually,
these queries return the correct amount of records in the tables. Which is
80 records in all tables involved.
Then I built a query that collects data from the 6 queries and this query
returns 75 records. What am I doing wrong?

Thanks
Don
 
As a guess, your query is not correctly structured for the data you have.

For instance, if one of the 6 tables does not have a matching record that
could cause you to "lose" a record or two in the query if you have set up a
join to that table and have not selected the proper type of join.

Can you copy and paste the SQL of your query? (View: SQL from the menu).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I have 6 tables that I have built 6 different queries on. Individually,
these queries return the correct amount of records in the tables. Which is
80 records in all tables involved.
Then I built a query that collects data from the 6 queries and this query
returns 75 records. What am I doing wrong?

Thanks
Don
Click to expand...


If you'ld like help with what you're doing wrong, please tell us what you're
doing. Post the SQL of the "query that collects", and if necessary the SQL of
the six individual queries. I'm guessing there's a problem with the joins
but... I can't see your screen!
 
John Spencer said:
As a guess, your query is not correctly structured for the data you have.

For instance, if one of the 6 tables does not have a matching record that
could cause you to "lose" a record or two in the query if you have set up a
join to that table and have not selected the proper type of join.

Can you copy and paste the SQL of your query? (View: SQL from the menu).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.
SELECT qryClientAcct.*, qryProcessor1TDAcct.*, qryProcessor1TD2Acct.*, qryAgent1Acct.*, qryAgent2Acct.*, qryAgent3Acct.*, qrySourceAcct.*
Click to expand...
FROM qrySourceAcct INNER JOIN (qryAgent3Acct INNER JOIN (qryAgent2Acct INNER
JOIN (qryAgent1Acct INNER JOIN (qryProcessor1TD2Acct INNER JOIN
(qryProcessor1TDAcct INNER JOIN qryClientAcct ON (qryClientAcct.ClientID =
qryProcessor1TDAcct.ClientID) AND (qryProcessor1TDAcct.ClientID =
qryClientAcct.ClientID)) ON (qryProcessor1TDAcct.ClientID =
qryProcessor1TD2Acct.ClientID) AND (qryProcessor1TD2Acct.ClientID =
qryProcessor1TDAcct.ClientID)) ON (qryProcessor1TD2Acct.ClientID =
qryAgent1Acct.ClientID) AND (qryAgent1Acct.ClientID =
qryProcessor1TD2Acct.ClientID)) ON (qryAgent1Acct.ClientID =
qryAgent2Acct.ClientID) AND (qryAgent2Acct.ClientID =
qryAgent1Acct.ClientID)) ON (qryAgent2Acct.ClientID = qryAgent3Acct.ClientID)
AND (qryAgent3Acct.ClientID = qryAgent2Acct.ClientID)) ON
(qryAgent3Acct.ClientID = qrySourceAcct.ClientID) AND (qrySourceAcct.ClientID
= qryAgent3Acct.ClientID);
 
John W. Vinson said:
If you'ld like help with what you're doing wrong, please tell us what you're
doing. Post the SQL of the "query that collects", and if necessary the SQL of
the six individual queries. I'm guessing there's a problem with the joins
but... I can't see your screen!
--

John W. Vinson [MVP]
.
The query that collects:
Click to expand...
SELECT qryClientAcct.*, qryProcessor1TDAcct.*, qryProcessor1TD2Acct.*,
qryAgent1Acct.*, qryAgent2Acct.*, qryAgent3Acct.*, qrySourceAcct.*
FROM qrySourceAcct INNER JOIN (qryAgent3Acct INNER JOIN (qryAgent2Acct INNER
JOIN (qryAgent1Acct INNER JOIN (qryProcessor1TD2Acct INNER JOIN
(qryProcessor1TDAcct INNER JOIN qryClientAcct ON (qryClientAcct.ClientID =
qryProcessor1TDAcct.ClientID) AND (qryProcessor1TDAcct.ClientID =
qryClientAcct.ClientID)) ON (qryProcessor1TDAcct.ClientID =
qryProcessor1TD2Acct.ClientID) AND (qryProcessor1TD2Acct.ClientID =
qryProcessor1TDAcct.ClientID)) ON (qryProcessor1TD2Acct.ClientID =
qryAgent1Acct.ClientID) AND (qryAgent1Acct.ClientID =
qryProcessor1TD2Acct.ClientID)) ON (qryAgent1Acct.ClientID =
qryAgent2Acct.ClientID) AND (qryAgent2Acct.ClientID =
qryAgent1Acct.ClientID)) ON (qryAgent2Acct.ClientID = qryAgent3Acct.ClientID)
AND (qryAgent3Acct.ClientID = qryAgent2Acct.ClientID)) ON
(qryAgent3Acct.ClientID = qrySourceAcct.ClientID) AND (qrySourceAcct.ClientID
= qryAgent3Acct.ClientID);

The 6 queries:

SELECT tbl1TD.ClientID, tbl1TD.[1TDLoanModCost1], tbl1TD.[1TDPmt1Amt],
tbl1TD.[1TDPmt2Amt], tbl1TD.[1TDPmt3Amt], tbl1TD.[1TDPmt4Amt]
FROM tbl1TD;

SELECT tblAgent.ClientID, tblAgent.Agent1LastName, tblAgent.Agent1FirstName,
tblAgent.Agent1ComAmt, tblAgent.Agent1Pmt1Amt, tblAgent.Agent1Pmt2Amt,
tblAgent.Agent1Pmt3Amt, tblAgent.Agent1Pmt4Amt
FROM tblAgent;

SELECT tblClient.ClientID, tblClient.Client1LastName,
tblClient.Client1FirstName, tblClient.PropertyAddress, tblClient.PropertyCity
FROM tblClient;

SELECT tblAgent.ClientID, tblAgent.Agent2LastName, tblAgent.Agent2FirstName,
tblAgent.Agent2ComAmt, tblAgent.Agent2Pmt1Amt, tblAgent.Agent2Pmt2Amt,
tblAgent.Agent2Pmt3Amt, tblAgent.Agent2Pmt4Amt
FROM tblAgent;

SELECT tblAgent.ClientID, tblAgent.Agent3LastName, tblAgent.Agent3FirstName,
tblAgent.Agent3ComAmt, tblAgent.Agent3Pmt1Amt, tblAgent.Agent3Pmt2Amt,
tblAgent.Agent3Pmt3Amt, tblAgent.Agent3Pmt4Amt
FROM tblAgent;

SELECT tblProcessor1TD.ClientID, tblProcessor1TD.[1TDProcessor1LastName],
tblProcessor1TD.[1TDProcessor1FirstName],
tblProcessor1TD.[1TDProcessor1ComAmt],
tblProcessor1TD.[1TDProcessor1Pmt1Amt],
tblProcessor1TD.[1TDProcessor1Pmt2Amt],
tblProcessor1TD.[1TDProcessor1Pmt3Amt], tblProcessor1TD.[1TDProcessor1Pmt4Amt]
FROM tblProcessor1TD;


SELECT tblProcessor1TD.ClientID, tblProcessor1TD.[1TDProcessor2LastName],
tblProcessor1TD.[1TDProcessor2FirstName],
tblProcessor1TD.[1TDProcessor2ComAmt],
tblProcessor1TD.[1TDProcessor2Pmt1Amt],
tblProcessor1TD.[1TDProcessor2Pmt2Amt],
tblProcessor1TD.[1TDProcessor2Pmt3Amt], tblProcessor1TD.[1TDProcessor2Pmt4Amt]
FROM tblProcessor1TD;

SELECT tblSource.ClientID, tblSource.SourceLastName,
tblSource.SourceFirstName, tblSource.SourceComAmt, tblSource.SourcePmt1Amt,
tblSource.SourcePmt2Amt, tblSource.SourcePmt3Amt, tblSource.SourcePmt4Amt
FROM tblSource;

Thanks
 
blanch2010 said:
John W. Vinson said:
If you'ld like help with what you're doing wrong, please tell us what you're
doing. Post the SQL of the "query that collects", and if necessary the SQL of
the six individual queries. I'm guessing there's a problem with the joins
but... I can't see your screen!
--

John W. Vinson [MVP]
.
The query that collects:
Click to expand...
SELECT qryClientAcct.*, qryProcessor1TDAcct.*, qryProcessor1TD2Acct.*,
qryAgent1Acct.*, qryAgent2Acct.*, qryAgent3Acct.*, qrySourceAcct.*
FROM qrySourceAcct INNER JOIN (qryAgent3Acct INNER JOIN (qryAgent2Acct INNER
JOIN (qryAgent1Acct INNER JOIN (qryProcessor1TD2Acct INNER JOIN
(qryProcessor1TDAcct INNER JOIN qryClientAcct ON (qryClientAcct.ClientID =
qryProcessor1TDAcct.ClientID) AND (qryProcessor1TDAcct.ClientID =
qryClientAcct.ClientID)) ON (qryProcessor1TDAcct.ClientID =
qryProcessor1TD2Acct.ClientID) AND (qryProcessor1TD2Acct.ClientID =
qryProcessor1TDAcct.ClientID)) ON (qryProcessor1TD2Acct.ClientID =
qryAgent1Acct.ClientID) AND (qryAgent1Acct.ClientID =
qryProcessor1TD2Acct.ClientID)) ON (qryAgent1Acct.ClientID =
qryAgent2Acct.ClientID) AND (qryAgent2Acct.ClientID =
qryAgent1Acct.ClientID)) ON (qryAgent2Acct.ClientID = qryAgent3Acct.ClientID)
AND (qryAgent3Acct.ClientID = qryAgent2Acct.ClientID)) ON
(qryAgent3Acct.ClientID = qrySourceAcct.ClientID) AND (qrySourceAcct.ClientID
= qryAgent3Acct.ClientID);

The 6 queries:

SELECT tbl1TD.ClientID, tbl1TD.[1TDLoanModCost1], tbl1TD.[1TDPmt1Amt],
tbl1TD.[1TDPmt2Amt], tbl1TD.[1TDPmt3Amt], tbl1TD.[1TDPmt4Amt]
FROM tbl1TD;

SELECT tblAgent.ClientID, tblAgent.Agent1LastName, tblAgent.Agent1FirstName,
tblAgent.Agent1ComAmt, tblAgent.Agent1Pmt1Amt, tblAgent.Agent1Pmt2Amt,
tblAgent.Agent1Pmt3Amt, tblAgent.Agent1Pmt4Amt
FROM tblAgent;

SELECT tblClient.ClientID, tblClient.Client1LastName,
tblClient.Client1FirstName, tblClient.PropertyAddress, tblClient.PropertyCity
FROM tblClient;

SELECT tblAgent.ClientID, tblAgent.Agent2LastName, tblAgent.Agent2FirstName,
tblAgent.Agent2ComAmt, tblAgent.Agent2Pmt1Amt, tblAgent.Agent2Pmt2Amt,
tblAgent.Agent2Pmt3Amt, tblAgent.Agent2Pmt4Amt
FROM tblAgent;

SELECT tblAgent.ClientID, tblAgent.Agent3LastName, tblAgent.Agent3FirstName,
tblAgent.Agent3ComAmt, tblAgent.Agent3Pmt1Amt, tblAgent.Agent3Pmt2Amt,
tblAgent.Agent3Pmt3Amt, tblAgent.Agent3Pmt4Amt
FROM tblAgent;

SELECT tblProcessor1TD.ClientID, tblProcessor1TD.[1TDProcessor1LastName],
tblProcessor1TD.[1TDProcessor1FirstName],
tblProcessor1TD.[1TDProcessor1ComAmt],
tblProcessor1TD.[1TDProcessor1Pmt1Amt],
tblProcessor1TD.[1TDProcessor1Pmt2Amt],
tblProcessor1TD.[1TDProcessor1Pmt3Amt], tblProcessor1TD.[1TDProcessor1Pmt4Amt]
FROM tblProcessor1TD;


SELECT tblProcessor1TD.ClientID, tblProcessor1TD.[1TDProcessor2LastName],
tblProcessor1TD.[1TDProcessor2FirstName],
tblProcessor1TD.[1TDProcessor2ComAmt],
tblProcessor1TD.[1TDProcessor2Pmt1Amt],
tblProcessor1TD.[1TDProcessor2Pmt2Amt],
tblProcessor1TD.[1TDProcessor2Pmt3Amt], tblProcessor1TD.[1TDProcessor2Pmt4Amt]
FROM tblProcessor1TD;

SELECT tblSource.ClientID, tblSource.SourceLastName,
tblSource.SourceFirstName, tblSource.SourceComAmt, tblSource.SourcePmt1Amt,
tblSource.SourcePmt2Amt, tblSource.SourcePmt3Amt, tblSource.SourcePmt4Amt
FROM tblSource;

Thanks
Click to expand...
Sorry make that 8 queries
 
TRY changing the query to all left joins. If that fails to return the desired
result, then try changing all the joins to right joins.


SELECT qryClientAcct.*, qryProcessor1TDAcct.*, qryProcessor1TD2Acct.*,
qryAgent1Acct.*, qryAgent2Acct.*, qryAgent3Acct.*, qrySourceAcct.*
FROM qrySourceAcct LEFT JOIN
(qryAgent3Acct LEFT JOIN
(qryAgent2Acct LEFT JOIN
(qryAgent1Acct LEFT JOIN
(qryProcessor1TD2Acct LEFT JOIN
(qryProcessor1TDAcct LEFT JOIN
qryClientAcct ON (qryClientAcct.ClientID =
qryProcessor1TDAcct.ClientID) AND (qryProcessor1TDAcct.ClientID =
qryClientAcct.ClientID)) ON (qryProcessor1TDAcct.ClientID =
qryProcessor1TD2Acct.ClientID) AND (qryProcessor1TD2Acct.ClientID =
qryProcessor1TDAcct.ClientID)) ON (qryProcessor1TD2Acct.ClientID =
qryAgent1Acct.ClientID) AND (qryAgent1Acct.ClientID =
qryProcessor1TD2Acct.ClientID)) ON (qryAgent1Acct.ClientID =
qryAgent2Acct.ClientID) AND (qryAgent2Acct.ClientID =
qryAgent1Acct.ClientID)) ON (qryAgent2Acct.ClientID = qryAgent3Acct.ClientID)
AND (qryAgent3Acct.ClientID = qryAgent2Acct.ClientID)) ON
(qryAgent3Acct.ClientID = qrySourceAcct.ClientID) AND (qrySourceAcct.ClientID
= qryAgent3Acct.ClientID);


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Ok, this is returning 83 records when there should only be 80. I tried the
LEFT & RIGHT join and get the same thing.

Thanks John
 
That means that someplace in all that query you have at least one record in a
table/query that matches more than one record in another table/query. Along
with some that don't match at all, but are being returned since the join is an
outer (left or right) join.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top