By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,191 Members | 2,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,191 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 #1
Share this Question
Share on Google+
69 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
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

P: 55
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
Expert Mod 10K+
P: 14,534
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

P: 55
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
Expert Mod 10K+
P: 14,534
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

P: 55
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 misunderstanding where / how to use the subform. Any way you could elaborate for me?
Thanks again,
Allen
Apr 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 55
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_________125
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
Expert Mod 10K+
P: 14,534
OK the first thing we need to examine are your relationships. Based on the information you provided I would see them as follows:

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

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

tblDistributions has a one to many relationship with tblInvestments. Therefore tblDistributions 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

P: 55
Good Morning Mary

Everything you listed above is correct with the exception of two items. The first is that as of right now tblRegistration and tblPortfolio are in a one to one relationship. They should have a one to many, but when creating the relationship I could not get it to have a one to many and honestly did not know how that would work seeing as though there should only be one row of data for each portfolio and one row of data for each registration. The field PortfolioID, the primary key of tblPortfolio is linked to field RegistrationName in the tblRegistration. I tried relating PortfolioID with the field PortfolioName in tblRegistration, both have the same data type and technically everything should work, but it's relationship type is "indeterminite" and I could never resolve why. With linking the fields that I did (PortfolioID with RegistrationName, both have different data types, PortfolioID is an autonumber and RegistrationName is text) it will cascade update and cascade delete correctly.

The second problem is that tblDeduction is related to LPID. LPID is the table that holds all of the investment information; tblInvestments is the table that holds all of the individual investments.(Yes, I know how confusing this is) Let me rephrase it. tblInvestments is the table that holds the information for peoples monetary investments, while LPID holds the information about each investment company. So, tblDeductions should be related to LPID. When I draw a relationship between Primary Key LPID in table LPID with LPName in tblDeductions it draws a one to one relationship which is incorrect. Each particular investment company can and probably will have multiple tax deductions so it should be a one to many, but I cannot get access to recognise this.

Hopefully this helps, I'm the computer tech for our firm and whenever employees try to verbally describe their computer problems it's always difficult to understand exactly what's going on, so I fully understand how tricky this must be to understand how the database has been setup and how's it's running and I appreciate greatly the time that you're spending trying to help me with this.

Thank you again,
Allen
Apr 17 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Good Morning Mary

Everything you listed above is correct with the exception of two items. The first is that as of right now tblRegistration and tblPortfolio are in a one to one relationship. They should have a one to many, but when creating the relationship I could not get it to have a one to many and honestly did not know how that would work seeing as though there should only be one row of data for each portfolio and one row of data for each registration. The field PortfolioID, the primary key of tblPortfolio is linked to field RegistrationName in the tblRegistration. I tried relating PortfolioID with the field PortfolioName in tblRegistration, both have the same data type and technically everything should work, but it's relationship type is "indeterminite" and I could never resolve why. With linking the fields that I did (PortfolioID with RegistrationName, both have different data types, PortfolioID is an autonumber and RegistrationName is text) it will cascade update and cascade delete correctly.
tblRegistration should one to many relationship with tblPortfolio. The field RegistrationName in tblRegistration is the foreign key to the primary key PortfolioID of tblPortfolio.

The reason the relationship will be showing as "indeterminate" is if there are values in tblRegistration field PortfolioName which are not currently in tblPortfolio. To find out what these values are run the following query.
Expand|Select|Wrap|Line Numbers
  1. SELECT RegistrationID, PortfolioName
  2. FROM tblRegistration LEFT JOIN tblPortfolio
  3. ON tblRegistration.PortfolioName=tblPortfolio.PortfolioID
  4. WHERE tblPortfolio.PortfolioID Is Null
  5.  
Try to resolve this as linking fields of different data types is asking for trouble.

The second problem is that tblDeduction is related to LPID. LPID is the table that holds all of the investment information; tblInvestments is the table that holds all of the individual investments.(Yes, I know how confusing this is) Let me rephrase it. tblInvestments is the table that holds the information for peoples monetary investments, while LPID holds the information about each investment company. So, tblDeductions should be related to LPID. When I draw a relationship between Primary Key LPID in table LPID with LPName in tblDeductions it draws a one to one relationship which is incorrect. Each particular investment company can and probably will have multiple tax deductions so it should be a one to many, but I cannot get access to recognise this.
Open tblDeduction in design view and go to LPName field. Change the indexed property to Yes (Duplicates OK). This will change the relationship to one to many.

Ok, tblDeduction is linked to LPID. How is LPID linked to the rest of the structure?
Apr 18 '07 #12

P: 55
Ok, tblDeduction is linked to LPID. How is LPID linked to the rest of the structure?
Oh wow! Ok, so I was able to correct all of the relationship problems. Thank you so much!!

