473,562 Members | 2,759 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

55 New Member
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 tblDistribution s, 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 tblDistribution s 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 #1
69 8033
MMcCarthy
14,534 Recognized Expert Moderator MVP
I have an inner join between the tblDeductions and tblDistribution s so that all the distribution records will be shown even if no deductions are present, because not all investments have a tax benefit.
This would be a Right Join i.e.
Expand|Select|Wrap|Line Numbers
  1. FROM tblDeductions Right Join tblDistributions
  2. WHERE <Your join criteria>
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.  
Move it to the Format event of the appropriate section of the report and change as follows:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(txtDeduction) Then
  2.    lblTax.Visible = False
  3.    txtDeduction.Visible = False
  4. Else
  5.    lblTax.Visible = True
  6.    txtDeduction.Visible = True
  7. End If
  8.  
The rest of the problems may be fixed by changing to a right join.

Mary
Apr 2 '07 #2
kabradley
55 New Member
Hello Mary,
Sorry for the terribly delayed reply. I had the worst case of the flu, it was awful. I'm back in the office today though and made the changes to my reports for the 'on format'. It worked like a charm!

The one problem that I'm still having to confront is how to get the tax deduction to display when there's no distributions for that year. Any suggestions there?

Thank you,
Allen
Apr 10 '07 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hello Mary,
Sorry for the terribly delayed reply. I had the worst case of the flu, it was awful. I'm back in the office today though and made the changes to my reports for the 'on format'. It worked like a charm!

The one problem that I'm still having to confront is how to get the tax deduction to display when there's no distributions for that year. Any suggestions there?

Thank you,
Allen
Hi Allen

Have a look at the first part of my post about changing the join to a right join (or left as the case may be).

Mary
Apr 10 '07 #4
kabradley
55 New Member
Hey Mary

Here is the From segment of my query. I have one Right Join already, but to be completely honest it seems as though the right join continues throughout several tables. Hopefully this doesn't seem like to much of a piled mess
Expand|Select|Wrap|Line Numbers
  1. FROM tblRegistration INNER JOIN ((LPCatagories INNER JOIN
  2. LPID ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) INNER JOIN
  3. (tblDeductions RIGHT JOIN (Investments INNER JOIN Distributions ON
  4. Investments.LPName = Distributions.LPName) ON tblDeductions.LPName =
  5. Distributions.LPName) ON (LPID.LPID = Investments.LPName) AND 
  6. (LPID.LPID = Distributions.LPName)) ON tblRegistration.RegistrationID = 
  7. Investments.RegistrationName;
  8.  
The Tax deduction table is tblDeductions and the distribution table is Distributions. The key as I'm pretty sure you already understand, but for other people looking at this thread that don't know, is that some distribution years won't have tax deductions and some tax deduction years won't have distributions and some years they will have both! :)

Also, not to throw to many irons in the fire, but is there any method that can be used in the on format portion of a report to move certain objects a certain number of twips?

Thank you again for reading through all of these problems and trying to help. I sincerely appreciate it.
-Allen
Apr 10 '07 #5
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hey Mary

Here is the From segment of my query. I have one Right Join already, but to be completely honest it seems as though the right join continues throughout several tables. Hopefully this doesn't seem like to much of a piled mess
Expand|Select|Wrap|Line Numbers
  1. FROM tblRegistration INNER JOIN ((LPCatagories INNER JOIN
  2. LPID ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) INNER JOIN
  3. (tblDeductions RIGHT JOIN (Investments INNER JOIN Distributions ON
  4. Investments.LPName = Distributions.LPName) ON tblDeductions.LPName =
  5. Distributions.LPName) ON (LPID.LPID = Investments.LPName) AND 
  6. (LPID.LPID = Distributions.LPName)) ON tblRegistration.RegistrationID = 
  7. Investments.RegistrationName;
  8.  
The Tax deduction table is tblDeductions and the distribution table is Distributions. The key as I'm pretty sure you already understand, but for other people looking at this thread that don't know, is that some distribution years won't have tax deductions and some tax deduction years won't have distributions and some years they will have both! :)

