Accounting Reports in Access

[Sarah]

I'm writing a database for a property management company, and I'm trying to
put together individual account sheets that perform regular accounting
calculations needed to show monthly balance/etc. However, what I'm running
into is the Access calculations and formulas are too complex, and do not work
like they would in Excel, where it's easy to put something like this together
with their formula functions. How do I do this?

Basically, I've got all the information entered into a table and I have a
query written to pull out individual accounts, but when I go to put those
into a report that does the calculations, I can't get it to work properly.

 

[Marco]

If you have a lot of complicated formulas in excel. Then link access to excel.

 

[Sarah]

So how do I do that? I've thought of that, but I have to keep a running
balance forward, that links through several different records by account
number. I can put all that into excel and create a sheet that does that, but
how do I do it in Access Does it work the same way as it does in excel?

 

[Marco]

When you link access to a file like txt or xls then when you have the access
open you can't open the xls.

I think that you should create a intermediate excel file just for the
results. and then you link access to that intermediate file.

to link you need to go to File»Get external data»Link

Marco

 

[Albert D. Kallal]

It's not clear what you mean by that the calculations are too complex.
Perhaps you're talking about extremely complicated commission rates, or
perhaps you doing some kind of simplex algorithm and calculation on stock
market values to come up with a type of bid for a stock.

however, if you're just talking about some type of classic ledger sheet who
we are entering account numbers and amounts, then I assume they approach
could be as follows:

you could build a report that groups by teach organization.

I would then build a sub report that has all of the expenses for that
organization.

I would then build a sub report that sums up all of the payments made by
that organization..

you could then have your report gave you the difference between the two
values.

and do not work
like they would in Excel, where it's easy to put something like this
together
with their formula functions.

If it's so easy in excel, then why aren't you using excel? Furthermore, last
time I looked there are several well known accounting packages in the
marketplace, and none of them are built and based on excel, but there is a
good number of accouting packages based on MS access. The very popular
simply accounting package is in fact based on MS access files. The folks at
simply accounting of course changed the file extension, but you can in fact
open simply accounting files directly with MS access.

I guess I'm pointing the above out, because MS access is a different beast
than that of excel, and your traditional approaches in excel that you used
will not work in MS access, and they won't work also in PowerPoint either.
The old saying about if you view every problem as a nail, then the only tool
you're going to want to use is a hammer.

Basically, I've got all the information entered into a table and I have a
query written to pull out individual accounts, but when I go to put those
into a report that does the calculations, I can't get it to work properly.

I suspect that you actually need a few tables here. I would assume you have
a table of organizations, this will allow you to have to contact
information, phone numbers, things like mailing address etc. Each
organization only needs to be entered ONCE into the system.

I would then probably at a table of payments made, and then another table of
expenses or cost items. (Or perhaps just make this one table, and have a
column called credit, and debit, and also other details such as was a paid
by check, visa etc...and of course a payment date).

So, at this point we're up to 2, or 3 tables already.

The current balances owing can be calculated by taking the difference
between payments made, and expenses (debts) for that particular customer
(account).

You could also take a look at the sample accounts ledger database at the
template library of Microsoft, but keep in mind these templates can be quite
simple examples:
"Accounts ledger database can be found here:"
http://office.microsoft.com/en-us/templates/TC010175341033.aspx?pid=CT101426031033&WT.mc_id=42
At the end of the day, I think it really comes down to how you actually plan
to enter this information into access. If the whole thing is just one big
table values that you're entering in over and all over, and you're copying
things like company information over and over, then this is not a data
normalized design. You might just as well stick to excel. However even in
these cases MS access generally a better reporting tool because it can
generate totals by each account for you.

Also, how you build and design the interface for the users to use this
application, is another issue you need to consider. For example, if all
payments or debts to a particular organization are always done at the same
time, then you'd build a form to display that organization, and enter the
numbers into a sub-form. This design would work well if you're dealing with
one client on the phone at a time for example. This design also means that
if you have to enter 4-5 items for that one company, then you'll not even
have to type the vender account number over and over again.

On the other hand, if your given a pile of receipts from all kinds of
different organizations in a big box at the end of the month, then you're
dealing in doing data entry for one different organization right after
another (or even better yet simply a one different account number after
another). In this type of scenario I would suggest that you build a ledger
style sheet that allows you to enter the account number, and then the
pertinent details (however, I still to suggest for rapid data entry that you
do build a table of organizations and their particular account number - the
advantage of this is you will NOT be able to enter illegal account numbers
during data entry). The other reason why I'm suggesting one table of
organizations (or account #) is because then that single defined list of
legal organizations (or legal account#) also becomes the source for your
grouping options in your reports.

The beauty of a relational database system is then you can say give me
totals for all of the account# (or organizations), and this tends to be a
lot easier in MS access to them that of using excel. And if done right, it
also tends to be less data entry work then using excel...

 

[Sarah]

Albert;

Thank you for your response, it was helpful but also raised new issues.

Originally, this was an Excel sheet. However, as we looked at the
information we needed to track, it is not fluid enough to handle the amount
of information we need it to handle. Further, Acces gives us the ability to
handle all of our information in one place, where now it is scattered all
over.

Basically, here's what I'm trying to do:

I have designed a table for tenant information, that has all contact info,
etc., in one place. This is referenced by the account number our
headquarters assigns to each tenant. I have also put together a table for
other subsets of information, like tenant violations and vehicle storage,
etc. What I've got left is the accounting side of things. As it stands
right now, I have one table set up to handle all that data- there are fields
for the charges, identifying the purpose of the charges (rent, utilities,
etc.), how the charge is paid, when they are paid, when they are deposited.
This is linked to the main information table using the account number
assigned by our headquarters, but each transaction has a separate transaction
number. I've written a query to bring up a particular tenant's transactions;
my problem is that I cannot figure out how to turn those transactions into
the accounting data we need. The formulas are really pretty simple, but I
had so many calculations I was trying to do within one record, it didn't
work. So I simplified it a bit, by changing the table to do only thing at a
time- for instance, posting charges due, then using another record to post
the amount paid. It's all in the same table. But I'm still having the same
problem- how do I get the report to generate balance information? The
formulas I've been trying aren't working.

After reading your explanation, I'm starting to think I might have too much
information in that one table, and it needs to be broken down even further-
to separate tables for charges and payments. But I still don't know if that
will work at all, either.

I don't know if this clarifies for you what I'm trying to do, but hopefully
it will give you a clearer picture of what I'm attempting. Thanks for your
help!

 

[liew.derek]

Hi Sarah,

I think you are trying to design and develop an accounting system for
your property management company. To make things clearer to you, let
me brief you on some of the fundamental elements and concepts in
creating accounting reports.

you have to create a table for a chart of account, that contains
account no,description, account category (asset/liability/Revenue/
cost), a Parent table to store transactional posting journals, and a
group of table connecting to it. these table will represents your
individual child table that normally resemble each of your accounting
modules, namely, your inventory, AR, AP modules.

Now, in your parent table, you need to have columns to store data such
as, journal no, account code, amount, description, period and year.The
trick here is, your debit and credit amount is stored under one column
(amount), conforming to conventional accounting double-entry rules.The
period and year columns, acts as your filtering tool, you can pull
data for any period in any particular year. Most importantly, you can
generate an opening balance for last year, with currrent year
transactional journals having displayed as breakdown. The trick in
generating whatever accounting reports, is using your creativity and
skill in your sql scripting.

All the Best to you!