LPID is linked to a plethora of other tables, but for what we are specifically dealing with here it's linked like so:

LPID.LPID one to many with Investments.LPName
LPID.LPID one to many with Distributions.LPName
LPID.LPID one to many with tblDeductions.LPName

Please let me know what other information you're needing. Thank you again,
Allen
Apr 18 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
tblRegistration should one to many relationship with tblPortfolio. The field PortfolioName in tblRegistration is the foreign key to the primary key PortfolioID of tblPortfolio

Investments has a one to many relationship with tblRegistrations. Therefore Investments has a foreign key to the primary key of tblRegistations.

Distributions has a one to many relationship with Investments. Therefore Distributions has a foreign key to the primary key of Investments.

tblDeduction has a one to many relationship with LPID. Therefore tblDeduction has a foreign key (LPName) to the primary key of LPID.

Investments has a one to many relationship with LPID. Therefore Investments has a foreign key (LPName) to the primary key of LPID.

Distributions has a one to many relationship with LPID. Therefore Distributions has a foreign key (LPName) to the primary key of LPID.



I'm not sure how clear this image is but you can link to it here.
You have a triangular relationship between Distributions, Investments and LPID. You need to delete one of the three links. Personally I would delete the link between Investments and Distributions as that will leave a many to many relationship between Investments and Distributions and between Investments and tblDeductions with LPID acting as the Join table. That way Distributions and Deductions will be handled using subforms and subreports rather than being handled in the same query.

Mary
Apr 18 '07 #14

P: 55
Thank you for the diagram. I cross-referenced it with my relationship table, the only difference is I never had a relationship between investments and distributions so, I didn't have to delete anything (there wasn't a triangulated relationship). The reason for all of the information being in one query is the source object of a cross-tab query that is feeding my report. The cross-tab won't accept multiple tables so I queried all the information in a normal query and then used that to feed the cross-tab. The end result of all of this is to look like this :
Picture Link
That's the actual report, on the far left you'll see "tax deductions" and that's the part that we're having problems with. I'm not sure how I would use sub-forms/sub-reports to generate a report like that. If I can, and if it's the method I need to use please let me know. Hopefully the image of the report will help clarify what all is being processed.
Thank you again,
Allen
Apr 18 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post the SQL of the Crosstab Query and if that is based on a query the SQL of it.
Apr 19 '07 #16

P: 55
Sure

Here is the cross-tab queries sql:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(qryDistri.DistributionAmt) AS AmtInvested
  2. SELECT qryDistri.RegistrationName, qryDistri.LPName,
  3. qryDistri.InceptionDate, qryDistri.TotInvested, qryDistri.Year,
  4. qryDistri.LPCatagory, ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice])
  5. AS TotalDeduction, Sum([AmtInvested]) AS TotalYearDistri
  6. FROM qryDistri
  7. WHERE (((qryDistri.RegistrationID)=[Forms]![Report Selector]![ListBox]))
  8. GROUP BY qryDistri.RegistrationID, qryDistri.RegistrationName,
  9. qryDistri.LPName, qryDistri.InceptionDate, qryDistri.TotInvested,
  10. qryDistri.Year, qryDistri.LPCatagory,
  11. ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice]), qryDistri.TotInvested,
  12. qryDistri.InceptionDate
  13. PIVOT qryDistri.Month In (1,2,3,4,5,6,7,8,9,10,11,12);
  14.  
And here is the SQL for the query that "feeds" the cross-tab:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblRegistration.RegistrationID, tblRegistration.RegistrationName,
  2. LPID.VendorFamily, LPID.LPName, Distributions.Month,
  3. Distributions.Year, Distributions.DistributionAmt,
  4. Investments.LPUnitsOwned, LPCatagories.LPCatagory,
  5. LPID.LPUnitPrice, tblDeductions.Deduction,
  6. LPID!LPUnitPrice*Investments!LPUnitsOwned AS TotInvested,
  7. Investments.InceptionDate
  8. FROM tblRegistration INNER JOIN ((LPCatagories INNER JOIN LPID ON
  9. LPCatagories.LPCatagoryID = LPID.LPCatagoryID) INNER JOIN
  10. (tblDeductions RIGHT JOIN (Investments INNER JOIN Distributions ON
  11. Investments.LPName = Distributions.LPName) ON
  12. tblDeductions.LPName = Distributions.LPName) ON
  13. (LPID.LPID = Investments.LPName) AND
  14. (LPID.LPID = Distributions.LPName)) ON
  15. tblRegistration.RegistrationID = Investments.RegistrationName;
  16.  
