Need help with Accounting Package I'm developing

[G37Sam]

Hello All,

So part of a DB I'm building for a Salon requires a little income & expenditure reporting.

The income from every hair service needs to be recorded, as well as daily & monthly expenses (rent, salaries etc..), some payments are made by cash others by cheque/credit card.

My question is how would you proceed in recording these transactions while keeping track of running balances?

What I'm currently doing is:

- A table to record account balances (cash, bank).
- A table to record the transactions, with every transaction the amount is added or deducted from its respective account (be it cash or bank)

With a little SQL on the transactions table I can output the needed statement of accounts.

Am I doing this right? I'd sincerely appreciate not turning this thread into another "buy an off the shelf solution" I want to be the solution!

Thanks for your help,
Sam

 

[Pat Hartman]

Running balances are something you do when working with a paper journal or spreadsheet. You might do them in a report but you would almost never do them in a query or form due to the inefficiency of calculating them on the fly. Obviously, storing them as you go is an absolute no-no.

When working with financial applications, there is generally a closing date for each period after which transactions are not allowed. Subsequent adjustments would need to be made in the current period. When a period is closed, you could create an ending balance. That ending balance could be used as the starting balance for the next period. You would never have a current up to the minute balance stored because you would always be in an unbalanced state. Some applications even use separate tables to segregate applied and unapplied transactions.

Storing a single account balance as you say you are doing will make the app very difficult to audit because you'll have a hard time isolating the period where the transactions don't add up. With your current setup, you would sum the transactions and if they don't = the current account balance, you would never be able to figure out where/when the problem happened.

 

[G37Sam]

Thanks for your help Pat.

Another issue I foresee running into would be when reporting transactions as depending on the transaction type, the linked table should vary to retrieve payee's info.

For example, if a transaction is a salary payment, it should link to employee's table to retrieve employee name & info whereas when it's an invoice payment, it should link to the suppliers table to get supplier info.

Is there a way to do that "on the fly"?

 

[Pat Hartman]

You could use a union query. Query1 could select salary payments and query2 could select invoice payments. As long as the two queries select the same number of columns with the same data types, the union will work for you.

 

[gemma-the-husky]

generally speaking, i would not bother trying to build an accounting package.

there are very good reasonably priced ones available.

if you are still interested though, i can give you a link to my fully featured general/nominal ledger. I don't want to market it for the above reasons. I am happy to give it away to anyone interested.

it records journals entries, including reversing journals, exports TBs, profit statements etc.

pm me, if you want it.

 

[G37Sam]

Thanks Pat, I figured that was the only way to go with it, is it good practice though?

Thanks for your offer Gemma, but I'm not really looking into something that deep. Just recording payments and income from hair services.

 

[Pat Hartman]

Union queries are a standard method for bringing data from different sources together. They are not updateable though so they are generally only used in reporting not on forms.

I also wouldn't create an accounting app. If you take Dave up on the offer, you may be able to just use bits and pieces to satisfy your requirements.