A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2003 INNER JOIN



 
 
Thread Tools Display Modes
  #1  
Old June 24th, 2008, 05:56 PM posted to microsoft.public.access.queries
KC_Cheer_Coach[_2_]
external usenet poster
 
Posts: 32
Default Access 2003 INNER JOIN

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?
  #2  
Old June 24th, 2008, 06:29 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Access 2003 INNER JOIN

You provided the data, but not the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"KC_Cheer_Coach" wrote in 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?



  #3  
Old June 24th, 2008, 06:50 PM posted to microsoft.public.access.queries
KC_Cheer_Coach[_2_]
external usenet poster
 
Posts: 32
Default Access 2003 INNER JOIN

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" wrote in 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?




  #4  
Old June 25th, 2008, 05:46 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Access 2003 INNER JOIN

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" wrote in message
...
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" wrote in
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?






  #5  
Old June 26th, 2008, 11:02 PM posted to microsoft.public.access.queries
KC_Cheer_Coach[_2_]
external usenet poster
 
Posts: 32
Default Access 2003 INNER JOIN

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" wrote in message
...
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" wrote in
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?






  #6  
Old July 7th, 2008, 07:08 AM posted to microsoft.public.access.queries
KC_Cheer_Coach[_2_]
external usenet poster
 
Posts: 32
Default Access 2003 INNER JOIN

Thank you! that works great!

"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" wrote in message
...
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" wrote in
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?






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.