Query Not Returning Correct Amount of Records

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


0
Utf
1/22/2010 6:00:01 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
791 Views

Similar Articles

[PageSpeed] 6

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

blanch2010 wrote:
> 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
> 
> 
0
John
1/22/2010 6:12:17 PM
On Fri, 22 Jan 2010 10:00:01 -0800, blanch2010
<blanch2010@discussions.microsoft.com> wrote:

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


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]
0
John
1/22/2010 6:44:52 PM

"John Spencer" wrote:

> 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
> 
> blanch2010 wrote:
> > 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
> > 
> > 
> .
> 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);
0
Utf
1/22/2010 7:18:01 PM

"John W. Vinson" wrote:

> On Fri, 22 Jan 2010 10:00:01 -0800, blanch2010
> <blanch2010@discussions.microsoft.com> wrote:
> 
> >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
> >
> 
> 
> 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:
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

0
Utf
1/22/2010 7:30:10 PM

"blanch2010" wrote:

> 
> 
> "John W. Vinson" wrote:
> 
> > On Fri, 22 Jan 2010 10:00:01 -0800, blanch2010
> > <blanch2010@discussions.microsoft.com> wrote:
> > 
> > >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
> > >
> > 
> > 
> > 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:
> 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
> 
Sorry make that 8 queries
0
Utf
1/22/2010 7:39:01 PM
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

blanch2010 wrote:
> 
> "John Spencer" wrote:
> 
>> 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
>>
>> blanch2010 wrote:
>>> 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
>>>
>>>
>> .
>> 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);
0
John
1/22/2010 9:31:10 PM
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

"John Spencer" wrote:

> 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
> 
> blanch2010 wrote:
> > 
> > "John Spencer" wrote:
> > 
> >> 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
> >>
> >> blanch2010 wrote:
> >>> 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
> >>>
> >>>
> >> .
> >> 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);
> .
> 
0
Utf
1/22/2010 11:07:02 PM
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

blanch2010 wrote:
> 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
> 
> "John Spencer" wrote:
> 
>> 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
>>
>> blanch2010 wrote:
>>> "John Spencer" wrote:
>>>
>>>> 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
>>>>
>>>> blanch2010 wrote:
>>>>> 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
>>>>>
>>>>>
>>>> .
>>>> 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);
>> .
>>
0
John
1/23/2010 5:28:54 PM
Thank you John.  I will look for that.  I again thank you for your help.

Don

"John Spencer" wrote:

> 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
> 
> blanch2010 wrote:
> > 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
> > 
> > "John Spencer" wrote:
> > 
> >> 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
> >>
> >> blanch2010 wrote:
> >>> "John Spencer" wrote:
> >>>
> >>>> 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
> >>>>
> >>>> blanch2010 wrote:
> >>>>> 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
> >>>>>
> >>>>>
> >>>> .
> >>>> 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);
> >> .
> >>
> .
> 
0
Utf
1/26/2010 3:31:01 AM
Reply:

Similar Artilces:

Query
I have a transaction # for each record in my main form with a subform "Approvals" and "Checkouts". I created a query to show the sum of all Approvals and all Checkouts by Transaction # for each record. (one for Accruals and one for Checkouts). All approvals and checkouts come up in these queries. I want to create a report showing, per Cost Center, the $ amt of Approvals, the $ amt of Checkouts, and a calculated field to show the remaining value. The report comes up with all approvals and checkouts per cost center, however, if there is an approval that does not hav...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Migrating notes records
Using Data Migration Manager I have uploaded 68000+ notes to CRM 4. However all notes show a creation date of 5/10/2008. Even more strangely, I have found that the AnnotationBase table contains the correct dates from the import file in the CreatedOn field, but has not set the OverriddenCreatedOn field. The server was built a few months ago, but certainly not in October 2008 - anyone have ideas as to how I can persuade CRM to recognise the values in CreatedOn? ...

