Accounting in Access

[Parker]

Accouonting in Access

Hi

Having developed a db that my staf are pleased with I now need to develope this into somthing that helps me.

I already have a way of working out what each client is charged for each job we do for them. And a way of entering payments they have made --- but only against individual jobs.

I need to be able to do some basic accounting type stuff like.

Clients
Invoice for all outstanding jobs on a clients account
Apply payments to a clients account insted of to each job
Raise invoices (usually twice monthly, but anyway on demand)
Issue Account statements once a year


Staff
Some employees are paid by the job (each job is payed at a different rate)
SSome staff are payed by the hour
Some staff (if payed at all) are payed on a salary scale
Some staff are payed on commision
And some staff get a combination of different pay types.

Suppliers
I supose we need a way of paying them

And, last but not least, Inland revenue (PAYE & NI) and VAT office.
Do all the normal end of year stuff.

Any ideas on structure and design would be a great help or sample db's for me to look at would also be great

Or if there is an accountant/access guru browsing "Help!" I know a little about Access but the only thing I know about accounting is that my accountant charges me too much

Thanks

 

[The_Doc_Man]

OK, a thought or two. You are using Access based on a model of the way you did business, but you are finding that the model is incomplete. Basically, each table in a business-model app represents a resource, an element, or an object in that model.

You talked about jobs. This implies that your app must have a table dedicated to facts about jobs. Because a job is part of the model you discussed.

To take the next step, if you have repeat business from clients and you want to track that, you need a separate table dedicated to clients. A field in the jobs table has to point to the client for whom the job was performed. This is the sort of thinking you need to employ when modeling a business.

Then, to accumulate client payments, you must have a payment table. Perhaps a field in this table also should point to a client record so you know who paid you.

But then you start talking about a running balance due from each client across multiple jobs. This implies that you have to track debits and credits (or charges and payments) and balance them against each other. That way, you know how much each client has paid and how much they owe you.

