general theory question

[Randy]

I have been working in access for about 5 years. However, most is very straight forward and with the help of this board and searches I have answered most questions.

So my issue, I need to build a small report system for our budget and planning group. At first I thought it would be straight forward, but after three attempts I am now questioning myself.

Basically I have this
Cat (defines the data, A=actuals, O=budget, C=current view etc.
Year (self expanitory)
LOB (line of business)
acct
period
value

However I really had trouble creating reports that brought in prior year value, current year value, and then comparing those values to the budget and current view.

So I tried this
Cat
Year
LOB
acct
Jan
feb
mar
etc for all twelve periods

I reloaded all the data and now I find I have an different set of issues.

So then I tried this

4 tables all identical, one for current year, one for prior year, one for budget and one for current view.

So now I am really confused. All methods seem to have pros and cons.

It would seem this would be a common application for access, so anyone out there build a basic financial application, to creat profit and loss statements by line of business want to chime in with some suggestions. I am realizing that no one method maybe best, but I am thinking there are some experienced individuals out there that might get me going down, the "better" path so I am not sitting here spinning my wheels.

Thanks for any help you can give.

 

[jeremie_ingram]

At first glance, I would say to change the YEAR to DATE in the DD/MM/YYYY format. By doing this you can calculate the period from this field, eliminating the additional field.
When you create a report, you should first generate a query that would return the select data you want and base it off of. Table structure should not have any real impact on the report generation process, since with a query you can easily combine/calculate/filter tables and data.
I guess what I am saying is that you should (in most cases) use the tables for the storage of data. Use queries to transfer/manipulate/generate/calcualte data from these tables. Base your reports and forms off of the queries and not the tables directly and you will have alot more flexibility in developing what you want.

I hope this is of some assistance.

 

[Randy]

thanks for the reply

I should have made it clear, I am basing my report off a query not off the table. I use a period field instead of a date field as that is how I get the data. Besides in my case, all dates would just be the end of the month i.e 1/31/2003, 2/28/2003 etc. so actual dates vrs a period would not really add any value.

My issue is creating an actual P&l statement.
Sales
cost of sales
standard margin
other cost of manufacturing
Gross Margin
Selling & GA costs
Corporate alloation
Income from Operations

for each LOB, for each period, with comparisons to prior year, budget and current view, with variances and percentage calculation.

The actual report writing of a P&L is more difficult than I thought it would be.

In addition, many users enter data into the applicaiton. And they want to enter as one row per record
cat
year
lob
acct
jan
feb
mar
apr etc etc

However if I create my table as
cat
year
lob
acct
period
value

I am not sure how to take the data from the form to the table. Cat, Year, LOB, and Acct are easy. But Jan (say 100.00) on the form equates to period 1 value of 100.00 etc for Feb - Dec.

I probably will have to write some sort of VBA exit routine, but was wondering if others had done this and if standard code was available that I could review and modify.

For other similar problems I have found a wealth of information, and actual code on this board.

I was just wondering if that same help could be found for this issue.

 

[Pat Hartman]

Your original table design is best since it is normalized.

Cat (defines the data, A=actuals, O=budget, C=current view etc.
Year (self expanitory)
LOB (line of business)
acct
period
value

To get the second version of the table from the first one, all you need is a crosstab query.

I think that you will find that creating crosstabs and joining the them will get you the sets of data that you need. For example create a crosstab that selects actuals for the current year and then create one that shows budget for the current year and by joining these two queries, you'll get all the buget data by month in the same record as all the actuals data by month.

 

[Randy]

thanks for comments

what happens when you have budget amounts but no actuals. or actuals but not budget. Actually my data set as all ways. Actuals with no budget. Budget with no current view. LOB in budget and current view, but no actuals. Actuals and budget for a LOB but no current view.

So if I set up three crosstab queries, do I then set up a union query based upon these three queries?

that would seem to make sense, but just wondering

Pat once again thanks for answering.

 

[Pat Hartman]

A union query will return them all as separate rows. If that works for you that's fine. You may find that a combination is actually better. You can do Left Join on Actuals to Budget and a second query with a Right join on Actuals to Budget. Then use a union query to combine the two recordsets. You'll end up with a record set like:

A - B
A - NB
NA - B

So all combinations are covered and the union eliminates duplicate rows.