Making Fields Invisible in Reports / Adding data to Cross tab Report

[kabradley]

Alrighty Guys and Gals, I have another question that I hope you all can help me with.

I have a report that uses a cross-tab query as its record source. This cross-tab query is getting all of its information from another query. That query, qryRpt, is pulling information from several different tables. One of these table is tblDistributions, a table that holds monthly distributions for a particular investment, and the second is tblDeduction, a table that holds a decimal value that is multiplied by the amount invested to return the amount of tax deduction that the investor will receive. These are not the only 2 tables, but they're the ones that are giving me problems.

I have an inner join between the tblDeductions and tblDistributions so that all the distribution records will be shown even if no deductions are present, because not all investments have a tax benefit.

The two problems I would like to solve are as follows:

1. On the report, it has a text box, txtDeduction, and label, lblDeduction, for the tax deductions. Since not all investments have a tax benefit I would like this column to only be visible when a deduction record is present.
I've tried this in the On Open event of the report

If IsNull(TotalDeduction) Then
 
lblTax.Visible = False
 
End If
 

But, that does not seem to work. If abc investment doesn't even have a row in tblDeduction is it's tax value null? It's not 0, but from the looks of it its not null either. When I run the report with this code it doesn't give me an error it just doesn't change anything. So any help on making that visible/invisible if the appropriate conditions are meet would be great. Also, if the conditions are met and the label and text boxes are made invisible is there a way to move other controls to the right, that way there is not a huge blank space on the report?

The second problem is dealing with the same report and the same tables. Distributions do not necessarily start coming the first month of the investment. At times they may not come for several years, but on certain investments the tax incentives start that year.
For instance, abc investment closes in 2005. The investor gets a tax incentive for 2005, 2006, and 2007. Through these three years no distributions have been paid out. In 2008 the first distribution flows and there are no more tax credits.

Because of this 'unstructured' distribution/deduction time the report does not always show all of the information. I believe the core problem is because I used the inner join. All of the distributions will show, BUT if there is a tax credit a year or more before the first distribution it will not be shown. That presents a serious problem. Also, the tax credit only lasts for that one year, so if the tax credit is for the year 2003 it should only calculate the tax benefit for 2003, not '04 '05 etc.

The questions on this problem are as follows:
1. Is there a way to show the tax deductions even if no distributions are present?
I tried something that worked, but was rather 'brutish'. On the add new investment form I used an append query to insert a new record with the new investments name, a distribution of $0.00, and the year and month that the new investment closed. This would then give the cross-tab query a 'value' so that the tax deduction would be included, but a)it did not seem like a reliable way to do things and b)On the report it showed $0.00 and my boss has specified that all of the fields should be blank until it has data to display.

For instance, if the deduction is 2003 and the first distribution is in 2004 then, there should be 12*twelve months in a year* blank spaces to the left of the 2003 until it gets to the deduction in which it would display the deduction value.

Hopefully I've explained all of this clear enough that you all can help. If further clarification is needed at all please let me know and I'll do my best to explain myself better. Thank you in advance for the help!

[MMcCarthy]

LOL!

I'll get to this when I can.

If I get lost just post a bump on the thread here to remind me.

Mary

[kabradley]

LOL!

I'll get to this when I can.

If I get lost just post a bump on the thread here to remind me.

Mary

Alright will do.
Thanks

[NeoPa]

Allen,
If you create a table which just contains all the months that you want data to show for, you could create a query with this table LEFT JOINING the other query results you already have. This would ensure that each slot is populated, even if the data you show would only be Null values. Does this make sense to you?

[kabradley]

Allen,
If you create a table which just contains all the months that you want data to show for, you could create a query with this table LEFT JOINING the other query results you already have. This would ensure that each slot is populated, even if the data you show would only be Null values. Does this make sense to you?

Hey Adrian,
So let me just double check that I am understanding what you are suggesting. The query that Mary wrote up top, I am to use that in another query. In this second query I am to join the query and a table that has all the months listed. Joining them in such a way that "All records in tblMonth show and only those in the query that are equal show". Is this correct?