The way you usually do this involves a charges table that links to jobs (and the jobs table links to clients so you don't need a separate pointer in the charges table). You also have a payment table that perhaps only needs to link to clients. Two tables.

You get a running balance by forming the union of the two tables where the union-query record includes the date of the item (job billing or payment), a code for what it is (bill/payment), the client code (direct from the payment table or indirect from the charges table), and the net amount. Balance due would be one sign, payments would have the opposite sign. The union query can manage that with a simple expression. Then sort the union query by client and then by date to get a running balance for the client's statement or your own records.

 

[Parker]

The_Doc_man
Thanksfor your reply

Yep you seem to have grasped the situation. It is a business and Ive been on a mission now for over a month to improve our data handling

Some of the process you describe are already in place. I have included a screen shot of the relevant section of the relationship table --- as you can see the elements you refer to are there but you lost me somewhere around union query.

Know nothing about this concept. have had a quick look at help files but (as usual) not much help I'll look for info in the forums but any help you can give me would be met with truck loadsa gratitude.

 

[Thecherub]

Oh my lord

That must be one fantastic Data base my hat is off to you i would not even attempt to design something like that. I'm pretty good at the whole access thing but for accounting of that magnitude i'd just buy an accounting package. it would do all you wish without the design headaches.

 

[Parker]

Trouble with accounting packages is that (like most business software) they don't take into account the way a small and bizzar business needs to work.

As I see it data bases work on logic right?
So, if you store all the information correctly then you should be able to manipulate it to get what you need when you need it. Yes?

So if I have developed a perfectly good db for storing information on clients, jobs for clients etc, etc, along with suppliers etc, etc, then why do I need to go out and buy another db plus all the add on bits for doing wages, VAT, corporate finace etc.,
(cause thats basically what comercial accounts pkgs are -- db's) to handle the finantial side?

We do that now and the dupplication of information is huge causing the same data to be entered into 3 different pieces of software. A complete waste of time.

I know it is possible to do what I want just need help to figure out how best to go about it

Yes it is a mamoth project but phase one is almost completed, the db is up and running and, thanks to some clarity of thought from The_Doc_man last night, phase two is now under way.

I did not know before what a "Union Query" was but on looking at it and searching the forum I have found out that I have already created union queries in my db just didn't know they where called that.

As for design headaches I believe that they are only ever caused by two things

(1) The inability or lack of knowledge from the designer to understand what the project actually is.

(2) The inability from the designer to understand the logic & the language behind the system they are attempting to use to make it happen.

Any of these can be overcome with enough determination.

I AM DETERMINED

Thanks for your post -- I take what you said as a complement, especially as I am a newcommer to both Access and progrming.

Maybee, as you confess to being pretty good at it you would help me try to understand some of the processes that I need to go through. After all if you break it down it is only a large number of small problems ---- not one big one as your post would sugest.

Cheers

Pete

 

[Thecherub]

I'll try my best

I'll be glad to help in any way I can but accounting practices vary from Country to country. I might be of some help since Canadas GST is basily a VAT but some other things might be wildly Different.

TheCherub

 

[Rich]

Don't forget to account for Bad Debt

 

[Parker]

That would be fantastic thanks.

One place I really do need help on at the moment is aditional tables and there link stucture. Me Brains going numb!

I think I already have the necessary tables for billing clients (look at my Link in a prev. post this link is not a definative pic there are some 36 tables, some of them not acctually developed yet and most of them linked in some way) I just have to develope the neccessary queries, reports etc to make that happen.

From that we will have a record of income.

But we also need to keep information on our expenditure so we need to decide on what information is necessary and where to put it.

This is maybee not as simple as it sounds because expenditure also includes monies we have to pay to VAT, Inland Revenue, PAYE and National insurance etc.

At the moment I have all contacts including clients, suppliers, Employees, banks, IR etc., all in one table Might it be better to split this table?

 

[Rich]

The Vat should be calculated from your invoice table, use a Union query to combine the other expenses

 

[Thecherub]

"At the moment I have all contacts including clients, suppliers, Employees, banks, IR etc., all in one table Might it be better to split this table?"

I think so, especialy employees they should be on one seperate table.

 

[Parker]

Don't forget to account for Bad Debt

Thanks Rich

That one may cause some good coding --- We don't have too many of them but we have a unique way of dealing with it -- I feel a lot of IF's, when's and else nested subs comming on for that one.

I suppose the last one might go somat like

Else pymentsstring <0 then
MsgBox "Send In The Boys"

End If

 

[Parker]

The Vat should be calculated from your invoice table, use a Union query to combine the other expenses

Dont have an invoice table yet.

See this is exactly what I need first. A structured list of the tables necessary for this project.

Thanks

 

[Uncle Gizmo]

Some links for you…

http://www.granite.ab.ca/access/advantages.htm


http://www.sybiz.com.au/pebble.asp


You need to make sure forign Accout packages are compatible with UK regs

 

[Parker]

Thanks Tony

I will take a look.

I have looked before at Access based or Access intergration accounting software but have found that either it dosn't comply and needs so much poking that you may as well start from scratch or it is'nt as capable as it should be but I will take a look at your links.

 

[Parker]

For those of you interested here is a list of the tables that I have already

List of tables

Airports (Relevant info on location contact etc)
Attend (Chauffeurs Availabillity)
Attendance type (linked to Attend)
BillingAddress (linked to customers)
Calls (Phone call log)
Chauffeurs
Company Information
Contact types (related to Customers)
Customers
Discount (level & type of discount awarded to clients accounts)
Event Type
Flights (details of incoming and out going flights from the UK)
Fuel (type of fuel – related to vehicles)
Job Type (Related to Orders)
Market research
Order Details (Related to Orders)
Orders
PA (Plans to split of PA info from customer tbl)
Payments (related to Customers & Orders)
Payment Methods (related to Payments)
Pick Up (Collection Address/Point; related to Orders)
Products
Terminals (related to Airports)
Transmission (related to Vehicles)
Vehicles

Jobs (established from queries – This table is used to display single line information on ongoing jobs and is appended each time a new job is added to the system: the display is on a separate Screen a bit like an airport info board)


I've not included full descriptions or full list of links due to the space it would take up but you should get a basic idea