asked on

How do you select values from 3 different tables in a single select statement?

What we need to focus on in the three table structures I have are:

Table Disbursements:
SIN
YEAR
T1_REFUND
OWES_REV
EXP_REF
CKDATE

Table Receipts:
DATE_REC
SIN
TYPE
AMT_REC

Table Adjustments:
SIN
DATE
AMT

The primary keys for all tables are sequence numbers. They are all related by SIN numbers.

What I need to do is below in the Code Snippet. What type of join statement should I use to solve my two problems.

1. I need the select statement to work if there is only 1 record in 1 table (doesn't matter which table).
For Example:
If there is an adjustment but no disbursements / receipts I want the adjustment value to show.
If there is a disbursements but no receipts/adjustments. I want disbursement values to show.
If there is a receipt but no disbursements/adjustments. I want the receipt values to show.

In other words, I do not want the select statement to be based on one particular table. Is this possible with just one select statement?

2. The amount I'm getting for " SUM(tblAdjustments.decAMT) AS ADJUSTMENT" is X times the true value of it where X is the record count for receipts. For example, if I have two receipt records with the same SIN, I will get 2X the value of what (SUM(tblAdjustments.decAMT) AS ADJUSTMENT) is in the database. I believe it has something to do with the joins but I do not know what join I should use.

SELECT     Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV, 
                      Disbursements.C_SCH1_REFUND AS EXP_REF, Receipts.YEAR AS [Receipt.Year], 
                      SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS REVENUE, 
                      SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC], 
                      SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT], 
                      SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT], 
                      SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM         Disbursements FULL OUTER JOIN
                      tblAdjustments ON Disbursements.SIN = tblAdjustments.strSIN FULL OUTER JOIN
                      Receipts ON tblAdjustments.strSIN = Receipts.SIN AND Disbursements.SIN = Receipts.SIN
WHERE     (Disbursements.CKDATE <= '10/31/2007')
GROUP BY Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND, Disbursements.C_OWES_REV, Disbursements.C_SCH1_REFUND, 
                      Receipts.YEAR

Open in new window

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnnyBCJ

ASKER

My problem with 'Union' is from my understanding it can only be used if the values from both tables are the same.
This works:

SELECT SIN
FROM Disbursements
UNION
SELECT SIN
FROM Receipts
UNION
SELECT strSIN
FROM tblAdjustments

This doesn't work:

SELECT SIN, YEAR, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
FROM Disbursements
UNION

SELECT
SIN,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM Receipts
group by SIN
UNION

SELECT
strSIN,
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
group by strSIN

Problem: All Queries combined using a union, intersect or except operator must have an equal number of expressions in their target lists.

I understand what is causing the issue, it's because disbursements selects 5 objects, receipts selects 5, adjustments select 2. The only common value they all have is SIN/strSIN.

SELECT SIN, YEAR, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION

SELECT
SIN,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
group by SIN
UNION

SELECT
strSIN,
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
group by strSIN

Problem: Incorrect syntax near 'UNION', Incorrect syntax near 'GROUP', Incorrect syntax near 'GROUP'.
I'm confused on how you can have a left join and a union. Left Join combines everything that is on the left

SELECT Disbursements.SIN, Disbursements.YEAR, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV,
Disbursements.C_SCH1_REFUND AS EXP_REF
FROM Disbursements LEFT OUTER JOIN
tblAdjustments ON Disbursements.SIN = tblAdjustments.strSIN LEFT OUTER JOIN
Receipts ON Disbursements.SIN = Receipts.SIN
UNION
SELECT Receipts_2.SIN, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT]
FROM Receipts AS Receipts_2 LEFT OUTER JOIN
tblAdjustments AS tblAdjustments_2 ON Receipts_2.SIN = tblAdjustments_2.strSIN LEFT OUTER JOIN
Disbursements AS Disbursements_2 ON Receipts_2.SIN = Disbursements_2.SIN
GROUP BY Receipts_2.SIN
UNION
SELECT tblAdjustments_1.strSIN, SUM(tblAdjustments_1.decAMT) AS ADJUSTMENT
FROM tblAdjustments AS tblAdjustments_1 LEFT OUTER JOIN
Disbursements AS Disbursements_1 ON tblAdjustments_1.strSIN = Disbursements_1.SIN LEFT OUTER JOIN
Receipts AS Receipts_1 ON tblAdjustments_1.strSIN = Receipts_1.SIN
GROUP BY tblAdjustments_1.strSIN

Problem: All Queries combined using a union, intersect or except operator must have an equal number of expressions in their target lists.

Am I getting close to what I should have done?

ASKER CERTIFIED SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnnyBCJ

ASKER