pre-select new record in combo box
Hi, I have a form (frmActivity) with a combo box (cboStore) that gets its data from a table (Store). If there is a new store which dose not show in the combo box I have a command button on the same form that open a popup window for entering the name of the new store. In the 'on close' event of the popup I requeary the combo box (cboStore) on the main form so that the new store will appear. What I would really like is to have the new store already selected in the combo box when the popup is closed. Is this possible and if so how? Thanks, Phil Try after the requery of the ...

Windows Mail won't allow me to sign in with correct Usr/pass
I'm having a problem connecting to my windows mail. I have it set up throught my wow account but today i had to reset my password so that i could check my email. now it will now allow me to log in i keep getting this error message The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please c...

Crosstab query totals
Need some help, I have a crosstab query that returns the following data see sql below, what I need is to total all gearbox types that are R and 37 and R and 27 and R and 47 etc. an example R and RF 27 total = 492 <> 1 2 4 SubTotals R 27 20 26 303 12 361 R 37 6 66 307 8 387 R 47 8 87 424 2 521 R47R 37 2 2 R57R 37 3 2 5 R67R 37 1 1 2 R77R 37 19 19 RF 27 4 17 109 1 131 RF 37 1 11 76 88 RF 47 1 25 39 65 TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType ...

location of web query
I have a file that refreshed 2006 data that I am now changing to 2007 but I cannot recall where a web query is on a certain worksheet, is there an easy way to find in which cell the query resides? ...

Access 2007 SQL Pass Through Queries
I have an Access 2003 mdb that I use to connect to a SQL 2005 database. When I open the application in Access 2007 it errors on using SQL pass through queries. I see that pass through queries (amongst other options) have been disabled under Access 2007. However it seems that they are enabled under certain circumstances so I've been trying to get them working by changing the security settings. The queries are still failing after setting the following: - Sandbox Mode to 0 (turn off sandbox mode for all apps) - Applied a recognised Digital Signature - Trusted the Publisher - Trusted the loc...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Attachments to window notes vs record notes
Got an odd situation. On record notes we have the paperclip and can attach files with no problems. On window notes there is no paperclip. Never tried to attach a document to a window note before but always assumed that both record level and window level notes would act the same way. Has anyone else seen this? Should we be able to attach documents to window level notes? GP10, SP3. Thx. No - the behaviour you are seeing is by design. Window notes have never allowed for OLE attachments. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC h...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Pivot table returns `
I have data from an SQL WBC-A. When I pivot this data it returns ` (The character below the tilde) Any other data returns the correct pivot. E.g. WBC-X returns WBC-X ...

How to display XML string returned by CRM Accountobject.retrive me
Hi All, Please let me know how i can display the query results fetched from crm database into Datagrid directly. Any help is appreciated. "Vijay Kr. Poriya" wrote: > Hi All, > > Please let me know how i can display the query results fetched from crm > database into Datagrid directly. > > Any help is appreciated. > > Vijay - I assume that you are referring to using the CRMAccount.Retrieve method documented at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk1_2/htm/v1d2crmaccountretrievemethod.asp This method will retrie...

Quest: Console app Build tool return val to VS output window...
I have a console app the I built that I run in another project post-build. Now I want the VC++ build process of the build app to think there is a build error and stop running the post-build cmds so what return value can I exit the console app with that will stop build like the VC++ standard complier and linker command line program can stop for standard build errors... Any help... Any value other than zero will cause the build process to stop... HTH -- ============ Frank Hickman NobleSoft, Inc. ============ > Any value other than zero will cause the build process to stop... Thanks t...

Corrupt record in Money 2004
I have one record in my .mny file that I can't clear when I try to accept it after it has downloaded from my credit card site. I keep getting the message that the transaction cannot be entered and it remains outstanding - bolded. I tried the salvage processes - msmoney.exe -s and salv.exe. Neither worked. I just want to delete this one record and put it back manually. Is there a way to do this with jet database functionality? Not that is exposed to any of us users. The database is actually "msisam"--a buttoned up derivative of Jet. "Bill Boffi" <anonymo...

