Loading Monthly General Ledger Balance Data

[StephenD]

I want to create an Access Database to analyse monthly general ledger data.
I would like to hold all monthly data (ending balances, not transactions) in
a table with the following fields.

Company
Department
Account
Jan-08
Feb-08 ....etc

The first three fields combined would represent the unique key. Each month
I would like to append a new field such as Mar-08 with that month's values.
Unfortuantely new accounts with vaules may have been created in the (now)
current month that did not previously exist.

What is the best design approach and methodology for appending each month's
data? Should I create a new field each month or just have a generic
month/year field populated with the current month year for the new data. It
is very usefule to be able to see the month's going across in the table view.

Thanks for any assistance / guidance you may be able to provide.

 

[Douglas J. Steele]

Hopefully you're not too committed to that design, because it's a bad idea.

You should not have fields named Jan-08, Feb-08 etc: that's called a
repeating group, and it's undesirable because it makes querying much more
difficult.

Check out some of the resources Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
for ideas on how to design the tables correctly.

Alternatively, you might consider purchasing an already built product,
rather than reinventing the wheel. Tony Toews lists a number of Access
Accounting systems at http://www.granite.ab.ca/accsacct.htm

 

[Jeff Boyce]

Please define "analyse" ...

Access is a relational database, great for storing data.

Excel is a spreadsheet, great for analyzing data.

What is it that you expect to be able to do in Access that you can't already
do in Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[StephenD]

,Jeff,

As an example I want to run analyses that will quickly show me a particular
expense or balance by company and/or by departmnet over time. I want to be
able to answer questions such as "Why is consolidated utility expense so
much higher this period than last period. What companies and/or departments
have contributed to this increase?"

Additionally I want to use Access to match intercompany receivables with
intercompany payables to see where they do not balance out (by company).

I currently load this data by cutting and pasting data from Excel into my
"Values" table in Access. Each month I add a new field for the current month
to the Excel spreadsheet and re-paste it into the table. The file seems to
have become too large to cut and paste. The source of this data is extracted
Oracle trial balances via text files, parsed into Excel. I find Oracle too
cumbersome to perform quick analyses.

Thanks,

Steve

 

[Jeff Boyce]

Stephen

Right off the top (and I'm sure I don't grasp the subtleties of your
situation yet), it seems to me that using a spreadsheet, using filtering,
and or using a Pivot Table (all within a spreadsheet) would represent
considerably less work that creating an Access application to do similar
kinds of things.

(but then maybe I don't get it!)

Regards

Jeff Boyce
Microsoft Office/Access MVP