Union Query vs Join Types

[EXlhell]

Hi All:

I have three queries that independently return the values I want. Please see below. However, when I try to query all the fields at once, I get undesirable results(Of course I'm using Access's built in QBE interface). Query one sums on values between criteria from my TOTAL table (not sure that a sum IIF expression is the best way to do this). Query two counts and sums records from my UNIT table and then groups by property. Query three simply returns attributes from my MortgageAP table which I would like to join by property along with the other tables, but not all properties have a mortgage so I'm thinking it should be a LEFT OUTER JOIN. The other problem I run into is that my expression values get aggregated when I join with the other tables. I want to create one query that has a single record row for each property with multiple fields and hard code it in the VBE as my strSQL string variable. I'm getting lost in the syntax and was wondering if I should create two/three different select statements and union them, or adjust my join types, or a combination of both? I would really appreciate anyone's help.

Best,

Query 1:
"SELECT dbo_ACCT1_RS.SCODE, dbo_ACCT1_RS.SDESC, dbo_TOTAL_RS.HPPTY, dbo_ATTRIBUTES_RS.SPROPNAME, " & _
"dbo_TOTAL_RS.UMONTH, dbo_TOTAL_RS.SMTD, dbo_TOTAL_RS.IBOOK, dbo_ATTRIBUTES_RS.SCODE, dbo_TOTAL_RS.SBEGIN, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '30000000' And '31999999',[SBEGIN]+[SMTD],0)) AS INV_CAP, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '25000000' And '25002000',[SBEGIN]+[SMTD],0)) AS LOAN_BAL, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '11000000' And '11200000',[SBEGIN]+[SMTD],0)) AS REHAB_B_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '11400000' And '11800000',[SBEGIN]+[SMTD],0)) AS TAX_B_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE Between '10201100' And '10211200',[SBEGIN]+[SMTD],0)) AS REHAB_P_RES, " & _
"SUM(IIf(dbo_ACCT1_RS.SCODE = '10201150',[SBEGIN]+[SMTD],0)) AS TAX_P_RES "


"FROM dbo_ATTRIBUTES_RS INNER JOIN (dbo_ACCT1_RS INNER JOIN dbo_TOTAL_RS ON dbo_ACCT1_RS.HMY = dbo_TOTAL_RS.HACCT) " & _
"ON (dbo_ATTRIBUTES_RS.HMY = dbo_TOTAL_RS.HPPTY)"

"GROUP BY dbo_ACCT1_RS.SCODE, dbo_ACCT1_RS.SDESC, dbo_TOTAL_RS.HPPTY, dbo_ATTRIBUTES_RS.SPROPNAME, dbo_TOTAL_RS.UMONTH, " & _
"dbo_TOTAL_RS.SMTD, dbo_TOTAL_RS.IBOOK, dbo_ATTRIBUTES_RS.SCODE, dbo_TOTAL_RS.SBEGIN, [SBEGIN]+[SMTD] "

Query 2:
"SELECT Funds.F_Name, Investments.SOI_Name, Count(dbo_UNIT.SCODE) AS CountOfSCODE, dbo_PROPERTY_RS.SCODE, Sum(dbo_UNIT.DSQFT) AS SumOfDSQFT "

"FROM ((dbo_PROPERTY_RS INNER JOIN dbo_UNIT ON dbo_PROPERTY_RS.HMY = dbo_UNIT.HPROPERTY) INNER JOIN Investments ON " & _
"dbo_PROPERTY_RS.SCODE = Investments.Asset_ID) INNER JOIN Funds ON Investments.Fund = Funds.[F_Name]"

"GROUP BY Funds.F_Name, Investments.SOI_Name, dbo_PROPERTY_RS.SCODE;"