Hopefully that helps. I've formatted it so its easier to read, but in the query its formatted properly.
Thanks!
Allen
Apr 19 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm going to play around with the joins on the query which I don't think are correct. Firstly, what is LPCatagories relationship to the other tables.
Apr 20 '07 #18

P: 55
Alright, that sounds good. Thank you!

LPCatagories has a one to many relationship with LPID on
LPCatagories.LPCatagoryID and LPID.LPCatagoryID

LPCatagories is a table that holds different catagories that investments fall under; such as: Real Estate, Tax Credits, Oil & Gas, Energy & Electric, etc.

When you are entering a new investment on the form it has a drop down box that has a rowsource of LPCatagories, you select which catagory the investment falls under and the control source is LPID.LPCatagoryID so it is added there.

Hopefully that helps.

Thank You!
Allen
Apr 20 '07 #19

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

Can you run this and see what result you get. I know Registration is missing but ignore that for the moment.
Expand|Select|Wrap|Line Numbers
  1. SELECT LPID.VendorFamily, LPID.LPName, Distributions.Month,
  2. Distributions.Year, Sum(Distributions.DistributionAmt),
  3. Sum(Investments.LPUnitsOwned), LPCatagories.LPCatagory,
  4. LPID.LPUnitPrice, Sum(tblDeductions.Deduction),
  5. LPID!LPUnitPrice*Sum(Investments.LPUnitsOwned) AS TotInvested,
  6. Investments.InceptionDate
  7. FROM (Investments INNER JOIN
  8. (((LPCatagories INNER JOIN LPID ON
  9. LPCatagories.LPCatagoryID = LPID.LPCatagoryID)
  10. INNER JOIN tblDeductions
  11. ON LPID.LPID = tblDeductions.LPName)
  12. INNER JOIN Distributions
  13. ON LPID.LPID = Distributions.LPName)
  14. ON Investments..LPName = LPID.LPID)
  15. GROUP BY LPID.VendorFamily, LPID.LPName, Distributions.Month,
  16. Distributions.Year, LPCatagories.LPCatagory,
  17. LPID.LPUnitPrice, Investments.InceptionDate
  18.  
Apr 20 '07 #20

P: 55
Hello Mary,
When I run the query I get a normal query result that has the columns
Vendor Family, LPName, Month, Year, Sum of Distributions, Sum of Units Owned, LPCatagory, LPUnitPrice, Sum of Deduction, TotInvested, and Inception Date.
I started running through all the data that was pulled and tried cross-referencing it with the data in my tables.

LPName
The first thing I noticed was that it only showed data for the investments that had both investment distributions AND deductions. There are 5 investment entered into the database with their entire distribution history recorded and only 2 of the 5 offer tax benefits. These were the 2 out of 5 that were displayed in the query.

Sum of Units Owned
It seems that it will show multiple investments for each company, yet if two investments were place on the same day it adds them together. For instance, APC has three investors. One investor invested on May 12th in 5 units, the other two invested on June 22 in 15 units and 10 units respectively. It treated the two investments on June 22nd (made by two seperate registrations) as one 25 unit investment.

Sum of Distributions
Then I started looking at the "Sum of Distributions" column. I stared at it for awhile until I realized what it was doing. For the person invested on May 12th, their distributions were always equal to what was in the distributions table. For the other two that were invested on June 22nd their distributions were always equal to 2 times the distribution amount. For person 1 technically he should have had 5 times the distribution amount because he own 5 units and the distribution table's distribution is on a 1 unit basis where as persons 2 & 3 should have had 25 times the distributions amount.

Sum of Deductions
"Sum of Deductions" did the same thing. For person 1 investment it showed the sum equal to the value in the deduction table and for person 2 & 3 it showed it 2 times the value of the deduction table.

I was not sure what other data from the query you were wanting, so I put myself in your shoes and tried to think of everything possible. If I missed something please let me know. I just never realized how difficult all of this can be. I've been pulling my hair out for the past several months and just when I think I've got it all solved and it's ready-to-roll, another problem rears its ugly head. I just am extremely appreciative of you and all of the folks at tsdn that have been willing to help me over the past couple of months. I greatly appreciate all of the help and time spent!!
Thank you so much,
Allen
Apr 20 '07 #21

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

We're going to take this step by step. First run this query
Expand|Select|Wrap|Line Numbers
  1. SELECT tblRegistration.RegistrationID, tblRegistration.RegistrationName,
  2. Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate
  3. FROM tblRegistration INNER JOIN Investments 
  4. ON tblRegistration.RegistrationID = Investments.RegistrationName;
  5.  
This will give 165 Investments. Do we want each one displayed as a separate record?

If not do you want to total the Units owned and which Inception Date do you want to use?
Apr 23 '07 #22

P: 55
Hi Allen

