[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Union CrossTab Query Displaying Zeroes FIrst Instead of Values

Posted on 2007-08-08
25
Medium Priority
?
283 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:kabradley
  • 13
  • 12
25 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 19658040
In the UNION query, what table does RegistrationID belong to?  All your queries would be far more readable if you used a table alias and organzed them in a text editor like this:

TRANSFORM Sum(a.TotalYearDistri) AS SumOfTotalYearDistri
SELECT
a.RegistrationID,
a.RegistrationName,
a.LPName,
a.InceptionDate,
a.LPCatagory,
a.TaxBenefit,
a.TaxCredit,
a.TotInvested,
a.Year,
a.TotalDeduction,
Sum(a.TotalCapital) AS SumOfTotalCapital,
Sum(a.TotalYearDistri) AS [Total Of TotalYearDistri]
FROM qryUnionQUERYwithDistriDate AS a
GROUP BY
a.RegistrationID,
a.RegistrationName,
a.LPName,
a.InceptionDate,
a.LPCatagory,
a.TaxBenefit,
a.TaxCredit,
a.TotInvested,
a.Year,
a.TotalDeduction
ORDER BY a.LPName, aYear, a.TotalDeduction
PIVOT a.Month In (1,2,3,4,5,6,7,8,9,10,11,12);

Is the field Year the year of InceptionDate?  If so, then it should not be in a separate field but called using the function Year(a.InceptionDate).  As Year is a Reserved Word, it should be changed to something like IncepYear.  I see it exists in the Union tables as Year.  Same goes for Month.  The aliases would really help in the UNION query using a, b, c, d, and e.  I don't believe the In() grouping is needed in the PIVOT as the month numbers will automatically be  sorted ascending.
0
 

Author Comment

by:kabradley
ID: 19700984
Sorry for the delayed reply, I had a business trip to Colorado that I hadnt anticipated.
Hope this helps the readability a bit. Thanks for the suggestion GrayL, I knew some liked to see the queries in their raw, unaltered form, but for everyones convenience and ease of reading I've edited them to where *hopefully* they're easier to follow.
A = qryUnionQuerywithDistriDate
qryUnionQuerywithDistriDate is a query that queries all of the union query fields and concatenates distributions.month, distributions.day, and distributions.year. It then compares this date against the inception date. The union query that it is querying is the code that is provided. A.Month, and a.Year are the month and year of the distribution, not the inception date. The reason for the In() statement is because there is not always a distribution in a particular month, but that month still needs to be present in the query results.

TRANSFORM Sum(a.TotalYearDistri) AS SumOfTotalYearDistri
SELECT a.RegistrationID,
a.RegistrationName,
a.LPName,
a.InceptionDate,
a.LPCatagory,
a.TaxBenefit,
a.TaxCredit,
a.TotInvested,
a.Year,
a.TotalDeduction,
Sum(a.TotalCapital) AS SumOfTotalCapital, Sum(a.TotalYearDistri) AS [Total Of TotalYearDistri]
FROM a
GROUP BY a.RegistrationID,
a.RegistrationName,
a.LPName,
a.InceptionDate,
a.LPCatagory,
a.TaxBenefit,
a.TaxCredit,
a.TotInvested,
aYear,
aTotalDeduction
ORDER BY a.PName,
a.Year,
a.TotalDeduction
PIVOT a.Month In (1,2,3,4,5,6,7,8,9,10,11,12);


The union query is obviously far more difficult to explain but, Ill do my best at explaining the relationships between each table and field.
A = tblPortfolio
tblPortfolio has the fields portfolioID and PortfolioName. Each registration can only belong to one portfolio, but a portfolio can have multiple registrations.
B = tblRegistration
      tblRegistration has the information about each registration; RegistrationID, RegistrationName, account type, and custodian. Each investment is related to a particular RegistrationID. A registration can have multiple investments, but an investment can only have one registration.

C = Investments
Investments has the information about each particular investments. Investments.LPName is the funds ID from LPID.LPID. Each LPID can have multiple investments related to it, but an investment can only have one LPID.

D = LPID
      LPID has the information about each particular fund. A LPID.LPID is related to LPCatagories.LPCatagory. A LPCatagory can have multiple LPIDs but, a LPID can only have one LPCatagory. A LPID also has multiple Distributions.DistributionAmt because a fund distributes multiple times, but a DistributionAmt can only have one LPID. LPID.TaxBenefit and LPID.TaxCredit are Yes/No check boxes that are used later on in the report, they are not used for any logical functions in the query.

E = LPCatagories
LPCatagories stores the different categories of funds such as power, mutual funds, stocks, bonds, oil&gas, etc. The LPID.LPCatagory is related to the LPCatagories.LPCatagoryID not LPCatagories.LPCatagory.

F = Distributions
This table has all the distribution values, dates, and capital returned for each fund. A distribution can only have one fund. The date has three separate fields, month, day, year. This was done because when I queried the information in the crosstab I could not separate the year and the month for the column and row values. If there is a way to correct this to where the distribution date can be in a traditional date format Im all ears.

G = tblDeductions
This table has the tax benefit for each year for each fund (if the fund had a tax benefit). It is related to LPID.LPID as the LPID can have multiple tax benefits, but a tax benefit can only have one LPID.

H = tblTaxBracket
Holds tax bracket and year for registration names. Registration Names can have multiple tax brackets (because networths change from year to year) but, a tax bracket can only have one registration name.

TotalYearDistri is the sum of all the distributions for each fund times the number of units a particular registration owns.

TotalCapital is the sum of the amount of capital returned from each distribution. Some distributions have only operational income (straight cashflow) others include a percentage of the principle originally invested. The equation takes that percentage (entered as a whole number not a decimal) multiplies it by the distribution amount, divides by 100 (to adjust for the whole number percentage) and then multiples all of that by the number of units owned (since each distribution is on a one unit basis).

TotalDeduction is the sum of the amount of tax deduction the registration received on a particular investment. Tax deductions will occur in many different conditions. They will occur only once the first year of the investment before any distributions, once only the first year of the investment WHILE distributions are coming, every year for a certain period of time sometimes with distributions some year without distributions, and sometimes many years after the inception of the fund and after distributions have started coming.

The where statement does the following; the user selects a name from a listbox on the report form this registrationID is then passed to where RegistrationID = , the F.Year and G.Year makes sure that it doesnt query and return information thats is null and lastly it doesnt query programs that are closed (have stopped distributing).

SELECT A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
[D.[LPUnitPrice]*C.[LPUnitsOwned] AS TotInvested,
F.Day,
F.Month,
F.Year,
Sum(F.DistributionAmt * C.LPUnitsOwned) AS TotalYearDistri, Sum(((F.[CapitalReturn]*F.[DistributionAmt])/100)*[C.[LPUnitsOwned]) AS TotalCapital,
0 AS TotalDeduction,
D.TaxBenefit,
D.TaxCredit
FROM (A INNER JOIN tblRegistration ON A.PortfolioID = B.PortfolioName)
INNER JOIN (((LPCatagories INNER JOIN LPID ON E.LPCatagoryID = D.LPCatagoryID)
LEFT JOIN F ON D.LPID = F.LPName)
INNER JOIN C ON D.LPID = C.LPName) ON B.RegistrationID = C.RegistrationName
WHERE((((B.RegistrationID)=[Forms]![Report Selector]![ListBox]))
AND
F.Year IS NOT NULL
AND
D.Closed = No)
GROUP BY A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
F.Month,
F.Year,
D.TaxBenefit,
D.TaxCredit,
F.Day
UNION SELECT A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
[D.[LPUnitPrice]*C.[LPUnitsOwned] AS TotInvested,
31 AS [Day],
12 AS [Month],
F.Year,
0 AS TotalYearDistri,
0 AS TotalCapital, ((((D.[LPUnitPrice])*C.[LPUnitsOwned]))*((G.[Deduction])/100))*((H.[TaxBracket])/100)) AS TotalDeduction,
D.TaxBenefit,
D.TaxCredit
FROM (A INNER JOIN B ON A.PortfolioID = B.PortfolioName)
INNER JOIN ((((E INNER JOIN D ON E.LPCatagoryID = D.LPCatagoryID)
LEFT JOIN F ON D.LPID = F.LPName)
INNER JOIN C ON D.LPID = C.LPName)
LEFT JOIN (G LEFT JOIN H ON G.Year = H.Year) ON D.LPID = G.LPName)
ON B.RegistrationID = C.RegistrationName
WHERE((((B.RegistrationID)=[Forms]![Report Selector]![ListBox]))
AND
G.Year IS NOT NULL
AND
D.Closed = No)
GROUP BY A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
G.Year,
G.Deduction,
H.[TaxBracket],
D.TaxBenefit,
D.TaxCredit,
F.Day;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19702757
You alias format does not appear to be correct.  It should be:

FROM LongTAbleName AS a INNER JOIN DifferentTableName AS b ON a.fldJoin = b.fldJoin

unless, of course you have tables named A, B, C, D, E, etc.??
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kabradley
ID: 19703279
Ok, I edited it again using that format.

A = tblPortfolio
B = tblRegistration
C = Investments
D = LPID
E = LPCatagories
F = Distributions
G = tblDeductions
H = tblTaxBracket

SELECT A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
[D.[LPUnitPrice]*C.[LPUnitsOwned] AS TotInvested,
F.Day,
F.Month,
F.Year,
Sum(F.DistributionAmt * C.LPUnitsOwned) AS TotalYearDistri, Sum(((F.[CapitalReturn]*F.[DistributionAmt])/100)*[C.[LPUnitsOwned]) AS TotalCapital,
0 AS TotalDeduction,
D.TaxBenefit,
D.TaxCredit
FROM (tblPortfolio INNER JOIN tblRegistration ON A.PortfolioID = B.PortfolioName)
INNER JOIN (((LPCatagories INNER JOIN LPID ON E.LPCatagoryID = D.LPCatagoryID)
LEFT JOIN Distributions ON D.LPID = F.LPName)
INNER JOIN Investments ON D.LPID = C.LPName) ON B.RegistrationID = C.RegistrationName
WHERE((((B.RegistrationID)=[Forms]![Report Selector]![ListBox]))
AND
F.Year IS NOT NULL
AND
D.Closed = No)
GROUP BY A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
F.Month,
F.Year,
D.TaxBenefit,
D.TaxCredit,
F.Day
UNION SELECT A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
[D.[LPUnitPrice]*C.[LPUnitsOwned] AS TotInvested,
31 AS [Day],
12 AS [Month],
F.Year,
0 AS TotalYearDistri,
0 AS TotalCapital, ((((D.[LPUnitPrice])*C.[LPUnitsOwned]))*((G.[Deduction])/100))*((H.[TaxBracket])/100)) AS TotalDeduction,
D.TaxBenefit,
D.TaxCredit
FROM (tblPortfolio INNER JOIN tblRegistration ON A.PortfolioID = B.PortfolioName)
INNER JOIN ((((LPCatagories INNER JOIN LPID ON E.LPCatagoryID = D.LPCatagoryID)
LEFT JOIN Distributions ON D.LPID = F.LPName)
INNER JOIN Investments ON D.LPID = C.LPName)
LEFT JOIN (tblDeductions LEFT JOIN tblTaxBracket ON G.Year = H.Year) ON D.LPID = G.LPName)
ON B.RegistrationID = C.RegistrationName
WHERE((((B.RegistrationID)=[Forms]![Report Selector]![ListBox]))
AND
G.Year IS NOT NULL
AND
D.Closed = No)
GROUP BY A.PortfolioName,
B.RegistrationID,
B.RegistrationName,
C.InvestmentID,
C.LPUnitsOwned,
C.InceptionDate,
D.VendorFamily,
D.LPName,
D.LPUnitPrice,
E.LPCatagory,
G.Year,
G.Deduction,
H.[TaxBracket],
D.TaxBenefit,
D.TaxCredit,
F.Day;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19703373
The first two lines of your FROM statement have to look like this.

FROM (tblPortfolio AS a INNER JOIN tblRegistration AS b ON a.PortfolioID = b.PortfolioName)
INNER JOIN (((LPCatagories AS e INNER JOIN LPID AS d ON e.LPCatagoryID = d.LPCatagoryID)

After that is seems to fall apart.  Press Alt+f11 to get to the VB Editor, click Help, Answer Wizard and type Inner Join Operation to see how the joins should be created.  Then type Left Join or Right Join to see the limitations of outer joins.
0
 

Author Comment

by:kabradley
ID: 19703602
What exactly do you mean by "fall apart"? Do you mean that the query would not even be capable of running or that the format is incorrect? I'll pull up the vb editor and check that out, but I wanted further clarification as to how serious of the problem it is.Thanks
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19703812
In the question SQL for the UNION for each SELECT statement, the very last object is Day, with no table qualifier.  I see you have corrected that in subsequent posts.  In a UNION query, the first SELECT statement establishes the order and the datatype of each field.  In every SELECT statement in the query following the UNION clause, the field names have no effect, and the datatypes must conform to that established in the first SELECT.  This is becoming very complex without any data.  Anyway you can pare the db down to the min required to support the question, zip it, and upload to:

    http://www.ee-stuff.com/login.php

0
 

Author Comment

by:kabradley
ID: 19703870
GRayL, I definitely don't have a problem with zipping it and sending you the db. I knew going into it that this problem would be extremely hard for me to define/explain and probably even harder for someone to understand (even with a perfect explanation and precept). I'll zip it and upload it immediately.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19703907
Just remember to copy the URL of this thread before you go there, log in, paste the URL, browse for the zip file, add a comment (imperative) and upload.  Then copy the URL of the uploaded file and paste it in post back here.
0
 

Author Comment

by:kabradley
ID: 19704002
Alright, just uploaded here's the direct link:
https://filedb.experts-exchange.com/incoming/ee-stuff/4347-PMExample.zip
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19712967
Been at this one all day, and have not found anything definitive.  I noticed no Detail in the report, you have everything in Headers.  Could that be the problem?  Also make life easier for yourself and create one date field from the Year, Month and Date fields in Distributions.  Calling the query qryUnionCrosstabFinal when it does not involve a cross tab sent me on a wild goose chase.  I suggest the following for your query names:

qunDistribution
qryDistributionWithDate
qxtDistibutionWithDate

this way you can 'see' the sequence in the names.  BTW given you had a field DistDate in the Distributions table you could easily add the criteria of qryDistibutionwithDate to qxtDistributionWithDate and eliminate the former.

In qryUnionCrosstabFinal you name the calculated field - TotalYearDistri - when in fact it is MonthDistAmt which in the cross tab becomes Total of TotalYearDistri.  I tell you, when you try to troubleshoot something you've never seen before, names really become important.

It's time to shut this thing down.  
0
 

Author Comment

by:kabradley
ID: 19717626
Thanks for looking at this GRayL.

I made the appropriate changes to the query names. I tell you, with some of these queries and fields I try so hard to follow naming conventions, but usually end of failing because the name is either to long or so short it doesn't fully explain what's is happening. for instance, the qryUnionCrosstabFinal was at one time 1 of about 10 union queries I was working on, trying different methods of pulling all the data I needed. When I finally got it to work, I tagged 'final' to the end to let myself know this is the working one. Why didn't I delete all the non-working queries? I have no idea, OCD kicked in I presume. Anywho thank you for the naming help, I've implemented it and will try harder to choose more appropriate names in the future.

On the distribution date, is there a way to automatically concatenate the 3 date fields (month, day, year) into one date. The reason I ask is the full version of the database has 6,500+ distributions, so that would be 6,500+ dates I would have to change. If there was a way of concatenating them programatically I would greatly appreciate knowing about it.

As to your concern about the report. I do not think that the problem is report based. (although it maybe fixed via code on the report). It seems to me that the root problem is the queries themselves. Right now when you query you can see how the deductions and distributions are on separate lines and whichever line appears first is the line that shows first on the report. I believe that's the core problem. If both deductions and distributions were in the same row in the crosstab query then there would be no problem displaying that line in the report.

Hope to hear from you soon (both about the report and the dates)

Thank You

P.s.6+ months of working on this one problem, Heck yeah it's time for it to be shut down!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19718113
OK, solved it.  I had to change the crosstab query to Sum(TotalDeduction), and revise the report accordingly.  The revised query has my name added and the report is just rptRay.  You owe me a beer!

Add the field DistDate:

UPDATE Distributions SET DistDate = DateSerial([Year],[Month],[Day]);
0
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 19718131
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19718172
Another BTW.  As you did not include the form with the mdb, when you are prompted, just enter 78
0
 

Author Comment

by:kabradley
ID: 19718316
w00t, Thanks for working on this so diligently GrayL. One small problem, the link you provided was for the incorrect version. Can you repost the link to the revised mdb?

Thanks!

P.s. I'm going to go try that update query for the dates, I hope it works!
0
 

Author Comment

by:kabradley
ID: 19718334
Just ran the update query, worked like a charm!!

I was definitely not looking forward to having to manually enter all of that info.

Thank you!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19718342
That's the correct URL,  when you get to your first question, make sure you click 'view all files' on the second line.
0
 

Author Comment

by:kabradley
ID: 19718360
I'm an idiot
I'm checking it out now, thank you.
0
 

Author Comment

by:kabradley
ID: 19718474
Ray,
I literally cannot believe. I am in quite a bit of shock right now! Thank you so much for finding the solution, I must say your "rank" is spot on! This is just....Yeah I'm at quite a loss of words.

Only two questions more questions,
One:
0
 

Author Comment

by:kabradley
ID: 19718486
One:
  Now that I'm using a single date field (instead of three) how will this alter the crosstab? I'm sure I'll need to change something to get the columns and rows correct.

Two:
   Why did summing the deductions work? What is that doing programatically that makes it to where it reads correctly?

Three:
  Does the USPS allow beer to be sent through the mail? :D
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19718548
1.Where you want to see Year use the function Year(DistDate)

Where you want to see the Month use Month(DistDate) - as in

PIVOT Month(Distributions.DistDate) IN (1,2,3,4,5,6,7,8,9,10,11,12)

2.  The original was creating a record which had a 0 value for deductions.  You remember there were 12 records, now there are 9, removing the zero value records, without damaging anything.  Although the sum() function is summing only one value, it allowed me to remove the field from the Group By and the Order By.

3.  Damn!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19718571
sorry:

PIVOT Month(Query.DistDate) IN (1,2,3,4,5,6,7,8,9,10,11,12)

replace Query with whatever you wind up naming the cross tab query.
0
 

Author Comment

by:kabradley
ID: 19719923
Ray,
All of that worked great! Thank you very much for your help!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19719956
Thanks, glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question