Access 2003 INNER JOIN

K

KC_Cheer_Coach

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

Jeff Boyce

You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

KC_Cheer_Coach

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 said:
You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

KC_Cheer_Coach said:
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?
Click to expand...
Click to expand...
 
J

Jeff Boyce

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 said:
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 said:
You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
K

KC_Cheer_Coach

Thank you. I will try that and let you know how it works out.

Jeff Boyce said:
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 said:
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 said:
You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 
K

KC_Cheer_Coach

Thank you! that works great!

Jeff Boyce said:
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 said:
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 said:
You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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?
Click to expand...
Click to expand...
Click to expand...
 

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

complex join issue - access 2003 2
Access 2003 adp/proxy security - A substitute for SYSTEM_USER() 6
FWT Newsletter - Weekly - November 10, 2003 3

Top