Distribution Report - Should a pivot table be used?

[kabradley]

Hello,
Thanks for looking at my post and hopefully having an answer or at least a suggestion to my problem. I currently work at a financial planning office that deals with many clients and accounts. Each client may have multiple accounts such as an individual, IRA, and possibly a joint tenant account. Each one of these accounts for the particular client is contained in a 'Portfolio'. For example, Joe Smith may have 3 accounts: Joe Smith Individual, Joe Smith Roth IRA, and Joe and Mary Smith Joint Tenants with Rights of Survivorship. I have been instructed to create a database that tracks all of these accounts, portfolios, and the individual investments that go alone with each.
I was assigned the project in October of 2006 walking into it with no previous Access knowledge, but quite a bit of general computing knowledge. Through the project I have learned quite a bit, but for the past month or so I have been completely stuck on one very, very important detail. Our distribution reports.

-How our Distributions Work-
Distribution reports come in either monthly, quartly, or annualy for each investment. Lets say Joe Smith purchased two units of XYZ L.P. If XYZ L.P. was $25, 000 per unit then Joe invested $50, 000. Within a year or so *time varies with each investment of course* Joe starts receiving distribution checks. We also receive a paper recording how much was distributed per unit. So for instance, if XYZ L.P. distributed $25 / unit in February Joe would receive a check for $50. When these reports come in detailing how much was distributed I am to input that information in a table labeled Distributions. This table then has a relationship with tblInvestments by field name LPName. Pulling the correct data through a query is fine, there's no errors there. The problem though, is then taking this information and reporting it.

-Formating needed for Distributions Report-
What my boss wants is to have a report that on the first line would have the months of the year Jan...Dec listed along with after December a title that says Total Distributions. On the line under this "title bar" there would be a header for the investment name. The next grouping would be for the particular year and the investments in that year.
Here comes the extremely tricky part: After the year, each distribution would be listed HORIZONTALLY so that it lines up with the correct month in the "Title Bar". So, instead of listing the information in access' traditional line by line method it would instead need to be listed in a row AND column format. After the first years distributions have been outlined at the end of that row would be the total dollar value for the distributions for only that year. The report would then skip a line and start the same process for the next year. If there wasn't another year then it would go to the next investment and start the whole process over again on a new page.

I have tried everything I can think of to make this work on a standard report in access, but just cannot get it to do it. It seems as though it somewhat violates the way access does its reporting. I then tried pivot tables. This gave a better result then the report way, but it was still way off. For one, I couldn't delete some of the title bars *where you make your selection of information* and on a report that would be printed out, it just did not look right. Besides the asthetic side of things the information that would display did not line up with the "title bar" lets say for instance that the first distribution came in June, NOT January. The pivot table just started displaying information in January like normal, because it was the first distribution it saw. So, there would need to be some method of correlating the month of the distribution to the month in the "Title Bar". The pivot table I designed was based off of a query that had the Account Name parameter filled by a form in which I would select which client I wanted the report for. So, all it needs to do is display the Investment Name and the distributions in a correct fashion.

I was given an ultimatum to finish it last friday, but as evident by me posting this today, it still is not finished. It seems all but impossible to do in access, bu t seeing as though the whole reason for the database in general was for this ONE report it has to work. Thank you in advance for any help you may be able to give me. Like I said before, I've worked on this one problem alone now for a little more then a month and because I haven't finished the database in a timely manner I am beginning to worry about my job.

Thank you much,
Allen
p.s. Here is a link to a drawing of what the report should look like, hopefully this will clarify any questions link

[nico5038]

Just checkout the Crosstable query.
Create first a select query to filter for one year and to provide a formatted YearMonth field like:
select Year([datefield]) & right("00" & Month([datefield]),2) as YearMonth, TheHeaderField, TheValueField from tblYours;

Now change the querytype to crosstable and set the YearMonth to the ColumnHeading, the TheHeaderField to the HeaderRow and the TheValueField to the Value. Finally change the GroupBy from the TheValueField to Sum.

Nic;o)

[kabradley]

Just checkout the Crosstable query.
Create first a select query to filter for one year and to provide a formatted YearMonth field like:
select Year([datefield]) & right("00" & Month([datefield]),2) as YearMonth, TheHeaderField, TheValueField from tblYours;
Nic;o)

Nico,
Thank you for the reply, but to be quite honest, I didn't understand anything you suggested. I know what a crosstable query is but, further than that I was lost as to what you were suggesting. If you wouldn't mind the suggestion again but with different wording I'd greatly appreciate it!
Thank you,
Allen

