creating a Profit and loss report for budgeting group

[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.

All reports are based upon a query. Some are just a
select query, and a couple I tried a crosstab. None of
these seemed to be able to do it all.

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.

 

[Phil]

Hi, I am just a normal user of Access. I am still using
Access 97 but I have done something similar to your
situation (current / prior / budget ). I am sure there
must be a faster way but the following way is the way i
used in my project.

1. Create a table with accounting period / month (1-12)
2. Separate table for Actual / budget / prior.... and
each table should have a field for accounting year and
accoutning period.
3. Accoutning year field is to determine what year is
current and prior
4. Use left-joint or right-joint query (not sure about
the name). The relationship should be started from
Accouting period table to all other individual tables.
One separate relationship between accounting period table
and individual table. The relationship should include
everything in accounting period table and anything matched
in individual table.
5. The query should show you by accounting period (row)
and all current/actual/ budget (columns).

Hopefully, it can help.

Thank you.