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

[MMcCarthy]

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

[kabradley]

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

[MMcCarthy]

Hi Allen

Leaving the crosstab to one side for the moment. This was the latest query I worked on.
[code=sql]
SELECT tblPortfolio.Po rtfolioName, RegistrationID, tblRegistration .RegistrationNa me, Investments.Inv estmentID, Investments.LPU nitsOwned, Investments.Inc eptionDate, LPID.VendorFami ly, LPID.LPName, LPID.LPUnitPric e, LPCatagories.LP Catagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Distributions.M onth, Distributions.Y ear, Sum(Distributio ns.Distribution Amt) AS TotalYearDistri , 0 As TotalDeduction
FROM (tblPortfolio INNER JOIN tblRegistration ON tblPortfolio.Po rtfolioID=tblRe gistration.Port folioName) INNER JOIN (((LPCatagories INNER JOIN LPID ON LPCatagories.LP CatagoryID = LPID.LPCatagory ID) LEFT JOIN Distributions ON LPID.LPID = Distributions.L PName) INNER JOIN Investments ON LPID.LPID = Investments.LPN ame) ON tblRegistration .RegistrationID = Investments.Reg istrationName
GROUP BY tblPortfolio.Po rtfolioName, RegistrationID, tblRegistration .RegistrationNa me, Investments.Inv estmentID, Investments.LPU nitsOwned, Investments.Inc eptionDate, LPID.VendorFami ly, LPID.LPName, LPID.LPUnitPric e, LPCatagories.LP Catagory, Month, Distributions.Y ear
UNION SELECT tblPortfolio.Po rtfolioName, tblRegistration .RegistrationID , tblRegistration .RegistrationNa me, Investments.Inv estmentID, Investments.LPU nitsOwned, Investments.Inc eptionDate, LPID.VendorFami ly, LPID.LPName, LPID.LPUnitPric e, LPCatagories.LP Catagory, [LPID]![LPUnitPrice]*[Investments]![LPUnitsOwned] AS TotInvested, Null AS [Month], tblDeductions.Y ear, 0 AS TotalYearDistri , ([Deduction]/100)*([LPUnitsOwned]*[LPUnitPrice]) AS TotalDeduction
FROM (tblPortfolio INNER JOIN tblRegistration ON tblPortfolio.Po rtfolioID=tblRe gistration.Port folioName) INNER JOIN ((((LPCatagorie s INNER JOIN LPID ON LPCatagories.LP CatagoryID = LPID.LPCatagory ID) LEFT JOIN Distributions ON LPID.LPID = Distributions.L PName) INNER JOIN Investments ON LPID.LPID = Investments.LPN ame) LEFT JOIN tblDeductions ON LPID.LPID = tblDeductions.L PName) ON tblRegistration .RegistrationID = Investments.Reg istrationName
GROUP BY tblPortfolio.Po rtfolioName, tblRegistration .RegistrationID , tblRegistration .RegistrationNa me, Investments.Inv estmentID, Investments.LPU nitsOwned, Investments.Inc eptionDate, LPID.VendorFami ly, LPID.LPName, LPID.LPUnitPric e, LPCatagories.LP Catagory, tblDeductions.Y ear, tblDeductions.D eduction;
[/CODE]

[kabradley]

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

[kabradley]

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
[code=sql]
(((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*
(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction[/code]
All of that information is already being queried with the exception of tblTaxBracket.T axBracket. 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:
[code=sql]
SELECT Investments.Reg istrationName, (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))*
(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction
FROM (LPID INNER JOIN Investments ON LPID.LPID = Investments.LPN ame) INNER JOIN (tblDeductions INNER JOIN tblTaxBracket ON tblDeductions.Y ear = tblTaxBracket.Y ear) ON LPID.LPID = tblDeductions.L PName
WHERE (((Investments. RegistrationNam e)=[Forms]![Report Selector]![ListBox]))
ORDER BY Investments.Reg istrationName;
[/code]
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:
[code=sql]
FROM (tblPortfolio INNER JOIN tblRegistration ON tblPortfolio.Po rtfolioID=tblRe gistration.Port folioName) INNER JOIN (((((LPCatagori es INNER JOIN LPID ON LPCatagories.LP CatagoryID = LPID.LPCatagory ID) LEFT JOIN Distributions ON LPID.LPID = Distributions.L PName) INNER JOIN Investments ON LPID.LPID = Investments.LPN ame) LEFT JOIN tblDeductions ON LPID.LPID = tblDeductions.L PName) INNER JOIN tblTaxBracket ON tblDeductions.Y ear = tblTaxBracket.Y ear) ON tblRegistration .RegistrationID = Investments.Reg istrationName
[/code]
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.

[MMcCarthy]

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

[kabradley]

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
[code=sql]
[Show Registration Names where they have a portfolio]) INNER JOIN
(((([Show only catagories where they have an id])
LEFT JOIN [Only show distributions that have a name])
INNER JOIN [show only investment names that have an id])
LEFT JOIN [show all deductions and only the distributions that have a matching lpname])
ON join all of this where registration name has an id
[/code]
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:
[code=sql]
tblDeductions INNER JOIN tblTaxBracket
ON tblDeductions.Y ear = tblTaxBracket.Y ear
[/code]
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 registrationnam e section? I'm just confused as to the layout of the FROM statement. Any guidance would be great!
Thank you,
Allen

[NeoPa]

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' ;)

[kabradley]

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

[MMcCarthy]

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

[NeoPa]

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.

[kabradley]

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

[NeoPa]

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 :)

[kabradley]

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

[MMcCarthy]

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.