Prev: Union Queries - Parameter Value Prompt
Next: Crosstab Query: Column Headings Replicating Fields!
From: KC_Cheer_Coach on 24 Jun 2008 12:56
My Inner Join is not working correctly. I have changed it so many times to
what should make sense, but my results now fluctuate from 18M records to 0
records!

This is what I have:
Table1: c.master, c.subacct, c.description, c.custid, c.custdiscount,
c.[master/sub discount]
Row 1 Data:
911111111, 911111112, TEST, 911111113, $200,000.00, NULL
Row 2 Data:
911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $200,000.00
Row 3 Data:
911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $200,000.00

Table2: p.master, p.subacct, p.description, p.custid, p.custdiscount,
p.[master/sub discount]
Row 1 Data:
911111111, 911111112, TEST, 911111113, $195,230.47, NULL
Row 2 Data:
911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $195,230.47
Row 3 Data:
911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47

I created a UNION that encompasses a LEFT JOIN on table 1, LEFT JOIN on
table 2, and INNER JOIN. This UNION returns the following:
Field names: master, subacct, description, custid, p.custdiscount,
p.masterdiscount, c.custdiscount, c.masterdiscount, [custdiscount
difference], [master/sub level disct diff].
Row 1 Data: 911111111, 911111112, TEST, 911111113, $195,230.47, NULL,
$200,000.00, NULL, $4,769.53, NULL
Row 2 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL,
$195,230.47, NULL, NULL, NULL, ($195,230.47)
Row 3 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, NULL, NULL,
$200,000.00, NULL, $200,000.00
Row 4 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47,
NULL, NULL, NULL, ($195,230.47)
Row 5 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, NULL, NULL,
$200,000.00, NULL, $200,000.00

Sorry, I wanted to make sure I gave you enough information this time~!
So, the qrys work in that all of my data is compared as in row 1, however
anywhere there is a "TOTAL FOR MASTER" or "TOTAL FOR SUBACCT" in the
description, it shows up on two lines when it should be on one line straight
across with the difference. There is also a "TOTAL FOR CYCLE" at the end of
each table where the master, subacct and custid are null, so I have to return
it based on those AND on masterdiscount is not null.

No matter what I try, I cannot get those "TOTAL FOR..." lines to compare
side by side like I can for the rest of the lines.

Can anyone help?
From: Jeff Boyce on 24 Jun 2008 13:29
You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in message
news:0B15162D-B7DA-49A0-84A4-F8029FF19E9F(a)microsoft.com...
> My Inner Join is not working correctly. I have changed it so many times to
> what should make sense, but my results now fluctuate from 18M records to 0
> records!
>
> This is what I have:
> Table1: c.master, c.subacct, c.description, c.custid, c.custdiscount,
> c.[master/sub discount]
> Row 1 Data:
> 911111111, 911111112, TEST, 911111113, $200,000.00, NULL
> Row 2 Data:
> 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $200,000.00
> Row 3 Data:
> 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $200,000.00
>
> Table2: p.master, p.subacct, p.description, p.custid, p.custdiscount,
> p.[master/sub discount]
> Row 1 Data:
> 911111111, 911111112, TEST, 911111113, $195,230.47, NULL
> Row 2 Data:
> 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $195,230.47
> Row 3 Data:
> 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47
>
> I created a UNION that encompasses a LEFT JOIN on table 1, LEFT JOIN on
> table 2, and INNER JOIN. This UNION returns the following:
> Field names: master, subacct, description, custid, p.custdiscount,
> p.masterdiscount, c.custdiscount, c.masterdiscount, [custdiscount
> difference], [master/sub level disct diff].
> Row 1 Data: 911111111, 911111112, TEST, 911111113, $195,230.47, NULL,
> $200,000.00, NULL, $4,769.53, NULL
> Row 2 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL,
> $195,230.47, NULL, NULL, NULL, ($195,230.47)
> Row 3 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, NULL,
> NULL,
> $200,000.00, NULL, $200,000.00
> Row 4 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47,
> NULL, NULL, NULL, ($195,230.47)
> Row 5 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, NULL, NULL,
> $200,000.00, NULL, $200,000.00
>
> Sorry, I wanted to make sure I gave you enough information this time~!
> So, the qrys work in that all of my data is compared as in row 1, however
> anywhere there is a "TOTAL FOR MASTER" or "TOTAL FOR SUBACCT" in the
> description, it shows up on two lines when it should be on one line
> straight
> across with the difference. There is also a "TOTAL FOR CYCLE" at the end
> of
> each table where the master, subacct and custid are null, so I have to
> return
> it based on those AND on masterdiscount is not null.
>
> No matter what I try, I cannot get those "TOTAL FOR..." lines to compare
> side by side like I can for the rest of the lines.
>
> Can anyone help?