[nico5038]

OK,step by step:

1) Open your table and place twice the DistributionDate, the InvestmentName and the Amount
2) Uncheck the first DistributionDate (so it won't show) and change the DistributionDate into:
DistYear:Year(DistributionDate)
Now add in the Criteria e.g. 2006 to select only rows from one year.
3) Change the second DistributionDate into:
YearMonth:Year(DistributionDate) & right("00" & Month(DistributionDate),2)
This will give 200601 -> 200612
4) Change the query into a Crosstable query (See query menu or the query button)
5) Change under the Amount the "GroupBy" into Sum
6) For the Crosstable combo's use for the second DistributionDate "ColumnHeader", for the InvestmentName "RowHeader" and for the Amount "Value"
7) Save the query and run it.

Nic;o)

[kabradley]

Thank you so much for the step by step Nico. Really sorry to sound like such a novice, so I appreciate the extra help. I'll report back after getting all that setup. Thank you for the help!
-Allen

[nico5038]

No problem, keep me posted and report the step when you have trouble !

Nic;o)

[kabradley]

Alright Nico,
I got the cross tab query working, but I'm still kinda stuck as to what to do about the actual report. If you look at my first post it has a detailed explanation as to what the report needs to do. There's a link at the bottom that has a very crude sketch of how the report needs to look. I just am having a terrible time figuring out how to get access to change its reporting procedures aka, having it report the distributions *which would all have the same field name* horizontally for the particular year and THEN skip a line to the next year and do the same thing. Any guidance on this issue would be great.

[kabradley]

Well, I'm an idiot....let's just put it that way. Not five minutes after my post, I got it working! That's how it always happens right?! So that's great, the only problem now is having a default column value showing up for Jan. through Dec. because an investment will not always have a distribution every month. I thought of trying to make some vb code tied to the 'close' button on my distribution form. So that the vba would check the month numerical value that was just entered and then check the fields in the table for that particular investment and see if there were any distributions prior to that. If not *assuming the distribution entered was not January aka 1* then it would update the table inserting the value 0 for the months that didn't have a distribution. Although, this seems as though it would work, I'm positive there's a better way to do things. Any suggestions would be great appreciated!
Thanks again,
Allen

[nico5038]

Just giving you the information in steps and you're doing fine now yourself :-)

For forcing all months I use an additional table tblMonth with 12 rows holding 1 to 12 in a field Month.
Now add to the report query a column like:
Month([the date field name])

Add that tblMonth and JOIN on the Month field. Next click the join line and select the option 2 (or 3) making the tblMonth "leading".

Getting the idea ?

Nic;o)

[kabradley]

Hey Nico,
I ended up working on this again all weekend. The crosstab query is still working beautifully, but I still can't seem to get all 12 months shown if the particular investment doesn't have a recorded distribution for a particular month. From what I understood you to say I did the following:

I created tblMonth in which it had 2 columns, tableID *Set as the primary key and with type autonumber*, then column month in which I typed Jan, Feb, March, etc. I saved the table opened up the crosstab query to be used with the report and then added tblMonth. Upon adding tblMonth I created a join *3rd type* between the Month field in tblDistributions and tblMonth. I then added tblMonth.Month to the query and set its crosstab setting to column. The tblMonth.Month replaced the tblDistribution.Month.

I'm not sure if this is what you meant for me to do, but if it is it's unforutnately not working. It is quite possible though that I misunderstood your directions. Again, thank you so much for all the help and guidance.

Sincerely,
Allen

[nico5038]

Don't use an ID, nor the month names, as the crosstable query will show them alphabetically.
Use plain numbers like:
MonthForced
1
2
3
etc.
Now add to the original query in the QBE editor:
Monthforced:Month([datefield])

Join the table and the query on the Monthforced fields.

Nic;o)

[kabradley]

Hey Nico,
GREAT NEWS!!!! With the exception of making the report 'pretty' all information is correctly displayed.w00t w00t!! Just in case you were looking for a different method to fix the month problem I found this method that worked great. In the pivot clause of the SQL statement I added:
IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

Basically adding fixed columns to the query. It worked like a charm! Thank you for all of the help and assistance you have given me. I cannot convey in words what it has meant to me.

Thank you again for all of the help you saved my job!

Sincerely,
Allen

[nico5038]

Glad to assist :-)

I know the IN() solution, but prefer the table approach as it will allow for a "dynamic" fill of that table from a GroupBy query on the original column.

Nic;o)