My issue with that Eszaq is that I need to be able to select different columns/data types from different tables in order to get the select statement to work. I understand that if I needed all the SIN from two tables that I would be able to do a 'union all' by selecting sin on both tables and it would give me all the sin on both tables but it doesn't work when I need to be able to select SIN, Year, Amount from adjustment table and SIN, Type, Amount from receipts (or when I need to do a case statement on the receipt type).

Maybe Union is not the way to go. I am thinking that creating a temp table combining all 3 tables may be the way to go. I believe that will allow me to do what I need.

eszaq

Of course, replacement value for missing column does not necessarily have to be NULL. You can populate field with some dummy value (of the same data type), or use CAST / CONVERT functions to stick in value from field with different datatype - whatever makes sense in terms of making result set easier to read and meaningful for your application.
BUT you have to list ALL the columns in the same order in each select in your query:
(BTW is it typo - strSIN? If not and it is text type not number, you'll have to convert numbers tochar)

SELECT SIN, YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF,
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT],
AS ADJUSTMENT
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION
 
SELECT
SIN, NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT],
0 AS ADJUSTMENT
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
group by SIN
UNION
 
SELECT
strSIN,  NULL as YEAR, AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT]
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
 
GROUP by 1

Open in new window

eszaq

Not sure if I grabbed right query to alter, just wanted to demonstrate the point.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eszaq

Oh, I completely overlooked that you have GROUP BY clauses. Try this:

SELECT SIN, 
YEAR,  C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF,
NULL AS REVENUE, NULL AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT],
NULL AS ADJUSTMENT
FROM Disbursements
LEFT JOIN tblAdjustments
LEFT JOIN Receipts
UNION
 
SELECT SIN, 
NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF,
SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS REVENUE,
SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT],
SUM(NULL) AS ADJUSTMENT
FROM Receipts
LEFT JOIN tblAdjustments
LEFT JOIN Disbursements
GROUP by  1,2,3,4,5
 
UNION
 
SELECT strSIN,  
NULL as YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF,
SUM(NULL) AS REVENUE, SUM(NULL) AS [CLIENT.REC], SUM(NULL) AS [CLIENT.SHORT], SUM(NULL) AS [SPOUSE.SHORT],
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
LEFT JOIN Disbursements
LEFT JOIN Receipts
GROUP by 1,2,3,4,5

Open in new window

SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

The general overal query should end up something like this:

SELECT Disb.YEAR, Disb.SIN, Disb.[T1 REFUND], Disb.OWES_REV, Disb.EXP_REF,
Rcpts.[Receipt.Year], Rcpts.REVENUE, Rcpts.[CLIENT.REC], Rcpts.[CLIENT.SHORT], Rcpts.[SPOUSE.SHORT],
Adjust.ADJUSTMENT
FROM (
SELECT Disbursements.YEAR, Disbursements.SIN, Disbursements.C_T1REFUND AS [T1 REFUND], Disbursements.C_OWES_REV AS OWES_REV,
Disbursements.C_SCH1_REFUND AS EXP_REF
FROM Disbursements
WHERE (Disbursements.CKDATE <= '10/31/2007')
) AS Disb
FULL OUTER JOIN (
SELECT Receipts.SIN, Receipts.YEAR AS [Receipt.Year],
SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS REVENUE,
SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM Receipts
GROUP BY Receipts.SIN, Receipts.YEAR
) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR
FULL OUTER JOIN (
SELECT tblAdjustments.SIN, /*, tblAdjustments.YEAR AS [YEAR], */
SUM(tblAdjustments.decAMT) AS ADJUSTMENT
FROM tblAdjustments
GROUP BY tblAdjustments.SIN /*, tblAdjustments.YEAR*/
) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN)
--ORDER BY ...

JohnnyBCJ

ASKER

I currently have

SELECT SIN, YEAR, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF, NULL AS REVENUE, NULL
AS [CLIENT.REC], NULL AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM Disbursements
WHERE (CKDATE <= '10/31/2007')
GROUP BY SIN, YEAR, C_T1REFUND, C_OWES_REV, C_SCH1_REFUND
UNION
SELECT SIN, YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0)
ELSE NULL END) AS REVENUE, SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.REC],
SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [CLIENT.SHORT],
SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE NULL END) AS [SPOUSE.SHORT], NULL AS ADJUSTMENT
FROM Receipts
WHERE (DATE_REC <= '10/31/2007')
GROUP BY SIN, YEAR
UNION
SELECT strSIN, strYear AS YEAR, NULL AS [T1 REFUND], NULL AS OWES_REV, NULL AS EXP_REF, NULL AS REVENUE, NULL AS [CLIENT.REC], NULL
AS [CLIENT.SHORT], NULL AS [SPOUSE.SHORT], SUM(decAMT) AS ADJUSTMENT
FROM tblAdjustments
WHERE (Date <= '10/31/2007')
GROUP BY strSIN, strYear

