Solved

# Access Union CrossTab Query Displaying Zeroes FIrst Instead of Values

Posted on 2007-08-08

Hello Everyone,

For the past several months I have been developing an access database for the company I work for. The purpose of the database is to hold clients investment information as well as distributions paid from each investment. The distributions are based on 1 unit. So if John Jones bought 20 units of ABC L.P. and in July of '07 ABC L.P. distributed $20 / unit John Jones received $400. Everything is working with the exception of one report. It is a crosstab report whos record source is a crosstab query. The crosstab query presents the information with the months as a column and years as a row, grouped by the investment name. The value is the distribution amounts for each month of each year. The SQL for the CrossTab Query is as follows:

TRANSFORM Sum(qryUnionQUERYwithDistriDate.TotalYearDistri) AS SumOfTotalYearDistri

SELECT qryUnionQUERYwithDistriDate.RegistrationID, qryUnionQUERYwithDistriDate.RegistrationName, qryUnionQUERYwithDistriDate.LPName, qryUnionQUERYwithDistriDate.InceptionDate, qryUnionQUERYwithDistriDate.LPCatagory, qryUnionQUERYwithDistriDate.TaxBenefit, qryUnionQUERYwithDistriDate.TaxCredit, qryUnionQUERYwithDistriDate.TotInvested, qryUnionQUERYwithDistriDate.Year, qryUnionQUERYwithDistriDate.TotalDeduction, Sum(qryUnionQUERYwithDistriDate.TotalCapital) AS SumOfTotalCapital, Sum(qryUnionQUERYwithDistriDate.TotalYearDistri) AS [Total Of TotalYearDistri]

FROM qryUnionQUERYwithDistriDate

GROUP BY qryUnionQUERYwithDistriDate.RegistrationID, qryUnionQUERYwithDistriDate.RegistrationName, qryUnionQUERYwithDistriDate.LPName, qryUnionQUERYwithDistriDate.InceptionDate, qryUnionQUERYwithDistriDate.LPCatagory, qryUnionQUERYwithDistriDate.TaxBenefit, qryUnionQUERYwithDistriDate.TaxCredit, qryUnionQUERYwithDistriDate.TotInvested, qryUnionQUERYwithDistriDate.Year, qryUnionQUERYwithDistriDate.TotalDeduction

ORDER BY qryUnionQUERYwithDistriDate.LPName, qryUnionQUERYwithDistriDate.Year, qryUnionQUERYwithDistriDate.TotalDeduction

PIVOT qryUnionQUERYwithDistriDate.Month In (1,2,3,4,5,6,7,8,9,10,11,12);

The query that this crosstab is getting its information from is a union query. A union query was used because besides needing to gather the distribution data, tax deduction data was also needed. So another real life example. John Jones invested in 20 units of ABC L.P. ABC L.P. distributes at a varying rate quarterly BUT it also has a tax benefit for the first two years of investment. The tax benefit is worth 100% of the investment amount the first year and 80% of the investment amount the second year. The actual tax benefit amount received is acqueried with this formula: (([tax benefit percentage] x ([units purchased] x [unit price])) x [tax bracket]))

So, if John Jones invested in 20 units at $1,000 / unit and he is in the 35% tax percentile then for the first year his tax benefit would be : $7,000

For the second year it would be : $5,600

The problem that I am having is that if there is only one year's worth of tax benefit the crosstab will show that information before showing the distributions BUT if there are multiple years of tax benefit it will show the first years tax benefit but, then 0's for all of the remaining years. I believe that I may have a problem with my joins, but I've tried configuring them in several different ways for over 4 months now and I cannot find a way that works.

The union queries SQL is as follows:

SELECT tblPortfolio.PortfolioName, RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Distributions.Day, Distributions.Month, Distributions.Year, Sum(Distributions.DistributionAmt * Investments.LPUnitsOwned) AS TotalYearDistri, Sum((([Distributions]![CapitalReturn]*[Distributions]![DistributionAmt])/100)*[Investments]![LPUnitsOwned]) AS TotalCapital, 0 AS TotalDeduction, LPID.TaxBenefit, LPID.TaxCredit

FROM (tblPortfolio INNER JOIN tblRegistration ON tblPortfolio.PortfolioID = tblRegistration.PortfolioName)INNER JOIN (((LPCatagories INNER JOIN LPID ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) LEFT JOIN Distributions ON LPID.LPID = Distributions.LPName) INNER JOIN Investments ON LPID.LPID = Investments.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName

WHERE((((tblRegistration.RegistrationID)=[Forms]![Report Selector]![ListBox])) AND Distributions.Year IS NOT NULL AND LPID.Closed = No)

GROUP BY tblPortfolio.PortfolioName, RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, Month, Distributions.Year, LPID.TaxBenefit, LPID.TaxCredit, Day

UNION SELECT tblPortfolio.PortfolioName, tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, 31 AS [Day], 12 AS [Month], tblDeductions.Year, 0 AS TotalYearDistri, 0 AS TotalCapital, (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction, LPID.TaxBenefit, LPID.TaxCredit

FROM (tblPortfolio INNER JOIN tblRegistration ON tblPortfolio.PortfolioID = tblRegistration.PortfolioName) INNER JOIN ((((LPCatagories INNER JOIN LPID ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) LEFT JOIN Distributions ON LPID.LPID = Distributions.LPName) INNER JOIN Investments ON LPID.LPID = Investments.LPName) LEFT JOIN (tblDeductions LEFT JOIN tblTaxBracket ON tblDeductions.Year = tblTaxBracket.Year) ON LPID.LPID = tblDeductions.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName

WHERE((((tblRegistration.RegistrationID)=[Forms]![Report Selector]![ListBox])) AND tblDeductions.Year IS NOT NULL AND LPID.Closed = No)

GROUP BY tblPortfolio.PortfolioName, tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, tblDeductions.Year, tblDeductions.Deduction, [tblTaxBracket].[TaxBracket], LPID.TaxBenefit, LPID.TaxCredit, Day;

Hopefully I have provided an adequate amount of information on the background of my business and what the database is needing to accomplish as well as having provided the appropriate information that is needing to help me solve this problem. If any more information is required or a further explanation of what the database is doing or how my business operates please let me know and I will try and clarify.

Thank you.