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

[kabradley]

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

[MMcCarthy]

OK Allen,

Try this for the UNION query.

[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 = tblRegistration .PortfolioName)
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 , (((([LPID].[LPUnitPrice])*([Investments].[LPUnitsOwned]))
*(([tblDeductions].[Deduction])/100))*(([tblTaxBracket].[TaxBracket])/100)) AS TotalDeduction
FROM (tblPortfolio INNER JOIN tblRegistration
ON tblPortfolio.Po rtfolioID = tblRegistration .PortfolioName)
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 LEFT JOIN tblTaxBracket
ON tblDeductions.Y ear = tblTaxBracket.Y ear)
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, [tblTaxBracket].[TaxBracket];
[/code]

[kabradley]

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!

[MMcCarthy]

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

[kabradley]

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

[MMcCarthy]

LOL!

I'll get to this when I can.

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

Mary

[kabradley]

LOL!

I'll get to this when I can.

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

Mary

Alright will do.
Thanks

[NeoPa]

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

[kabradley]

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

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

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

Any other suggestions?

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

Thanks for trying to help Adrian.
-Allen

[NeoPa]

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

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

[NeoPa]

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

for civilised countries or, if in USA then :

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

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

[MMcCarthy]

Allen

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

Mary

[kabradley]

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

Thanks,
Allen

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

[NeoPa]

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

[kabradley]

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

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