Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 08:00 AM   #1
KarlT
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 6
Thanked 0 Times in 0 Posts
KarlT is on a distinguished road
Microsoft Access - Stamp Duty Calculation

Hi there,
I am new to access and I am trying to build a calculator for when I am buying a buy to let property. I am trying to find the query for second property Stamp duty and failing miserably at every turn.
I have found one for excel but it make no sense to me at all and do no have a clue on how to translate it into access even if it is possible.

this is what it looks like in excel:
=SUMPRODUCT(--(B4>{125000;250000;925000;1500000}), (B4-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(B4*IF(B4>39999,0.03,0))

If you are good enough to answer or help please be gentle as I really do not have a clue and I am in at the deep end with little knowledge and understanding of terminology. Thank you in advance

KarlT is offline   Reply With Quote
Old 08-10-2018, 09:00 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Microsoft Access - Stamp Duty Calculation

looks like this is UK stamp duty to me. So the formula needs to calculate incremental stamp duty and then add 3% for non principle private residence (i.e. second home, buy to let)

first you need stamp duty tables -

tblDuty
BandBottom…BandTop...Rate
125000...……..250000...…...2%
250000...……...925000..…...3%
925000...……...1500000.....5%
1500000...……..null.........….2%

tblnonPPR
nonPPRBottom…nonPPRTop...nonPPRRate
40000...……......….Null...…….....….3%


to calculate the duty, your query will then be

Code:
 
PARAMETERS [Enter House Price] Long;
SELECT [Enter House Price] AS HousePrice, Sum((IIf([Enter House Price]>Nz([BandTop],[Enter House Price]),[BandTop],[Enter House Price])-[bandbottom])*[rate])+Avg((IIf([Enter House Price]>Nz([nonPPRTop],[Enter House Price]),[nonPPRTop],[Enter House Price])-[nonPPRbottom])*[nonPPRrate]) AS DutyDue
FROM tblDuty, tblnonPPR
WHERE (((tblDuty.BandBottom)<[Enter House Price]))
GROUP BY [Enter House Price]
Create the tables exactly as above and then copy/paste the sql to a new query
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
KarlT (08-10-2018)
Old 08-10-2018, 09:09 AM   #3
KarlT
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 6
Thanked 0 Times in 0 Posts
KarlT is on a distinguished road
Re: Microsoft Access - Stamp Duty Calculation

You have got it about the stamp duty...... bang on. would this formula work for this? example at the bottom of the of the stamp duty tax breaks.
If I purchase a second property I need to calculate the stamp duty.
£0-£39999 is 0% of purchase price in Stamp duty
£40000 - £124999 is 3% of purchase price in Stamp duty
£125000-249999 is 5% of purchase price in Stamp duty
£250000-924999 is 8% of purchase price in Stamp duty
£925000-£1499999 is 13% of purchase price in Stamp duty
£1.5 over is 15% or Purchase price in stamp duty

so if I am to purchase a property at say £150000 the calculation is 125000 * 3% (3500) + 25000 @ 5% (1250) so the total stamp duty to be paid is £5000

I hope this makes sense? thank you again

KarlT is offline   Reply With Quote
Old 08-10-2018, 09:32 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Microsoft Access - Stamp Duty Calculation

don't see why not, keeps it in one table - just doesn't follow the calculation banding shown at gov.uk

But you need to make sure you have your break points correct - you are missing £1 between the top of one band and the bottom of the next. Since the smallest coin is 1p you will need to use £124999.99 and perhaps need to change the formula from using > to >=
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
KarlT (08-10-2018)
Old 08-10-2018, 09:41 AM   #5
KarlT
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 6
Thanked 0 Times in 0 Posts
KarlT is on a distinguished road
Re: Microsoft Access - Stamp Duty Calculation

Ahhhh, yes you are right about the banding........ this is not going to be easy if I cant even get that bit right....lol.
Again thank you. I thought it may have been a little more simple. I have created a some tables already but I am sorry I do not fully understand the tables and code.. I think my friend may, I will ask him to help formulate it into my calculator. Again thank you
KarlT is offline   Reply With Quote
Old 08-10-2018, 10:06 AM   #6
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,155
Thanks: 0
Thanked 690 Times in 675 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Microsoft Access - Stamp Duty Calculation

Use a rate table,and a query.
Ranman256 is offline   Reply With Quote
Old 08-10-2018, 10:51 AM   #7
KarlT
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 6
Thanked 0 Times in 0 Posts
KarlT is on a distinguished road
Re: Microsoft Access - Stamp Duty Calculation

CJ_London, I think I might be getting a nit of this into my tied thick skull. What code needs changing if I am to use only the one table?
thank you in advance......feeling like I might get this.

KarlT is offline   Reply With Quote
Old 08-10-2018, 02:18 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Microsoft Access - Stamp Duty Calculation

the code I provided uses both tables
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
KarlT (08-11-2018)
Old 08-11-2018, 02:36 AM   #9
KarlT
Newly Registered User
 
Join Date: Aug 2018
Posts: 6
Thanks: 6
Thanked 0 Times in 0 Posts
KarlT is on a distinguished road
Re: Microsoft Access - Stamp Duty Calculation

Thank you CJ_London.... I am very new to this and this might actually be beyond me... so sorry for the lack of understanding. First question - how do I get Null? and the second, Can I not have it all in one table and if I do does the code still work unchanged? Again thank you very much, you are helping a very tortured head right now
KarlT is offline   Reply With Quote
Old 08-11-2018, 02:48 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Microsoft Access - Stamp Duty Calculation

you have cross posted here

http://www.accessforums.net/showthread.php?t=73283

and been advised of the etiquette. You have answers on the other thread, so suggest you continue on the other forum.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
KarlT (08-11-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[Microsoft][ODBC Microsoft Access Driver] Too many client tasks. juanvan General 10 05-15-2017 08:12 AM
Question Link Microsoft Outlook Contacts to Microsoft Access Mohsin Malik General 3 06-13-2016 12:05 AM
Microsoft Access is redundant technology and no longer supported by Microsoft Groundrush The Watercooler 17 06-23-2012 11:14 PM
Microsoft Access 2007 Percentile Calculation SachinP Queries 0 05-21-2009 10:00 AM
Question Microsoft Works Database v. Microsoft Access akt01 General 10 02-02-2009 03:01 PM




All times are GMT -8. The time now is 11:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World