![]() |
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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 | |
|
|