We're going to take this step by step. First run this query
...
Do we want each one displayed as a separate record?
If not do you want to total the Units owned and which Inception Date do you want to use?
Hi Mary,
Yes, each of those 165 "investments" are individual personal investments all of which should be treated as their own record. They should not be totaled and the inception date will be native to that record.

For instance,
InvestmentID 219 & 51 are both the same investment company BUT they are not the same investment. They have different unit amounts and inception dates (Because the people purchased different amounts and they invested their money at different times)

Hope this helps.
Thank you much,
Allen
Apr 23 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
OK try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblRegistration].[RegistrationID], [tblRegistration].[RegistrationName], 
  2. [Investments].[InvestmentID], [Investments].[LPUnitsOwned], [Investments].[InceptionDate], 
  3. [LPID].[VendorFamily], [LPID].[LPName], [LPID].[LPUnitPrice], [LPCatagories].[LPCatagory],
  4. [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, [Distributions].[Month], [Distributions].[Year], [Distributions].[DistributionAmt], 
  5. [tblDeductions].[Deduction]
  6. FROM tblRegistration INNER JOIN ((((LPCatagories INNER JOIN LPID
  7. ON [LPCatagories].[LPCatagoryID]=[LPID].[LPCatagoryID]) 
  8. LEFT JOIN Distributions ON [LPID].[LPID]=[Distributions].[LPName])
  9. INNER JOIN Investments ON [LPID].[LPID]=[Investments].[LPName])
  10. LEFT JOIN tblDeductions ON [LPID].[LPID]=[tblDeductions].[LPName])
  11. ON [tblRegistration].[RegistrationID]=[Investments].[RegistrationName];
  12.  
And then crosstab ...
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([DistributionAmt]*[LPUnitsOwned]) AS AmtInvested
  2. SELECT qryDistriPort.RegistrationName, qryDistriPort.LPName, qryDistriPort.InceptionDate, 
  3. qryDistriPort.TotInvested, qryDistriPort.Year, qryDistriPort.LPCatagory,
  4. ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice]) AS TotalDeduction, 
  5. Sum(qryDistriPort.DistributionAmt) AS TotalYearDistri
  6. FROM qryDistriPort
  7. GROUP BY qryDistriPort.RegistrationName, qryDistriPort.LPName,
  8. qryDistriPort.InceptionDate, qryDistriPort.TotInvested, qryDistriPort.Year, 
  9. qryDistriPort.LPCatagory, ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice])
  10. ORDER BY qryDistriPort.Year
  11. PIVOT qryDistriPort.Month In (1,2,3,4,5,6,7,8,9,10,11,12);
  12.  
Mary
Apr 23 '07 #24

P: 55
Hi Mary,

I just ran the two queries.
I first ran the normal query. It produced all of the data concerning distributions, in a row by row format. It did not however produce all of the deduction information. The deduction that's in the database for Alliance Petro. 2003 B and the Mewborne are only for the year that is specified. For instance, the Alliance gives a tax benefit for 2002 only. No other year, so far. The Query treated it though, as though there was a tax benefit every year that there was a distribution, but it did not have a row that was devoted only to the deduction, with no distribution amounts.

I then ran the cross tab. The first cross tab that I ran I used the original field names aka, the ones referring to qryDistriPort. It produced a crosstab much like the one I am already using. It had all of the information about distributions arranged correctly, but the deductions were incorrect and there again, the deduction year did not have it's own designated row like each year's distribution does. I then tried replacing the query path with the query that you gave me. So I replaced all of the qryDistriPort statments with the name I saved your query under. When I ran that cross tab the query seemed to be identical to the one that I previously ran.

As always if you need any further information that I failed to mention please let me know.
Thank you so very much,
Allen
Apr 24 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
A Distribution is tied to an LPName. Taking the example of "Alliance Petroleum Corp 2003 B" there are a number of distributions over a number of years and months.

However, a deduction is also tied to the LPName. Therefore that deduction will get applied to each distribution. Where should the deduction be applied?
Apr 24 '07 #26

P: 55
A Distribution is tied to an LPName. Taking the example of "Alliance Petroleum Corp 2003 B" there are a number of distributions over a number of years and months.

However, a deduction is also tied to the LPName. Therefore that deduction will get applied to each distribution. Where should the deduction be applied?
Hmm... I had never thought of it like that.

