Calculating VAT
- Thread starter SteveF
- Start date
Your invoices table is not enough for that query. Add the vatrates table to the query, and use the VAT Rate from the vat rates table. The relationships you created will link the invoices table with the vatrates table once you add it.
Been sat puzzling over this for hours and still not got it.
I'm now using a form to input the VAT rate and it's clear that, whilst the vat rate is displaying in the table, the query is using the vat rate ID as has been said and I can't see where I'm going wrong.
It won't beat me though, I will carry on reading and fiddling and will get it eventually.
Thanks for help.
I'm now using a form to input the VAT rate and it's clear that, whilst the vat rate is displaying in the table, the query is using the vat rate ID as has been said and I can't see where I'm going wrong.
It won't beat me though, I will carry on reading and fiddling and will get it eventually.
Thanks for help.
I've reattached your sample db and made some pretty quick changes.
These were the changes:
1. In the Invoices table, have a look at the Lookup tab in the VAT_Rate's field property. I changed that from Combo box to text box and that will automatically get rid of the lookup field.
2. Manually set the relationship between VAT_Rate (in the Invoices table) and VAT Rate ID (in the vatrates table) via the Relationships pane.
3. In the query, I added the vatrates table and referred to the Vat Rate field from the vatrates table to get the vat rate.
These were the changes:
1. In the Invoices table, have a look at the Lookup tab in the VAT_Rate's field property. I changed that from Combo box to text box and that will automatically get rid of the lookup field.
2. Manually set the relationship between VAT_Rate (in the Invoices table) and VAT Rate ID (in the vatrates table) via the Relationships pane.
3. In the query, I added the vatrates table and referred to the Vat Rate field from the vatrates table to get the vat rate.
Ah, gee. That's brilliant, thanks.
I had that relationship: vat_rate ID to Vat rate, but was convinced it was wrong because it was the ID number that was being calculated instead of the vat rate.
I see in the invoice table now that sites, customers and vat rates are all ID numbers rather than the names as I had them and yet the query uses the vat rates.
I don't know if I would ever have got there, I was thinking of all sorts of workarounds but totally frustrated because I knew there was an answer.
Many thanks for help. What a great forum
I had that relationship: vat_rate ID to Vat rate, but was convinced it was wrong because it was the ID number that was being calculated instead of the vat rate.
I see in the invoice table now that sites, customers and vat rates are all ID numbers rather than the names as I had them and yet the query uses the vat rates.
I don't know if I would ever have got there, I was thinking of all sorts of workarounds but totally frustrated because I knew there was an answer.
Many thanks for help. What a great forum
Yeap, that's what the Lookup field masks. It simply hides "real" relationships.
You're welcome.
Every day's a school day with this stuff, there seems to be so much going on in the background; nothings simple!
stefanrobin
New member
- Local time
- Tomorrow, 03:43
- Joined
- Dec 11, 2023
- Messages
- 14
Hello all. When calculating the gross amount from the net amount with a VAT rate of 25%, you multiply the net amount by 1 plus the VAT percentage. So, in your case, it should be:
Net Amount * (1 + VAT Percentage) = 200 euros * (1 + 0.25) = 200 euros * 1.25 = 250 euros
Your initial formula of multiplying the net amount by 1.25 is accurate and gives you the correct gross amount of 250 euros. The discrepancy in your second approach might be due to an incorrect interpretation of the percentages.
Net Amount * (1 + VAT Percentage) = 200 euros * (1 + 0.25) = 200 euros * 1.25 = 250 euros
Your initial formula of multiplying the net amount by 1.25 is accurate and gives you the correct gross amount of 250 euros. The discrepancy in your second approach might be due to an incorrect interpretation of the percentages.
Similar threads