Which is great but my issue now is that it gives me 3 seperate records (for one each select statement). Is it possible to combine all 3 rows into 1? Like one huge group by SIN/Year?

Scott Pletcher

Yes, by using a FULL OUTER JOIN :-) .

JohnnyBCJ

ASKER

Syntax please?
Remember I want this to bring back results that are not table depended (which is what is happening). I'm afraid that full outer join will only bring back the results that have something in all 3 Disbursements/Adjustments/Receipts table. Maybe I'm wrong.

The above issue is also combined with the fact that of Disbursement.SIN.
If Disbursement.SIN returns a null I want it to be replaced by Receipt.SIN or Adjustment.SIN.

SELECT Disb.SIN, Disb.YEAR, Disb.[T1 REFUND], Disb.OWES_REV, Disb.EXP_REF, Rcpts.REVENUE, Rcpts.[CLIENT.REC], Rcpts.[CLIENT.SHORT],
Rcpts.[SPOUSE.SHORT], Adjst.ADJUSTMENT
FROM (SELECT YEAR, SIN, C_T1REFUND AS [T1 REFUND], C_OWES_REV AS OWES_REV, C_SCH1_REFUND AS EXP_REF
FROM Disbursements
WHERE (CKDATE <= '10/31/2007')) AS Disb FULL OUTER JOIN
(SELECT SIN, YEAR AS [Receipt.Year], SUM(CASE WHEN Receipts.[Type] = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END)
AS REVENUE, SUM(CASE WHEN Receipts.[Type] = 'CL' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],
SUM(CASE WHEN Receipts.[Type] = 'CS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],
SUM(CASE WHEN Receipts.[Type] = 'SS' THEN COALESCE (Receipts.AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT]
FROM Receipts
WHERE (DATE_REC <= '10/31/2007')
GROUP BY SIN, YEAR) AS Rcpts ON Rcpts.SIN = Disb.SIN AND Rcpts.[Receipt.Year] = Disb.YEAR FULL OUTER JOIN
(SELECT strSIN, SUM(decAMT) AS ADJUSTMENT
FROM tblAdjustments
WHERE (Date <= '10/31/2007')
GROUP BY strSIN) AS Adjst ON Adjst.strSIN IN (Disb.SIN, Rcpts.SIN)

Works but it is finding receipt/adjustment records where there is no disbursements (Which is great!!!!) but in that case I need to fill in the SIN number from whatever record it is found in (tblAdjustment.strSIN or Receipt.SIN). How do I manage to do that?

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnnyBCJ

ASKER

Thank you all for helping me. I really appreciate the help! The final code snippet is from below.

    Dim da As New SqlDataAdapter(" SELECT     SIN, SUM(COALESCE (EXP_REF, 0) + COALESCE (PayBack, 0) - COALESCE (REVENUE, 0) - COALESCE ([CLIENT.REC], 0) - COALESCE ([CLIENT.SHORT],     " & _
                    "                      0) - COALESCE ([SPOUSE.SHORT], 0) + COALESCE (ADJUSTMENT, 0)) AS BF, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC,                   " & _
                    "                      SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM([CLIENT.REC2]) AS CL, SUM([CLIENT.SHORT2]) AS CS, SUM([SPOUSE.SHORT2]) AS SS,                      " & _
                    "                      SUM(ADJUSTMENT2) AS ADJ2, SUM([CLIENT.REC2]) + SUM([CLIENT.SHORT2]) + SUM([SPOUSE.SHORT2]) + SUM(ADJUSTMENT2 * - 1) AS CSCOL,                        " & _
                    "                      SUM(COALESCE (EXP_REF, 0)) + SUM(COALESCE (PayBack, 0)) - SUM(COALESCE (REVENUE, 0)) - SUM(COALESCE ([CLIENT.REC], 0))                               " & _
                    "                      - SUM(COALESCE ([CLIENT.SHORT], 0)) - SUM(COALESCE ([SPOUSE.SHORT], 0)) + SUM(COALESCE (ADJUSTMENT, 0)) + SUM(COALESCE (CSCH1, 0))                   " & _
                    "                      + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ([CLIENT.REC2], 0)) - SUM(COALESCE ([CLIENT.SHORT2], 0))                     " & _
                    "                      - SUM(COALESCE ([SPOUSE.SHORT2], 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR " & _
                    "                       FROM         (SELECT     SIN, YEAR, SUM(COALESCE (C_SCH1_REFUND, 0)) AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC],       " & _
                    "                                              0 AS [CLIENT.SHORT], 0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, SUM(CASE WHEN [Adinfo] = 'P' THEN COALESCE (CKAMT, 0)             " & _
                    "                                              ELSE 0 END) AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2],             " & _
                    "                                              0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                 " & _
                    "                       FROM          Disbursements                                                                                                                         " & _
                    "                       WHERE      (CKDATE <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                 " & _
                    "                       GROUP BY SIN, YEAR                                                                                                                                  " & _
                    "                       UNION ALL                                                                                                                                           " & _
                    "                       (SELECT     SIN, YEAR, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                              " & _
                    "                                               0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, SUM(CASE WHEN [Adinfo] = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END)       " & _
                    "                                               AS PBack2, SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2],       " & _
                    "                                               0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                " & _
                    "                        FROM         Disbursements AS Disbursements_1                                                                                                      " & _
                    "                        WHERE     (CKDATE > '" & dateFiscalYearEndMinusOne & "') AND (CKDATE <= '" & dateFiscalYearEnd & "')                                                                                     " & _
                    "                        GROUP BY SIN, YEAR                                                                                                                                 " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     SIN, YEAR, 0 AS EXP_REF, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT,                                    " & _
                    "                                            SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS REVENUE,                         " & _
                    "                                            SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC],                                            " & _
                    "                                            SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT],                                          " & _
                    "                                            SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack,           " & _
                    "                                            0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2],                 " & _
                    "                                            0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                                                                         " & _
                    "                      FROM         Receipts                                                                                                                                " & _
                    "                      WHERE     (DATE_REC <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                 " & _
                    "                      GROUP BY SIN, YEAR                                                                                                                                   " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     SIN, YEAR, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                                " & _
                    "                                            0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2,           " & _
                    "                                            SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, SUM(CASE WHEN [Type] = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0)         " & _
                    "                                            ELSE 0 END) AS CREVREC, SUM(CASE WHEN [Type] = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.REC2],                   " & _
                    "                                            SUM(CASE WHEN [Type] = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT2],                                         " & _
                    "                                            SUM(CASE WHEN [Type] = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                        " & _
                    "                      FROM         Receipts AS Receipts_1                                                                                                                  " & _
                    "                      WHERE     (DATE_REC > '" & dateFiscalYearEndMinusOne & "') AND (DATE_REC <= '" & dateFiscalYearEnd & "')                                                                                   " & _
                    "                      GROUP BY SIN, YEAR                                                                                                                                   " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     strSIN, strYear, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                          " & _
                    "                                            0 AS [SPOUSE.SHORT], SUM(decAMT) AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2,           " & _
                    "                                            0 AS CREVREC, 0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], 0 AS ADJUSTMENT2                                 " & _
                    "                      FROM         tblAdjustments                                                                                                                          " & _
                    "                      WHERE     (Date <= '" & dateFiscalYearEndMinusOne & "')                                                                                                                     " & _
                    "                      GROUP BY strSIN, strYear                                                                                                                             " & _
                    "                      UNION ALL                                                                                                                                            " & _
                    "                      SELECT     strSIN, strYear, 0 AS EXP_REF, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS REVENUE, 0 AS [CLIENT.REC], 0 AS [CLIENT.SHORT],                          " & _
                    "                                            0 AS [SPOUSE.SHORT], 0 AS ADJUSTMENT, 0 AS PayBack, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC,       " & _
                    "                                            0 AS [CLIENT.REC2], 0 AS [CLIENT.SHORT2], 0 AS [SPOUSE.SHORT2], SUM(decAMT) AS ADJUSTMENT2                                     " & _
                    "                      FROM         tblAdjustments AS tblAdjustments_1                                                                                                      " & _
                    "                      WHERE     (Date > '" & dateFiscalYearEndMinusOne & "') AND (Date <= '" & dateFiscalYearEnd & "')                                                                                           " & _
                    "                      GROUP BY strSIN, strYear)) AS HI                                                                                                                     " & _
                    "                      GROUP BY SIN                                                                                                                                         " & _
                    "                      ORDER BY SIN     ", myConnection)

Open in new window

JohnnyBCJ

ASKER

The issue with full outer or right join is that there is not necessarily a record in every table and I cannot base it on any 1 table. That is why I went with the union and group by to get rid of the duplicates. Thanks again. Enjoy the points!

JohnnyBCJ

ASKER

I really appreciate the help. I cannot imagine being able to get as far as I am (as quickly as I am) without the help of people like you! Enjoy the points

Mark Wills

With comments like that and a good healthy approach with good dialogue and feedback, it is an absolute joy and pleasure to work with you, look forward to doing it again...