possible table setup problem due to vat

i have been writing an app for my work for 18 months now, in a fairly
organic manner! my boss has never used a computerised system and i
have been learning access along the way.

we are a tyre/exhaust garage and the app will cover buying stock,
invoicing customers, vat returns,, monthly statements for account
holders etc etc. most of it is done and i am happy with it.

the only thing causing a problem now is 1 suppliers method of
invoicing. i have already solved the problem of some suppliers
method of vat calculation being slightly off by entering the vat once
the invoice is recived as a figure rather than calculating it.

basically what happens is this. we receive an advice note when
products are delivered, this is entered on the system on a form/
subform (tblacq/tblacqdetail). when we recieve the invoice (from all
bar this one supplier) each invoice exactly matches one advice note,
so that record is updated with vat amount, tax point and invoice
number. all straightforward and easily done.

this particular supplier provides us with an advice note for each
delivery but rather than a separate invoice for each advice note they
send one invoice every fortnight covering each and every delivery.
the problem with this is they calculate vat on the total so i cannot
enter the vat amount for each advice note.

Any ideas?

Re: possible table setup problem due to vat

1) You could divide the TOTAL INVOICE VAT by TOTAL INVOICE PRICE
(including Vat)

2) You will then end up with a RATIO (the answer of 1).

3) Multiply the ITEM PRICE by the RATIO, and you get VAT for that
Item.

I think my maths is right ?

Anyone want to back me up here ?


Groovo

Re: possible table setup problem due to vat

There may be a good reason why you have decided to grown your own
rather than buying an accounting package such as Quickbooks which will
do everything you mention in your post, and the payroll too. However,
where VAT is concerned the VAT officials can get rather picky and you
may feel that it would be safer to use a package that is approved by
the powers that be. If there are particular aspects of what you have
designed that don't fit easily into an accounting package, it might be
worth considering keeping those within your database but moving the
accounting stuff into an accounting package. This should have the
effect of reducing your boss's audit and payroll costs as well as
making the whole tedious business of end of year returns a whole lot
simpler. I appreciate that this doesn't actually answer your question
and is only offered from the "been there, done that and wished I
hadn't" point of view. If the advice is unwelcome, just disregard it