VAT dilemma

[neilsolaris]

Hello,

I've got a dilemma I wondered how others here would solve.

Basically, on one of my payment queries I have a VAT field, which shows the VAT payable for suppliers that are VAT registered (VAT is sales tax, for anyone not familiar with UK tax). The problem is, if any of them deregisters, then when I run the query again it'll show the historical payments without VAT, even though that wasn't the case. I got around that by creating a field with the date they deregistered, whilst leaving non VAT suppiers blank, and VAT suppliers with a date 50 years ahead (it won't be a problem for me by then!).

This all works very well. However, if any of the previous VAT suppliers register again for VAT then some of the previous records will be incorrect again.

It's not a huge issue. It's recorded correctly in QuickBooks and in individual payment files. But it would be nice to get this working perfectly in Access. Does anyone have any suggestions?

Many thanks for your help.

[orange]

Neil,

Take a look at this link for possible solution.

You may also find this post in this forum helpful.

Good luck.

[neilsolaris]

Neil,

Take a look at this link for possible solution.

You may also find this post in this forum helpful.

Good luck.

Many thanks Orange. I'll have a read of that, and see if I can make it work for my situation.

[orange]

Here's another link same sort of info.

[Welshgasman]

I think you should be treating it like items on an order.?
One of the few times you do store duplicated items, like price of an item? as that could change later on.?
So if they deregister today, then subsequent orders are free of VAT and if they register again, those orders after that date hold VAT values?

[neilsolaris]

I think you should be treating it like items on an order.?
One of the few times you do store duplicated items, like price of an item? as that could change later on.?
So if they deregister today, then subsequent orders are free of VAT and if they register again, those orders after that date hold VAT values?

Thanks. I'm just trying to visualize how this works. Would I create a separate VAT table for each job, then enter each Vat amount for each supplier?

[orange]

Please show us your tables and relationships (graphic of relationships window). As has been suggested your transaction details would be in a lineItem/detail record. But we don't know the specifics of your tables.
By recording the info in the detail record, that will account for vendor opting in or out. Your details will provide any historic info.

KEY CONCEPT:

The current VAT status of a Vendor could be in your Vendor Table.
The VAT status at the time of a transaction(current, past or future) should be stored in the detail record. (order/purchase....)

[Welshgasman]

Thanks. I'm just trying to visualize how this works. Would I create a separate VAT table for each job, then enter each Vat amount for each supplier?

No, as orange indicates. If the supplier is VAT registered then calculate VAT, store the code if needed in the line item, at the time of creation.
If not VAT registered then VAT is 0.

[neilsolaris]

No, as orange indicates. If the supplier is VAT registered then calculate VAT, store the code if needed in the line item, at the time of creation.
If not VAT registered then VAT is 0.

I got it now. Thanks for that.

Thanks for everyone's help. I can try to upload my relationships table later Orange, but I'm not on my computer right now.

[neilsolaris]

Please show us your tables and relationships (graphic of relationships window). As has been suggested your transaction details would be in a lineItem/detail record. But we don't know the specifics of your tables.
By recording the info in the detail record, that will account for vendor opting in or out. Your details will provide any historic info.

KEY CONCEPT:

The current VAT status of a Vendor could be in your Vendor Table.
The VAT status at the time of a transaction(current, past or future) should be stored in the detail record. (order/purchase....)

Hi Orange,

Here's my relationships. So, the net payments go to the T_payments table first, and then the VAT is calculated in the Q-Totals query. In Q_Totals I use Group By, as there are often more than one payment type belonging to each supplier for a particular job.

So what I'm thinking, if I understood everyone, is that in the T_payments table, I could create a new field (lets call is VATReg for now), and if they are VAT registered for that particular job, I could enter, say a 1 in the relevant lines, and if they are not, then leave it blank or 0. Then, in the Q_Totals, if the sum of VatReg is greater than zero, then I calculate the VAT, otherwise leave it blank or zero.

Am I on the right lines?

Many thanks.

[neilsolaris]

Incidentally, if I rename PlayerCode_PK on the T_MusiciansDetails table to PlayerCode (it isn't a primary key anymore), it won't cause any problems to my reports will it (one or two of them use PlayerCode)?

Many thanks.

[orange]

Neil,

Please describe your business in simple English. I do not understand Payments nor Suppliers as presented in your graphic.

Can you take a few sample records and show us how/walk us through scenarios that highlight how all the tables relate.

[neilsolaris]

Hi Orange,

Sorry, I should have updated this thread before, but I implemented yours and Welshgasman's advice this morning and it works perfectly now. Many thanks.

[Welshgasman]

Could you mark it solved then please?

[neilsolaris]

Could you mark it solved then please?

Sure. I tried doing that from my mobile phone just now, I hope it worked ok.