Technically what happens is the investment (let's take Alliance Petroleum Corp 2003B for example) provides a percentage of the investment amount to use as a tax credit. So, for APC 2003B in 2003 they gave a 100% tax credit. That means if client "x" purchased 1 unit (1 unit = $25,000) then they are able to deduct $25,000 from their income on their tax return. So, what was to happen in the database was it should take the percentage (represented in decimal format) multiply it by how much the registration initially invested. Take the result of that calculation and multiply that by the registrations tax bracket. So, continuning our example. If the investor deducted $25,000 from his income and he was in the 35% tax bracket then his tax savings were $8,750.(25000 * .35)
This tax saving is completely seperate from distributions. It is not related at all. The only thing that relates them is the fact that an investment can have both tax credits/savings and distributions.

Also, each investment can have multiple tax credits/savings. So, using the above example client x saved $8,750 in 2003. APC also gave a tax credit in 2004 and 2005. In 2004 it was 30% of the amount invested so, for client x their credit was $7,500 and had a net savings of $2,625. In 2005 APC offered a tax creidt of 11%. Client x's credit was $2,750 with a net savings of $962.50.

Hopefully this helps dimenstrate what exactly the tax credit/saving is and how it differs from the distributions. So, saying all of this to say in my estimation distributions and deductions should only indirectly be related because they have no relation besides sharing the investment name/company. Hope the jumbled mess up top helps :)

Thank you,
Allen
Apr 24 '07 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
OK this is the first query ...
Expand|Select|Wrap|Line Numbers
  1. SELECT RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Distributions.Month, Distributions.Year, Sum(Distributions.DistributionAmt) AS DistributionAmount, "" As Deduction
  2. FROM tblRegistration 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
  3. GROUP BY RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, Month, Distributions.Year
  4. UNION
  5. SELECT tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Null AS [Month], tblDeductions.Year,"", tblDeductions.Deduction
  6. FROM tblRegistration 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 ON LPID.LPID = tblDeductions.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName
  7. GROUP BY tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, tblDeductions.Year, tblDeductions.Deduction;
  8.  
Apr 24 '07 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
OK the problem now is that the crosstab query pivots on the Month but there is no month assigned to the Deduction. Leave it with me.
Apr 24 '07 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, amended version of the first query...
Expand|Select|Wrap|Line Numbers
  1. SELECT RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Distributions.Month, Distributions.Year, Sum(Distributions.DistributionAmt) AS TotalYearDistri, 0 As TotalDeduction
  2. FROM tblRegistration 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
  3. GROUP BY RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, Month, Distributions.Year
  4. UNION SELECT tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Null AS [Month], tblDeductions.Year, 0  AS TotalYearDistri,  ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice]) AS TotalDeduction
  5. FROM tblRegistration 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 ON LPID.LPID = tblDeductions.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName
  6. GROUP BY tblRegistration.RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, tblDeductions.Year, tblDeductions.Deduction;
  7.  
New Crosstab query...
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([TotalYearDistri]*[LPUnitsOwned]) AS AmtInvested
  2. SELECT qryDistriPort.RegistrationName, qryDistriPort.LPName, qryDistriPort.InceptionDate, qryDistriPort.TotInvested, qryDistriPort.Year, qryDistriPort.LPCatagory, qryDistriPort.TotalDeduction, qryDistriPort.TotalYearDistri
  3. FROM qryDistriPort
  4. GROUP BY qryDistriPort.RegistrationName, qryDistriPort.LPName, qryDistriPort.InceptionDate, qryDistriPort.TotInvested, qryDistriPort.Year, qryDistriPort.LPCatagory, qryDistriPort.TotalDeduction, qryDistriPort.TotalYearDistri
  5. ORDER BY qryDistriPort.Year
  6. PIVOT qryDistriPort.Month In (1,2,3,4,5,6,7,8,9,10,11,12);
  7.  
Mary
Apr 24 '07 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
I've amended the queries slightly because I omitted Portfolio Name in the previous queries. I've also changed the report. I'm emailing you the amended database now.

Mary
Apr 24 '07 #31

P: 55
Hi Mary,

Sorry to take so long getting back with you. I've been fooling around with the database all weekend and I still can't get it to work. We had to send out a report to one of our clients so I had one eye on fixing the distribution/tax deduction problem and another eye on patching up a make-shift report.

I still cannot seem to get the queries to return the correct information. The one positive is that they are returning the deduction even if no distribution is present for that year, the problem though is that they are not returning all of the distributions. It seems to be rather scattered as to which they will grab and display and which they won't.

All of this is based on the two SQL statements that you posted. If you ammened those further and they were saved as queries in the database itself please let me know and I'll see if I can get those to work properly.

Thank you again for all of your help,

Allen
Apr 30 '07 #32

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

Leaving the crosstab to one side for the moment. This was the latest query I worked on.
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.Month, Distributions.Year, Sum(Distributions.DistributionAmt) AS TotalYearDistri, 0 As TotalDeduction
  2. 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
  3. GROUP BY  tblPortfolio.PortfolioName, RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, Month, Distributions.Year
  4. 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, Null AS [Month], tblDeductions.Year, 0  AS TotalYearDistri,  ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice]) AS TotalDeduction
  5. 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 ON LPID.LPID = tblDeductions.LPName) ON tblRegistration.RegistrationID = Investments.RegistrationName
  6. 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;
  7.  
