Query Not Returning Correct Amount of Records

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
 
J

John Spencer

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
 
J

John W. Vinson

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!
 
B

blanch2010

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);
 
B

blanch2010

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
 
B

blanch2010

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
 
J

John Spencer

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
 
B

blanch2010

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
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incone & Expenses Statement 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
QBF & Query Malfunction 0
Query Returning Multiple Results 1
Query return duplicate results 1
Why does MSAccess cut query after 2886000 records 1
Pass Through Query - not returning rows 1
Can there be too many sub queries within a single query? 4

Top