Question on Design?? (Access2K/Win2K)

Topic

New Reply

I have a sales/marketing database for my business well under construction. I have it entering orders, prinitng out invoices via Word etc. All working to plan.

I have a table to hold data of expenses/invoices paid, i.e. suppliers, shipping etc. tblInvoices holds data like to whom, date recd, date paid etc. A linked table, tblInvoiceDetails hold details of Expense Catorgory, amount etc. so i can split any invoice e.g. I pay the post office for shipping and i buy supplies.

My question relates to Income. Should i take the easy way out and flag each order when it is paid?? Or should i try to record the data a little like the Invoices paid structure??

Is there an accepted data model for an accounts system?? I guess if i get it right the database could also reconcile my company bank account too..

Reply | Quote

Replies

Actually there are a number of commercial and some shareware/freeware accounting packages based on Access. In addition there are numerous custom applications that include an accounting module much like what you are considering. You might want to visit Tony Toews webpage where he has considerable information on accounting systems based on Access – the URL is http://www.granite.ab.ca/accsacct.htm%5B/url%5D.

By all formal accounting standards, you should use a double-entry journal system, which complicates things more than a little – if you do that you should handle income as a separate transaction, rather than simply marking an invoice as paid (which is what we do in our invoicing system – but it doesn’t try to do any real accounting). As far as an accepted data model, it is pretty well defined by accounting standards, though when you look under the cover, you may find fairly wide variations. One of the systems I worked with used a single table for customers, suppliers and employees, while another put each in a separate table. Hope this helps.

Reply | Quote

Actually there are a number of commercial and some shareware/freeware accounting packages based on Access. In addition there are numerous custom applications that include an accounting module much like what you are considering. You might want to visit Tony Toews webpage where he has considerable information on accounting systems based on Access – the URL is http://www.granite.ab.ca/accsacct.htm%5B/url%5D.

By all formal accounting standards, you should use a double-entry journal system, which complicates things more than a little – if you do that you should handle income as a separate transaction, rather than simply marking an invoice as paid (which is what we do in our invoicing system – but it doesn’t try to do any real accounting). As far as an accepted data model, it is pretty well defined by accounting standards, though when you look under the cover, you may find fairly wide variations. One of the systems I worked with used a single table for customers, suppliers and employees, while another put each in a separate table. Hope this helps.

Reply | Quote

Record payments in a payments table. You can use the invoice key as a foreign key in the payments table, but invoices and payments are two quite different things and just marking something as “paid” doesn’t provide for partial payments, refunds, returns, etc. Double entry bookkeeping isn’t as hard as it looks but it can be confusing if you aren’t well grounded in the basics. You probably would be better off following Wendell’s advice and finding something already built for that purpose.

Reply | Quote

Record payments in a payments table. You can use the invoice key as a foreign key in the payments table, but invoices and payments are two quite different things and just marking something as “paid” doesn’t provide for partial payments, refunds, returns, etc. Double entry bookkeeping isn’t as hard as it looks but it can be confusing if you aren’t well grounded in the basics. You probably would be better off following Wendell’s advice and finding something already built for that purpose.

Reply | Quote