Apr 30 '07 #33

P: 55
Wow Mary!!
Ok, I got back in the office this morning and have been working on the query since then. The sql that you wrote up above worked wonderfully. I looked through all that was displayed and saw that it looked accurate. I then built a cross-tab query based off of that query. When I ran the cross tab it ran correctly, which was great!! I then said to myself, "What could it hurt to try and make a report based off of the cross-tab?" So I did, and sure enough it worked! Needless to say I was quite thrilled.
The check list I ran across the data was this:
Did it display the deduction received for only the year that it was received even if there were no distributions? Yes
Did it display that value only for one year and not throughout the life of the program? Yes
Did it have the information accurate to each individual registration? Yes
Absolutely awesome!
The one thing now that I need to do is on the report have any investments with no data yet to be turned invisible. For instance: Atel Venture Fund has no distributions and has no deductions, yet it shows up on the report and queries. *I'm assuming that it does this because you wrote the query to include records even if they don't have data in the distribution table* I figured that I could use the NoData function, but I haven't investegated it, just a thought. Also, on the distribution/deduction info that has a zero value I need to turn those text boxes to invisible. I figured I could use an if statement in the onformat section of the detail header. If txtbox = "0" then txtbox.visible = false.
I wasn't sure if this was the method to use, but like the NoData command I haven't tried it yet.

Thank you so much for the help and if there is anything else that needs to be added to the cross-tab query please let me know. I'd also be extremely interested to learn what you did to make the sql statment pull the correct data. The one thing that caught me off guard was the symbol next to the query. It was two intertwined circles, I haven't seen that in access before and wasn't sure what it meant. The Union Select statement was new to me as well.

Again Mary,
Thank you so, so very much for all of this help. I greatly appreciate it and I know that the firm will greatly appreciate it.

Sincerely,

Allen
May 4 '07 #34

P: 55
Mary,