From: KC_Cheer_Coach on 24 Jun 2008 13:50
Oh gosh! Sorry about that. This is the one that works except for the fact
that my TOTAL lines are not joined together. I tried to create another query
where c.description = p.description or where custid is null, but the first
one returns 18M recs and the latter returns 0!

SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION, CURRENT.CUSTID,
CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS. CUSTDISCOUNT,
PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB DISCOUNT],0)
as [MASTER/SUB LEVEL DISCT DIFF]
FROM [CURRENT]
LEFT JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
(CURRENT.SUBACCT= PREVIOUS. SUBACCT) AND (CURRENT.MASTER = PREVIOUS.MASTER))
UNION
SELECT PREVIOUS.MASTER, PREVIOUS.SUBACCT, PREVIOUS.DESCRIPTION,
PREVIOUS.CUSTID, PREVIOUS.CUSTDISCOUNT, PREVIOUS.[MASTER/SUB DISCOUNT],
CURRENT. CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT],
Nz(CURRENT.CUSTDISCOUNT,0) - Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT
DIFFERENCE],
Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB DISCOUNT],0)
as [MASTER/SUB LEVEL DISCT DIFF]
FROM PREVIOUS
LEFT JOIN [CURRENT] ON ((PREVIOUS.CUSTID = CURRENT.CUSTID) AND
(PREVIOUS.SUBACCT = CURRENT.SUBACCT) AND (PREVIOUS.MASTER = CURRENT.MASTER))
UNION
SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION, CURRENT.CUSTID,
CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS. CUSTDISCOUNT,
PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB DISCOUNT],0)
as [MASTER/SUB LEVEL DISCT DIFF]
FROM [CURRENT]
INNER JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
(CURRENT.SUBACCT = PREVIOUS.SUBACCT) AND (CURRENT.MASTER = PREVIOUS.MASTER))
ORDER BY CURRENT.MASTER DESC , CURRENT.SUBACCT DESC , CURRENT.CUSTID DESC;


"Jeff Boyce" wrote:

> You provided the data, but not the SQL statement.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in message
> news:0B15162D-B7DA-49A0-84A4-F8029FF19E9F(a)microsoft.com...
> > My Inner Join is not working correctly. I have changed it so many times to
> > what should make sense, but my results now fluctuate from 18M records to 0
> > records!
> >
> > This is what I have:
> > Table1: c.master, c.subacct, c.description, c.custid, c.custdiscount,
> > c.[master/sub discount]
> > Row 1 Data:
> > 911111111, 911111112, TEST, 911111113, $200,000.00, NULL
> > Row 2 Data:
> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $200,000.00
> > Row 3 Data:
> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $200,000.00
> >
> > Table2: p.master, p.subacct, p.description, p.custid, p.custdiscount,
> > p.[master/sub discount]
> > Row 1 Data:
> > 911111111, 911111112, TEST, 911111113, $195,230.47, NULL
> > Row 2 Data:
> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $195,230.47
> > Row 3 Data:
> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47
> >
> > I created a UNION that encompasses a LEFT JOIN on table 1, LEFT JOIN on
> > table 2, and INNER JOIN. This UNION returns the following:
> > Field names: master, subacct, description, custid, p.custdiscount,
> > p.masterdiscount, c.custdiscount, c.masterdiscount, [custdiscount
> > difference], [master/sub level disct diff].
> > Row 1 Data: 911111111, 911111112, TEST, 911111113, $195,230.47, NULL,
> > $200,000.00, NULL, $4,769.53, NULL
> > Row 2 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL,
> > $195,230.47, NULL, NULL, NULL, ($195,230.47)
> > Row 3 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, NULL,
> > NULL,
> > $200,000.00, NULL, $200,000.00
> > Row 4 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47,
> > NULL, NULL, NULL, ($195,230.47)
> > Row 5 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, NULL, NULL,
> > $200,000.00, NULL, $200,000.00
> >
> > Sorry, I wanted to make sure I gave you enough information this time~!
> > So, the qrys work in that all of my data is compared as in row 1, however
> > anywhere there is a "TOTAL FOR MASTER" or "TOTAL FOR SUBACCT" in the
> > description, it shows up on two lines when it should be on one line
> > straight
> > across with the difference. There is also a "TOTAL FOR CYCLE" at the end
> > of
> > each table where the master, subacct and custid are null, so I have to
> > return
> > it based on those AND on masterdiscount is not null.
> >
> > No matter what I try, I cannot get those "TOTAL FOR..." lines to compare
> > side by side like I can for the rest of the lines.
> >
> > Can anyone help?
>
>
>
From: Jeff Boyce on 25 Jun 2008 12:46
When I need to do two separate totals, then join by the totals, I create
three queries. Two separate totals queries, plus one joining the two
previous queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in message
news:19FAA779-5185-49FE-AB91-877FF50F01D5(a)microsoft.com...
> Oh gosh! Sorry about that. This is the one that works except for the fact
> that my TOTAL lines are not joined together. I tried to create another
> query
> where c.description = p.description or where custid is null, but the first
> one returns 18M recs and the latter returns 0!
>
> SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION,
> CURRENT.CUSTID,
> CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS.
> CUSTDISCOUNT,
> PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
> Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
> Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> DISCOUNT],0)
> as [MASTER/SUB LEVEL DISCT DIFF]
> FROM [CURRENT]
> LEFT JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
> (CURRENT.SUBACCT= PREVIOUS. SUBACCT) AND (CURRENT.MASTER =
> PREVIOUS.MASTER))
> UNION
> SELECT PREVIOUS.MASTER, PREVIOUS.SUBACCT, PREVIOUS.DESCRIPTION,
> PREVIOUS.CUSTID, PREVIOUS.CUSTDISCOUNT, PREVIOUS.[MASTER/SUB DISCOUNT],
> CURRENT. CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT],
> Nz(CURRENT.CUSTDISCOUNT,0) - Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT
> DIFFERENCE],
> Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> DISCOUNT],0)
> as [MASTER/SUB LEVEL DISCT DIFF]
> FROM PREVIOUS
> LEFT JOIN [CURRENT] ON ((PREVIOUS.CUSTID = CURRENT.CUSTID) AND
> (PREVIOUS.SUBACCT = CURRENT.SUBACCT) AND (PREVIOUS.MASTER =
> CURRENT.MASTER))
> UNION
> SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION,
> CURRENT.CUSTID,
> CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS.
> CUSTDISCOUNT,
> PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
> Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
> Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> DISCOUNT],0)
> as [MASTER/SUB LEVEL DISCT DIFF]
> FROM [CURRENT]
> INNER JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
> (CURRENT.SUBACCT = PREVIOUS.SUBACCT) AND (CURRENT.MASTER =
> PREVIOUS.MASTER))
> ORDER BY CURRENT.MASTER DESC , CURRENT.SUBACCT DESC , CURRENT.CUSTID DESC;
>
>
> "Jeff Boyce" wrote:
>
>> You provided the data, but not the SQL statement.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in
>> message
>> news:0B15162D-B7DA-49A0-84A4-F8029FF19E9F(a)microsoft.com...
>> > My Inner Join is not working correctly. I have changed it so many times
>> > to
>> > what should make sense, but my results now fluctuate from 18M records
>> > to 0
>> > records!
>> >
>> > This is what I have:
>> > Table1: c.master, c.subacct, c.description, c.custid, c.custdiscount,
>> > c.[master/sub discount]
>> > Row 1 Data:
>> > 911111111, 911111112, TEST, 911111113, $200,000.00, NULL
>> > Row 2 Data:
>> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $200,000.00
>> > Row 3 Data:
>> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $200,000.00
>> >
>> > Table2: p.master, p.subacct, p.description, p.custid, p.custdiscount,
>> > p.[master/sub discount]
>> > Row 1 Data:
>> > 911111111, 911111112, TEST, 911111113, $195,230.47, NULL
>> > Row 2 Data:
>> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $195,230.47
>> > Row 3 Data:
>> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47
>> >
>> > I created a UNION that encompasses a LEFT JOIN on table 1, LEFT JOIN on
>> > table 2, and INNER JOIN. This UNION returns the following:
>> > Field names: master, subacct, description, custid, p.custdiscount,
>> > p.masterdiscount, c.custdiscount, c.masterdiscount, [custdiscount
>> > difference], [master/sub level disct diff].
>> > Row 1 Data: 911111111, 911111112, TEST, 911111113, $195,230.47, NULL,
>> > $200,000.00, NULL, $4,769.53, NULL
>> > Row 2 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL,
>> > $195,230.47, NULL, NULL, NULL, ($195,230.47)
>> > Row 3 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, NULL,
>> > NULL,
>> > $200,000.00, NULL, $200,000.00
>> > Row 4 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47,
>> > NULL, NULL, NULL, ($195,230.47)
>> > Row 5 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, NULL, NULL,
>> > $200,000.00, NULL, $200,000.00
>> >
>> > Sorry, I wanted to make sure I gave you enough information this time~!
>> > So, the qrys work in that all of my data is compared as in row 1,
>> > however
>> > anywhere there is a "TOTAL FOR MASTER" or "TOTAL FOR SUBACCT" in the
>> > description, it shows up on two lines when it should be on one line
>> > straight
>> > across with the difference. There is also a "TOTAL FOR CYCLE" at the
>> > end
>> > of
>> > each table where the master, subacct and custid are null, so I have to
>> > return
>> > it based on those AND on masterdiscount is not null.
>> >
>> > No matter what I try, I cannot get those "TOTAL FOR..." lines to
>> > compare
>> > side by side like I can for the rest of the lines.
>> >
>> > Can anyone help?
>>
>>
>>