Query 3
"SELECT Funds.F_Name, dbo_ATTRIBUTES_RS.SPROPNAME, Investments.SOI_Name, dbo_ATTRIBUTES_RS.SUBGROUP1, " & _
"Investments.SOI_Type, dbo_PROPERTY_RS.SCITY, dbo_PROPERTY_RS.SSTATE, dbo_PROPERTY_RS.SACQUIRE, " & _
"dbo_MortgageAP.cOrigBal, dbo_MortgageAP.sUserDefined1, dbo_MortgageAP.sDesc, dbo_MortgageAP.sUserDefined2, " & _
"dbo_MortgageAP.sUserDefined3, dbo_MortgageAP.sUserDefined4, dbo_MortgageAP.sUserDefined5, dbo_MortgageAP.sUserDefined6, " & _
"dbo_MortgageAP.dtBegin, dbo_MortgageAP.dtMaturity, dbo_MortgageAP.sUserDefined7, dbo_ATTRIBUTES_RS.SCODE, " & _
"dbo_ATTRIBUTES_RS.HPROP, dbo_PROPERTY_RS.HMY, dbo_MortgageAP.hProp, dbo_UNIT.HPROPERTY, dbo_MortgageAP.hMy "

"FROM ((((dbo_PROPERTY_RS INNER JOIN dbo_ATTRIBUTES_RS ON dbo_PROPERTY_RS.HMY = dbo_ATTRIBUTES_RS.HPROP) INNER JOIN " & _
"dbo_MortgageAP ON dbo_PROPERTY_RS.HMY = dbo_MortgageAP.hProp) INNER JOIN dbo_UNIT ON dbo_PROPERTY_RS.HMY = dbo_UNIT.HPROPERTY) " & _
"INNER JOIN Investments ON dbo_PROPERTY_RS.SCODE = Investments.Asset_ID) INNER JOIN Funds ON Investments.Fund = Funds.[F_Name] "

"GROUP BY Funds.F_Name, dbo_ATTRIBUTES_RS.SPROPNAME, Investments.SOI_Name, dbo_ATTRIBUTES_RS.SUBGROUP1, " & _
"Investments.SOI_Type, dbo_PROPERTY_RS.SCITY, dbo_PROPERTY_RS.SSTATE, dbo_PROPERTY_RS.SACQUIRE, " & _
"dbo_MortgageAP.cOrigBal, dbo_MortgageAP.sUserDefined1, dbo_MortgageAP.sDesc, dbo_MortgageAP.sUserDefined2, " & _
"dbo_MortgageAP.sUserDefined3, dbo_MortgageAP.sUserDefined4, dbo_MortgageAP.sUserDefined5, dbo_MortgageAP.sUserDefined6, " & _
"dbo_MortgageAP.dtBegin, dbo_MortgageAP.dtMaturity, dbo_MortgageAP.sUserDefined7, dbo_ATTRIBUTES_RS.SCODE, " & _
"dbo_ATTRIBUTES_RS.HPROP, dbo_PROPERTY_RS.HMY, dbo_MortgageAP.hProp, dbo_UNIT.HPROPERTY, dbo_MortgageAP.hMy "

"ORDER BY dbo_ATTRIBUTES_RS.SPROPNAME;"

 

[lameid]

If you want one record, you want to join not union. A join matches records and displays the result and a union stacks records in the same layout (same number of columns and columns of the same datatype) on top of each other.

Since you are summarizing several different things, you probably should do your grouping in sub queries to get to common scope of each Select and then join those together.

You are right you will need an outer join (i.e. Left Outer Join) to get records that only exist on one side... The important thing is to know whether or not hat is true.

Since you are using T-SQL (SQL Server), there is something called a FULL OUTER JOIN if records don't necessarily exist in one query... Usually that is coupled with using the COALESCE or ISNULL function for columns you join on...

So if you had these queries...

Select ID, Sum(X)
From ABC
Group By ID

Select ID, SEQ, Sum(Y) as Y 
From DEF
Group By ID, SEQ

You might join them like this...

Select A.*, B.SEQ, B.Y
From

     (Select ID, Sum(X)
     From ABC
     Group By ID
     ) A --Alias subquery to A
Left Join
     (
     Select ID, SEQ, Sum(Y) as Y 
     From DEF
     Group By ID, SEQ
     ) B 
ON A.ID = B.ID;

Note that indents are just there for readability.