Assuming it is, I did that this morning made that join. The data that I got in return was all the distributions(because they have a month associated with them) but, none of the deductions(they don't have a month associated to them). It did get rid of all the erroneous data, but it also got rid of the deductions.

Any other suggestions?

Also, completely switching topics, but still discussing this particular query, I need to compare the investment date to the distribution dates. If the distribution dates are before the investment date, they should not show (because the client wasn't even in the program yet). The problem that I am having is the investment date is a 'date/time' data type in access, but the distribution dates are three (3) seperate fields. Field Month, Day, and Year. I was able to concatenate these three fields in a query as one field, but it was not able to be compared to the investment date. For instance the distribution date is January 3rd, 2007. In the program it will show as 1 3 2007 each piece of information is its field. I was able to get the query to concatenate it as 132007. This though was not acceptable to access to be compared to a investment date of #1/21/2006. I didn't know if I needed to use 'CONVERT' in my query statement or what. Any suggestions for that?
If I should make that question a new thread I will, I just thought since it was dealing with the same query it would be ok to enter here.

Thanks for trying to help Adrian.
-Allen

[NeoPa]

Allen,
For the first part I was suggesting a concept rather than a solution. I'm no good with cross-tab queries and have no wish to get involved in their complexities atm I'm afraid.
From your clear explanation, it appears that you've understood my suggestion precisely. If the other query doesn't lend itself well to this approach then I must stop there.

For the second part, you should look at converting the result of the three fields into a Date/Time field rather than a string. This way it will be perfectly manageable and comparable with the other Date/Time data.

[NeoPa]

CDate([Day] & "/" & [Month] & "/" & [Year]) AS NewDate

for civilised countries or, if in USA then :

CDate([Month] & "/" & [Day] & "/" & [Year]) AS NewDate

J/K - but don't forget the date format is different for different countries.
Good luck.

[MMcCarthy]

Allen

I'll try and have a look at this later today.

Mary

[kabradley]

Adrian,
wow, just wow. I searched the internet all over for that simple answer /shakes head
Thanks for that extremely simple piece of code. In the "criteria" area of the query I entered [newDate]>[inceptionDate] but this did not seem to filter the information correctly. I knew that sometimes using functions with dates can present a problem so I searched the net for an answer, but through my reading I didn't see anything that specified that this type of 'comparing' would not work. Is there a specific function needed? Or am I just all out doing it incorrectly?

Thanks,
Allen

P.S. I read an article just a few days ago about America's rediculous dating convention...just one more thing that we decided to do differently for no apparent reason at all :/ lol

[NeoPa]

I've no idea why your code wouldn't work as expected Allen. Perhaps you could post the SQL you're trying (I'm hoping it's not too voluminous but we'll look anyway).
American Dates.
I have no real point to make about this. I was just pointing out they can be different really. A sensible date format would probably be military or reverse formats rather than any currently in use that I know of.

[kabradley]

I've no idea why your code wouldn't work as expected Allen. Perhaps you could post the SQL you're trying (I'm hoping it's not too voluminous but we'll look anyway).
American Dates.
I have no real point to make about this. I was just pointing out they can be different really. A sensible date format would probably be military or reverse formats rather than any currently in use that I know of.

Well Adrian,
After some more fiddling with it I was able to get it to narrow down selections. I am honestly not sure what I did different compared to this morning, but whatever it was it is working now. Thanks for the CDATE tip. Definitely will have to remember that.
Allen

[NeoPa]

That's funny (coincidental) as I've just had a very similar conversation with Lee123. See last couple of posts in check boxes.

Tell me about it Lee.
I've spent most of this week and last trying things that fail, only to find later on (when I've tried everything else and returned for a rerun out of simple desperation), that they are now working perfectly.
It tends to hold the project up just a little :(
Anyway, I'm glad that worked for you and good luck :)

Glad you liked the CDate() stuff too.

[kabradley]

Hi Mary,

Just wanted to remind you about this thread. I know you have tons going on, but was hoping that you may have time to look at it today.

Just let me know.
Thanks!
Allen

[kabradley]

Bumping to the top.

Hope to hear from you soon, thanks Mary

Allen :)

[MMcCarthy]

Bumping to the top.

Hope to hear from you soon, thanks Mary

Allen :)

Hi Allen

Sorry can you post the sql of the crosstab query you are currently using. The one I have is out of date.

Mary

[kabradley]

Alrighty Mary, the most current SQL statements are as follows

Union Query

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, 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,
(((([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;
 

And The CrossTab Query is:

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

Just to kinda refresh your memory as to what the problem is. When it queries the distributions and deductions, if there is a distribution the same year as a deduction the distribution will be shown first with a deduction of 0, and vice - versa for the next line. When the query is used in a report what happens is you see the first line of data and then it skips to the next record, unless you make the detail section of the report tall enough to show a line of distributions with a 0 deduction and then the next line with 0 distribution and a deduction > 0.

Hope that helps describe the problem.

Thank you again!

Allen

[MMcCarthy]

Thanks Allen

I'll have a look at it tonight

Mary

[NeoPa]

It's lucky Mary's actually a cleverly programmed bot. I would cry if I had to decipher that lot.

Best wishes Allen.

[MMcCarthy]

It's lucky Mary's actually a cleverly programmed bot. I would cry if I had to decipher that lot :(
;) Best wishes Allen.

Hi Allen,

Did you get my email?

Mary

[kabradley]

I know Adrian, that query is massive!

Mary, I didn't get an email, but I pm'ed you with another address to try.

Allen

[MMcCarthy]

I know Adrian, that query is massive!

Mary, I didn't get an email, but I pm'ed you with another address to try.

Allen

OK Allen if you still didn't get my email all I'm looking for is an uptodate copy of the database. You've added new columns which are not in the one I have.