I've been looking over and over on the sql statement you wrote trying to understand it. I think I have a fairly good grasp on it and the (new to me) union select/query. I belive I understand the method used and why it was done the way it was. I'm trying to add this statement to the query though
Expand|Select|Wrap|Line Numbers
  1. (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*
  2. (([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction
All of that information is already being queried with the exception of tblTaxBracket.TaxBracket. This is a new table I made to house registrations individual tax bracket from year to year. Before adding anything to the union query I made a seperate query to make sure I had my logic correct. The query that displayed everything correctly is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT Investments.RegistrationName, (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*
  2. (([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction
  3. FROM (LPID INNER JOIN Investments ON LPID.LPID = Investments.LPName) INNER JOIN (tblDeductions INNER JOIN tblTaxBracket ON tblDeductions.Year = tblTaxBracket.Year) ON LPID.LPID = tblDeductions.LPName
  4. WHERE (((Investments.RegistrationName)=[Forms]![Report Selector]![ListBox]))
  5. ORDER BY Investments.RegistrationName;
  6.  
Basically it's grabbing the amount invested and multiplying that by the investments tax deduction. It then takes that product and multiplies that by the particular tax bracket for that year.
The "/100" is because in the table the deduction maybe 100 but it should be 1 so I'm dividing it to get it in correct format. In that query I joined the year fields for tblTaxBracket and tblDeduction. This worked great in the query. It only displayed the data that I wanted / needed and the equation was correct as well.

I then went to the sql in the union query and looked as to where I should put it. I knew I shouldn't put it in the top section because it was automatically setting the deduction value to 0. I then looked at the bottom and saw where the deduction equation was. I replaced that equation with my equation and then looked on down to the FROM section. I tried this:
Expand|Select|Wrap|Line Numbers
  1. 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 ON LPID.LPID = tblDeductions.LPName) INNER JOIN tblTaxBracket ON tblDeductions.Year = tblTaxBracket.Year) ON tblRegistration.RegistrationID = Investments.RegistrationName
  2.  
The bolded section is what I added.

But after doing that when I try and run the report I get the following error:
"Join expression not supported"

I was expecting this error or one similar to it, but I'm not sure how to fix it. I belive the problem completely revolves around the fact that I do not fully understand why the FROM statement is written how it is. I don't believe that the addition of this new table (tblTaxBracket) would cause all that much raucus, but I'm not sure how to write my join statement correctly so it is accepted.

Sorry to complicate things even more, but I would greatly appreciate help in fixing my join quandary.
Thank you again Mary,
Allen

P.S. I was able to set the text boxes in the report that were 0 to invisible and then if they had data to visible. I haven't started on trying to 'skip' investments that do not have any distributions/deductions. I wanted to get this query fixed first.

P.S.S. Sorry for the double post.
May 4 '07 #35

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

I haven't had time to fully read through the last two posts but as a quick note. The two circles represent a union query.

A Union query combines the results of two or more SELECT queries into one result set. So for example if you want to get all the names from two tables but those tables aren't related. You could use a union query to get all the names from the first table and then add all the names from the second table.

In the case of trying to join another table into this query. I have used a LEFT JOIN to get everything from the first table and only the information from the other table that matches. LEFT JOINS are inherited so if you are adding a further table you will need to use a LEFT JOIN.

I'll check out the rest of the information in the posts later.

Mary
May 4 '07 #36

P: 55
Ahh Ok, so I've been tooling on the query all morning and I just cannot seem to get the joins right. So far I've broken the query down like so
Expand|Select|Wrap|Line Numbers
  1. [Show Registration Names where they have a portfolio]) INNER JOIN
  2.  (((([Show only catagories where they have an id]) 
  3. LEFT JOIN [Only show distributions that have a name])
  4.  INNER JOIN [show only investment names that have an id]) 
  5. LEFT JOIN [show all deductions and only the distributions that have a matching lpname])
  6.  ON join all of this where registration name has an id
  7.  
Now that of course is me trying to simplfy it so that I understand exactly whats going on. The problem is, is I don't know where this statement would fit into all of that:
Expand|Select|Wrap|Line Numbers
  1. tblDeductions INNER JOIN tblTaxBracket 
  2. ON tblDeductions.Year = tblTaxBracket.Year
  3.  
It doesn't seem (to me) to mesh in the above statement as to what all is going on. So, should it be off to the side after the registrationname section? I'm just confused as to the layout of the FROM statement. Any guidance would be great!
Thank you,
Allen
May 7 '07 #37

NeoPa
Expert Mod 15k+
P: 31,433
Allen,

Mary's very busy at the moment with various Admin issues, particularly those pertaining to the site changes being rolled out.
As your question/thread is quite so involved and long, I don't expect anyone will be able to jump in for her on this one. If you can be patient, I'm sure she will get around to you as soon as she's able. This may, however, be a after number of days.

Clearly, there are pros and cons involved with being helped by the 'Top Man' ;)
May 10 '07 #38

P: 55
Allen,

Mary's very busy at the moment with various Admin issues, particularly those pertaining to the site changes being rolled out.
As your question/thread is quite so involved and long, I don't expect anyone will be able to jump in for her on this one. If you can be patient, I'm sure she will get around to you as soon as she's able. This may, however, be a after number of days.

Clearly, there are pros and cons involved with being helped by the 'Top Man' ;)
Thank you for the update NeoPa. I was beginning to get a little worried that getting this query to work was a lost cause. Thank you though for delivering the message. I hope the roll out of the new site goes smoothly and doesn't present any major headaches.

Patiently waiting :)
Allen
May 15 '07 #39

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you for the update NeoPa. I was beginning to get a little worried that getting this query to work was a lost cause. Thank you though for delivering the message. I hope the roll out of the new site goes smoothly and doesn't present any major headaches.

Patiently waiting :)
Allen
Hi Allen

If you don't hear from me by the end of the week can you send me a PM to remind me.

Mary
May 15 '07 #40

NeoPa
Expert Mod 15k+
P: 31,433
We were both up till after 02:00 last night (Mary's in the same time-zone over here as I am) working on various aspects of the site, so it's certainly not just a lame excuse. She's not someone I'd ever refer to as a 'shirker'.

I hope that at least you are now back 'in the picture' anyway :)

-Adrian.
May 15 '07 #41

P: 55
Oh my! Well Adrian, hopefully you and Mary are getting near completion. I really like the new roll over buttons at the top. That's a pretty classy touch and is actually useful! Form AND function! There are a few other things that I've been working on around the office, so I'm definitely not twidling my thumbs. :) I know making major changes to a site with minimal downtime takes a lot of work and planning and if it weren't for this wonderful site in the first place I wouldn't have had any help for my problem so please don't worrry about it. If I haven't heard from Mary by the end of the week I'll just PM her like requested.
Thanks for the update. Y'all are doing a wonderful job!

-Allen
May 16 '07 #42

NeoPa
Expert Mod 15k+
P: 31,433
I can't pretend to be heavily involved in the design of the site myself (although anyone will tell you I'm always willing to offer my opinions ;)), though I do, nevertheless, have many threads to cover & respond to. Mary, on the other hand, is very much involved :)
May 17 '07 #43

P: 55
Hey Mary,
Just wanted to see how your work load was coming and whether or not you thought you'd be able to looka t my problem again this week.

