By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,781 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,781 IT Pros & Developers. It's quick & easy.

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

P: 55
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
Expand|Select|Wrap|Line Numbers
  1. If IsNull(TotalDeduction) Then
  2.  
  3. lblTax.Visible = False
  4.  
  5. End If
  6.  
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!
Apr 2 '07
Share this Question
Share on Google+
69 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 8 '07 #51

P: 55
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
Jun 8 '07 #52

NeoPa
Expert Mod 15k+
P: 31,562
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?
Jun 9 '07 #53

P: 55
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
Jun 12 '07 #54

NeoPa
Expert Mod 15k+
P: 31,562
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.
Jun 12 '07 #55

NeoPa
Expert Mod 15k+
P: 31,562
Expand|Select|Wrap|Line Numbers
  1. CDate([Day] & "/" & [Month] & "/" & [Year]) AS NewDate
for civilised countries or, if in USA then :
Expand|Select|Wrap|Line Numbers
  1. CDate([Month] & "/" & [Day] & "/" & [Year]) AS NewDate
J/K - but don't forget the date format is different for different countries.
Good luck.
Jun 12 '07 #56

MMcCarthy
Expert Mod 10K+
P: 14,534
Allen

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

Mary
Jun 12 '07 #57

P: 55
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
Jun 12 '07 #58

NeoPa
Expert Mod 15k+
P: 31,562
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.
Jun 12 '07 #59

P: 55
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
Jun 13 '07 #60

NeoPa
Expert Mod 15k+
P: 31,562
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.
Jun 13 '07 #61

P: 55
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
Jun 15 '07 #62

P: 55
Bumping to the top.

Hope to hear from you soon, thanks Mary

Allen :)
Jun 19 '07 #63

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 19 '07 #64

P: 55
Alrighty Mary, the most current SQL statements are as follows

Union Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (tblPortfolio INNER JOIN tblRegistration 
  3. ON tblPortfolio.PortfolioID = tblRegistration.PortfolioName)
  4. INNER JOIN (((LPCatagories INNER JOIN LPID 
  5. ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) 
  6. LEFT JOIN Distributions ON LPID.LPID = Distributions.LPName)
  7. INNER JOIN Investments ON LPID.LPID = Investments.LPName) 
  8. ON tblRegistration.RegistrationID = Investments.RegistrationName
  9. WHERE (((tblRegistration.RegistrationID)=[Forms]![Report Selector]![ListBox])) 
  10. AND Distributions.Year IS NOT NULL AND LPID.Closed = No
  11. 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
  12. 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,
  13. 31 AS [Day], 12 AS [Month], tblDeductions.Year, 0 AS TotalYearDistri,
  14. (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))
  15. *(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction, LPID.TaxBenefit, LPID.TaxCredit
  16. FROM (tblPortfolio INNER JOIN tblRegistration 
  17. ON tblPortfolio.PortfolioID = tblRegistration.PortfolioName) 
  18. INNER JOIN ((((LPCatagories INNER JOIN LPID 
  19. ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) 
  20. LEFT JOIN Distributions ON LPID.LPID = Distributions.LPName)
  21. INNER JOIN Investments ON LPID.LPID = Investments.LPName)
  22. LEFT JOIN (tblDeductions LEFT JOIN tblTaxBracket 
  23. ON tblDeductions.Year = tblTaxBracket.Year) 
  24. ON LPID.LPID = tblDeductions.LPName) 
  25. ON tblRegistration.RegistrationID = Investments.RegistrationName
  26. WHERE (((tblRegistration.RegistrationID)=[Forms]![Report Selector]![ListBox])) 
  27. AND tblDeductions.Year IS NOT NULL AND LPID.Closed = No
  28. 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;
  29.  
And The CrossTab Query is:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(qryUnionCrossTabFinal.TotalYearDistri) AS SumOfTotalYearDistri
  2. 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]
  3. FROM qryUnionCrossTabFinal
  4. GROUP BY qryUnionCrossTabFinal.RegistrationID, qryUnionCrossTabFinal.RegistrationName, qryUnionCrossTabFinal.LPName, qryUnionCrossTabFinal.InceptionDate, qryUnionCrossTabFinal.LPCatagory, qryUnionCrossTabFinal.TaxBenefit, qryUnionCrossTabFinal.TaxCredit, qryUnionCrossTabFinal.TotInvested, qryUnionCrossTabFinal.Year, qryUnionCrossTabFinal.TotalDeduction
  5. ORDER BY qryUnionCrossTabFinal.LPName, qryUnionCrossTabFinal.Year, qryUnionCrossTabFinal.TotalDeduction
  6. PIVOT qryUnionCrossTabFinal.Month In (1,2,3,4,5,6,7,8,9,10,11,12);
  7.  
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
Jun 20 '07 #65

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks Allen

I'll have a look at it tonight

Mary
Jun 20 '07 #66

NeoPa
Expert Mod 15k+
P: 31,562
It's lucky Mary's actually a cleverly programmed bot. I would cry if I had to decipher that lot.

Best wishes Allen.
Jun 21 '07 #67

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 21 '07 #68

P: 55
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
Jun 21 '07 #69

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 22 '07 #70

69 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.