From: KC_Cheer_Coach on 26 Jun 2008 18:02
Thank you. I will try that and let you know how it works out.

"Jeff Boyce" wrote:

> When I need to do two separate totals, then join by the totals, I create
> three queries. Two separate totals queries, plus one joining the two
> previous queries.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in message
> news:19FAA779-5185-49FE-AB91-877FF50F01D5(a)microsoft.com...
> > Oh gosh! Sorry about that. This is the one that works except for the fact
> > that my TOTAL lines are not joined together. I tried to create another
> > query
> > where c.description = p.description or where custid is null, but the first
> > one returns 18M recs and the latter returns 0!
> >
> > SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION,
> > CURRENT.CUSTID,
> > CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS.
> > CUSTDISCOUNT,
> > PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
> > Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
> > Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> > DISCOUNT],0)
> > as [MASTER/SUB LEVEL DISCT DIFF]
> > FROM [CURRENT]
> > LEFT JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
> > (CURRENT.SUBACCT= PREVIOUS. SUBACCT) AND (CURRENT.MASTER =
> > PREVIOUS.MASTER))
> > UNION
> > SELECT PREVIOUS.MASTER, PREVIOUS.SUBACCT, PREVIOUS.DESCRIPTION,
> > PREVIOUS.CUSTID, PREVIOUS.CUSTDISCOUNT, PREVIOUS.[MASTER/SUB DISCOUNT],
> > CURRENT. CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT],
> > Nz(CURRENT.CUSTDISCOUNT,0) - Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT
> > DIFFERENCE],
> > Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> > DISCOUNT],0)
> > as [MASTER/SUB LEVEL DISCT DIFF]
> > FROM PREVIOUS
> > LEFT JOIN [CURRENT] ON ((PREVIOUS.CUSTID = CURRENT.CUSTID) AND
> > (PREVIOUS.SUBACCT = CURRENT.SUBACCT) AND (PREVIOUS.MASTER =
> > CURRENT.MASTER))
> > UNION
> > SELECT CURRENT.MASTER, CURRENT.SUBACCT, CURRENT.DESCRIPTION,
> > CURRENT.CUSTID,
> > CURRENT.CUSTDISCOUNT, CURRENT.[MASTER/SUB DISCOUNT], PREVIOUS.
> > CUSTDISCOUNT,
> > PREVIOUS.[MASTER/SUB DISCOUNT], Nz(CURRENT.CUSTDISCOUNT,0) -
> > Nz(PREVIOUS.CUSTDISCOUNT,0) as [CUSTDISCOUNT DIFFERENCE],
> > Nz(CURRENT. [MASTER/SUB DISCOUNT],0) - Nz(PREVIOUS. [MASTER/SUB
> > DISCOUNT],0)
> > as [MASTER/SUB LEVEL DISCT DIFF]
> > FROM [CURRENT]
> > INNER JOIN PREVIOUS ON ((CURRENT.CUSTID = PREVIOUS.CUSTID) AND
> > (CURRENT.SUBACCT = PREVIOUS.SUBACCT) AND (CURRENT.MASTER =
> > PREVIOUS.MASTER))
> > ORDER BY CURRENT.MASTER DESC , CURRENT.SUBACCT DESC , CURRENT.CUSTID DESC;
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> You provided the data, but not the SQL statement.
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "KC_Cheer_Coach" <KCCheerCoach(a)discussions.microsoft.com> wrote in
> >> message
> >> news:0B15162D-B7DA-49A0-84A4-F8029FF19E9F(a)microsoft.com...
> >> > My Inner Join is not working correctly. I have changed it so many times
> >> > to
> >> > what should make sense, but my results now fluctuate from 18M records
> >> > to 0
> >> > records!
> >> >
> >> > This is what I have:
> >> > Table1: c.master, c.subacct, c.description, c.custid, c.custdiscount,
> >> > c.[master/sub discount]
> >> > Row 1 Data:
> >> > 911111111, 911111112, TEST, 911111113, $200,000.00, NULL
> >> > Row 2 Data:
> >> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $200,000.00
> >> > Row 3 Data:
> >> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $200,000.00
> >> >
> >> > Table2: p.master, p.subacct, p.description, p.custid, p.custdiscount,
> >> > p.[master/sub discount]
> >> > Row 1 Data:
> >> > 911111111, 911111112, TEST, 911111113, $195,230.47, NULL
> >> > Row 2 Data:
> >> > 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, $195,230.47
> >> > Row 3 Data:
> >> > 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47
> >> >
> >> > I created a UNION that encompasses a LEFT JOIN on table 1, LEFT JOIN on
> >> > table 2, and INNER JOIN. This UNION returns the following:
> >> > Field names: master, subacct, description, custid, p.custdiscount,
> >> > p.masterdiscount, c.custdiscount, c.masterdiscount, [custdiscount
> >> > difference], [master/sub level disct diff].
> >> > Row 1 Data: 911111111, 911111112, TEST, 911111113, $195,230.47, NULL,
> >> > $200,000.00, NULL, $4,769.53, NULL
> >> > Row 2 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL,
> >> > $195,230.47, NULL, NULL, NULL, ($195,230.47)
> >> > Row 3 Data: 911111111, 911111112, TOTAL FOR SUBACCT, NULL, NULL, NULL,
> >> > NULL,
> >> > $200,000.00, NULL, $200,000.00
> >> > Row 4 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, $195,230.47,
> >> > NULL, NULL, NULL, ($195,230.47)
> >> > Row 5 Data: 911111111, NULL, TOTAL FOR MASTER, NULL, NULL, NULL, NULL,
> >> > $200,000.00, NULL, $200,000.00
> >> >
> >> > Sorry, I wanted to make sure I gave you enough information this time~!
> >> > So, the qrys work in that all of my data is compared as in row 1,
> >> > however
> >> > anywhere there is a "TOTAL FOR MASTER" or "TOTAL FOR SUBACCT" in the
> >> > description, it shows up on two lines when it should be on one line
> >> > straight
> >> > across with the difference. There is also a "TOTAL FOR CYCLE" at the
> >> > end
> >> > of
> >> > each table where the master, subacct and custid are null, so I have to
> >> > return
> >> > it based on those AND on masterdiscount is not null.
> >> >
> >> > No matter what I try, I cannot get those "TOTAL FOR..." lines to
> >> > compare
> >> > side by side like I can for the rest of the lines.
> >> >
> >> > Can anyone help?
> >>
> >>
> >>
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Union Queries - Parameter Value Prompt
Next: Crosstab Query: Column Headings Replicating Fields!