Thanks,
Allen
Jun 4 '07 #44

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey Mary,
Just wanted to see how your work load was coming and whether or not you thought you'd be able to looka t my problem again this week.

Thanks,
Allen
Yes Allen, hopefully today.
Jun 4 '07 #45

P: 55
Yes Allen, hopefully today.
Oh Great!
Thanks for the update, more than anything I was just trying to see where we stood, but if you could look at it today that'd be marvelous.

Thank you
Jun 4 '07 #46

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

Try this for the 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.Month, Distributions.Year, Sum(Distributions.DistributionAmt) AS TotalYearDistri, 0 As TotalDeduction
  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. GROUP BY tblPortfolio.PortfolioName, RegistrationID, tblRegistration.RegistrationName, Investments.InvestmentID, Investments.LPUnitsOwned, Investments.InceptionDate, LPID.VendorFamily, LPID.LPName, LPID.LPUnitPrice, LPCatagories.LPCatagory, Month, Distributions.Year
  10. 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, Null AS [Month], tblDeductions.Year, 0 AS TotalYearDistri, (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))
  11. *(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction
  12. FROM (tblPortfolio INNER JOIN tblRegistration 
  13. ON tblPortfolio.PortfolioID = tblRegistration.PortfolioName) 
  14. INNER JOIN ((((LPCatagories INNER JOIN LPID 
  15. ON LPCatagories.LPCatagoryID = LPID.LPCatagoryID) 
  16. LEFT JOIN Distributions ON LPID.LPID = Distributions.LPName) 
  17. INNER JOIN Investments ON LPID.LPID = Investments.LPName) 
  18. LEFT JOIN (tblDeductions LEFT JOIN tblTaxBracket 
  19. ON tblDeductions.Year = tblTaxBracket.Year) 
  20. ON LPID.LPID = tblDeductions.LPName) 
  21. ON tblRegistration.RegistrationID = Investments.RegistrationName
  22. 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];
  23.  
Jun 6 '07 #47

P: 55
Wow!
So the query finally works !!! Thank you so much for all of this help Mary! I appreciate it so very much and am so glad that you were willing to stick with me through the whole thing since it was a little bit more complex of a problem than I thought it was to begin with. I greatly, greatly appreciate it!!
I've spent a good amount of time trying to understand the sql statement, more specifically the joins in the second part of the union. I just can't really make heads or tails of it when it gets to the 'big' Inner Join. That's where I lose it. If you can recommend a website that explains the workings of joins fairly well I'd appreciate a link. The problem I've run accross is I understand how to write them when they're extremely basic joins, but when they have multiple, multiple joins I just have no idea where to begin.

And lastly the site looks great!! The new script formatting that you instituted is awesome!! It is sooo much easier to read the code now! I also like the new icon that shows up in the address bar. Great job on all of it!!

Thank you again for all of your help, and for running an excellent site in general. I know I speak for thousands, if not millions, when I say that thescripts.com is the greatest asset/community I could ask for while building my database!
Jun 6 '07 #48

MMcCarthy
Expert Mod 10K+
P: 14,534
You're more than welcome Allen and thank you for all your kind words. I'll see if I can get some time to put an article together on multiple joins as I don't know of one at the moment. (Don't hold your breath though :LOL)

If anyone else would like to take up this challenge let me know.

Mary
Jun 6 '07 #49

P: 55
Mary
/shakes head
Well I made a report that used the crosstab query that I made using the union query above. After making it and running it I ran into a problem that I had completely not realized when looking at the queried data. Because of how the crosstab gathers the information on any years that don't have tax deductions it has a $0.00 amount which is fine, EXCEPT when the year has a tax deduction because what ends up happening is two values show up. The actual tax deduction and then the $0.00. Well, if I place all the fields in the detail section of the report as you can imagine it will have two lines for certain years [see link and image attached for visual aid] if I use a header and place the fields there those tax deductions that happen to be after the $0.00 they do not show up.

All in all I'm not sure if this is something that could simply be handled in the OnFormat section telling access to make the txtboxes invisible when their value = 0 or if it is quite a bit more involved. I've played around with the grouping trying to make it sort the data in the following order LPName -> Year -> taxDeduction that way the $0.00 would always be displayed after the actual taxDeduction if deduction was present. That doesn't seem to do the trick.

So...Just when I thought this beast was conquered and just when you thought you had gotten rid of me..... I rise again :/

Any ideas on how to solve it? Thank you in advance

Allen

Link for image of report : Link

P.S. I'd love to learn more about multiple join queries so I'm all in favor of a article :) And what's this about needing some time? It's not like you have a website to run or anything Lol
Jun 8 '07 #50

69 Replies

Post your reply

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