How to create new record
How can i use code to create a new record into a table. Something like this? CreateNewRecord("John","Smith") I tried to Google it but i can't seem to find the answer. help! thanks On Wed, 4 Jul 2007 19:34:03 -0500, "Tim McGavin" <nws@gsw-inc.com> wrote: >How can i use code to create a new record into a table. > >Something like this? > >CreateNewRecord("John","Smith") > >I tried to Google it but i can't seem to find the answer. > >help! >thanks > Open a Recordset based on the table and use ...

PaperSizes.Count returns 0
Hi All I have a Hp printer 1280 that is installed in windows 7. when I use following code to get Paper count. System.Drawing.Printing.PrinterSettings p = new System.Drawing.Printing.PrinterSettings(); MessageBox.Show(p.PaperSizes.Count.ToString()); it reutrns 0.I can't print from my programm but printer directly works fine. System.Drawing.Printing.PrinterSettings p = new System.Drawing.Printing.PrinterSettings(); p.PrinterName is returned correctly except PaperSize.Count. my program unser winXp works correctly but Win7. Is it possible it's caused by Printer ...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

Can you help me with a troublesome query?
I'd like some help formulating a query for the following situation. The problem I actually have to solve is somewhat complicated, so I've extracted the essence of the problem into the simplified situation described below. While I know I could do this with an ugly cursor solution, I'm pretty sure that performance would be terrible as the table grows to a large size, which it will in the real problem I have to solve. So I'd like to hear your best ideas on how to solve this in a way that will scale to as high as a million records or more. Consider this table: ...

Inquiry Return Sort
Just upgraded to 10 sp3. Sales Order Processing Item Inquiry returns values in a dislogical order. Well, I am sure it is logical but not overly worthwhile. Anyway, my users are telling me it would sort it previously by date. Right now if they select a single item for the search, the list returned does not sort by document number or by date from what we can tell. This makes it very difficult to find the last sale of an item. Can anyone tell me how to change this? I am guessing the sort order is defined in a SQL Stored Procedure that processes the redisplay/update for this window? Allen...

What does the program return value mean, for example 0(0x0) or 2 (0x2), when debug a program?
What does the program return value mean, for example 0(0x0) or 2 (0x2), when debug a program? -- ___________________ Xiongjun Xia It's what WinMain (or main) returns. -- Regards, Nish [VC++ MVP] "Xia Xiongjun" <xj-14@126.com> wrote in message news:%23wHd$78tFHA.2392@tk2msftngp13.phx.gbl... > What does the program return value mean, for example 0(0x0) or 2 (0x2), > when debug a program? > > -- > ___________________ > Xiongjun Xia > Think of these as random numbers. That way you won't be misled into thinking they have any significanc...

Query plan isn't doing an index seek
If you have the following: SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = ws.VendorID) FROM Work ws What I get is a Hash Match from 2 index scans. |--Compute Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName])) |--Hash Match(Right Outer Join, HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as [ws].[VendorId])) |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR])) |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...

Adding records
Is there any way to add a record into the middle of a database without having to add it at the end and then sort. I need to be able to manually add records into the database, as though you would in excell, exactly where I want it without having to use sort since not all of the data can/should be sorted. Any thoughts? Tables are unordered. If you need to Order records then you use a query. You then need a way of defining that order. A more specific description of your problem including descriptions of the fields involved will get you a more specific answer. Cheers, Jason Lepack On M...

Data/Values change when Query Analyzed in Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

FW: Try corrective update
--lkdjfwcphrqtozfp Content-Type: multipart/related; boundary="ygblvpmg"; type="multipart/alternative" --ygblvpmg Content-Type: multipart/alternative; boundary="lcxxrzqr" --lcxxrzqr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help maintain the security of yo...