Also, not to throw to many irons in the fire, but is there any method that can be used in the on format portion of a report to move certain objects a certain number of twips?

Thank you again for reading through all of these problems and trying to help. I sincerely appreciate it.
-Allen
Allen as this is a many to many relationship the only way to include all the records would be using a subform.

Mary
Apr 10 '07 #6
kabradley
55 New Member
Mary
The only way to do it would be to use a subform? I must say, I'm a little confused. Would the data still be coming from the cross-tab query? If so, am I not correct in thinking that the query isn't going to have the correct information to begin with? If the report data is coming from the crosstab query and the crosstab query isn't correct then how will the report be any different? I could just be misunderstandin g where / how to use the subform. Any way you could elaborate for me?
Thanks again,
Allen
Apr 11 '07 #7
MMcCarthy
14,534 Recognized Expert Moderator MVP
Sorry Allen

I meant sub report not subform.

If your crosstab query is not returning all results because of the many to may relationship then you will need to create two queries the first of which will populate the main report and the second of which will polulate records in the sub report for each record in the main report.

Your problem as I understand it is not one of sql code but rather an issue of logic. If you don't agree that this is what you are trying to do then try to explain logically what you want the query results to be. The report is irrelevant until the query results are correct.

Mary
Apr 12 '07 #8
kabradley
55 New Member
Good Morning Mary,

I think what may help is for me to give a more thorough explanation of the business I am in. I work for a financial planning firm. They take investors money and allocate it in certain investments. Several months ago I was assigned the task of creating a way to track all of these investments. I started building the access database and slowly started understanding access and vba. The database now has effectively 4 seperate sections, or operations it does. The first is recording the individual registrations that make the investments. Typically a registration is just the individual that makes the investment, but in the legal term it just means the name that the investment is registered under. So, it holds the information of the registrations. Each registration is apart of a portfolio. So, hypethetically lets say you have a registration, your husband has a registration, and you have two kids that have trust accounts. So, you would have the portfolio "Mary's Portfolio" that would be related to the three seperate registrations i.e. Mary's Personal IRA, Mr. Mary's Personal IRA, Mary's kids IRA.

The second task the db accomplishes is holding investment information. For instance ABC investment costs $10,000 for one unit *you can purchase more then one unit* and ABC investment falls under the catagory of an Oil & Gas Investment.
With this information and then the information under the registration table, I then have an actual Investment table, this holds the particular investments. So, lets say Mary's Personal IRA purchases 3 units of ABC investment the table would hold that information and then the date the investment was made.

The third task, besides tracking all of that is to track the distributions that each investment produces. So, lets say ABC investment distributes a check once a month. I enter all of that data for every month since it's first distribution.

The key problem that's comprised this thread is the relation between an investments distributions and its tax benefits. Some investments only distribute money. Others distribute money BUT they also give you a tax benefit, and some do not distribute anything and only give you a tax benefit.
The tax benefits work as follows. Lets say ABC investment gives you a 70% deduction per unit. Each unit is $10,000. 70% of $10,000 is $7,000. So, when you file your taxes you can take $7,000 off of your income, therefore reducing the amount of taxes that you will effectively pay.

The problem I am having though is this. Continuing our ABC investment example. Lets say Mary's Personal IRA purchases 3 units of ABC investment in November of 2003. Upon purchasing the investments you can deduct $21,000 from your income when you file your taxes for 2003. Let's say that ABC is just a really great investment and gives you a tax benefit in 2004 as well. *The tax benefit only lasts for one year, unless othewise stated* So, let's say in 2004 they give you a 10% deduction. So, in 2003 you deduct $21,000 and in 2004 you can deduct $3,000 from your income. This is all great except for the fact that ABC investment did not start distributing until March of 2004. So, it has no recorded distributions for 2003, but it has a recorded tax benefit for 2003. This is where the problem comes. In the crosstab query I cannot get this tax benefit to show up without having a distribution for that year. So for ABC the crosstab should be something like this:

Inv. N._Reg. Name_____Year__ Tax____Jan Feb March ...
ABC Mary's Personal IRA 2003 210000
ABC Mary's Personal IRA 2004 3000___________ 125
ABC Mary's Personal IRA 2005________111 241 547

In this exact situation though, I cannot get it to do this instead it looks something like this:

Inv. N. Reg. Name_____Year Tax____Jan Feb March ...
ABC Mary's Personal IRA 2004 21000_________1 25
ABC Mary's Personal IRA 2005 21000__111 241 547

(The lines are merely spacing, not information of any sort)

It will list the initial tax benefit for all the years, it won't relate the particular year of the tax benfit with said row in the query and it also will not display any information for years in which there were no distributions.

Hopefully this long explanation helps you grasp what the databases purpose is, what it does, and what my problem is. If you need further clarification just let me know. Or, if you want any code snipets or anything of that nature I'd be happy to ablidge.

Thank you again so much for spending the time to help me, I greatly appreciate it.
-Allen
Apr 13 '07 #9
MMcCarthy
14,534 Recognized Expert Moderator MVP
OK the first thing we need to examine are your relationships. Based on the information you provided I would see them as follows:

tblRegistration s has one to many relationship with tblPortfolio. Therefore tblRegistration s has a foreign key to the primary key of tblPortfolio.

tblInvestments has a one to many relationship with tblRegistration s. Therefore tblInvestments has a foreign key to the primary key of tblRegistations .

tblDistribution s has a one to many relationship with tblInvestments. Therefore tblDistribution s has a foreign key to the primary key of tblInvestments.

tblTaxBenefits would be a separate table.
tblTaxBenefits has a one to many relationship with tblInvestments. Therefore tblTaxBenefits has a foreign key to the primary key of tblInvestments.

Mary
Apr 16 '07 #10

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

Similar topics

1
19146
by: Stephan | last post by:
Hi, I'm using Visual Studio 2003 (C#) with the integrated Crystal Report software and have the following question: How can I assign a value (string) to an unbound (string) field in Crystal Report at runtime? Example: private void button1_Click(object sender,
3
6252
by: Stig | last post by:
I'm having two tables with no relation and I want to list them both in one report. How can I do this. I have tried to use sub report, but cant get it to work? can someone please help me. If I just create an subreport with one static text object it will not even then show at runetime.
3
3690
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what a spreadsheet might show, the column names will actually be dynamic, based on data from a SQL Server 2000 database. The row data will also come...
3
5216
by: Mat N | last post by:
Hi, I've been trying to work out how to create a report based on crosstab query for which the number of fields is variable. For example in a situation where you show customer billing by year in the following format: Customer 2000 2001 2002 Bill 103 10 205 Frank 12 50
3
10618
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems that are hard to find. The main problem I am having right now is that I have a report that is sorted by one of these lookup fields and it only displays...
2
1908
by: Darryl Kerkeslager | last post by:
As the subject above hopefully makes clear, I want to do several reports, "with lots of fields not otherwise in database". These reports also have variable-length text. I have defined the following options (all users have Office 2000): 1. All Access solution. This appears to involve creating forms to enter data that is just put into a...
1
2084
by: sbarron76 | last post by:
Good day. Your help would be appreciated on my issue. I have an MS Access 97 database that produces reports that mirror letters with the necessary fields. One of these fields is a series of values. I have created labels for the headings and text boxes for the body of the table. I would like to be able to make the labels invisible if there is...
11
1744
by: BASSPU03 | last post by:
Tomorrow's my final presentation of my DB and I ran into an unexpected problem: I access a main form called frmViewAllResources with several tabbed subforms through my switchboard. There's a preview and print button on the main form that generates a report called "Commodities." It previews and prints all the relevant fields from the main form...
12
3518
by: Studiotyphoon | last post by:
Hi, I have report which I need to print 3 times, but would like to have the following headings Customer Copy - Print 1 Accounts Copy - Print 2 File Copy -Print 3 I created a macro to print the report three times, but do not know how
0
7577
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7869
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7627
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7935
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5193
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3608
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2073
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.