Tax Band Query Statement

[paulS30berks]

I am trying to convert an excel formula into a new access query statement.

The statement itself will refer to a tax percentage column within the same query.

Example of excel formula:

=IF(Tax Percentage>22,40,IF(Tax Percentage>10,22,IF(Tax PercentageN2>0,10,0)))

The output I hope to achieve is:

If Tax Percentage is greater than 22 then = 40
If Tax Percentage is greater than 10 but less than 22 should equal 22
If Tax Percentage is greater than 0 but less than 10 should equal 0.

Hope this makes sense?

thanks

 

[stepone]

=IF(Tax Percentage>22,40,IF(Tax Percentage>10,22,IF(Tax PercentageN2>0,10,0)))

Hi there - this should pretty much be the same in an Access query, except that you shouldn't have any spaces in a field name, so call it "taxPercentage". If you can't change the name, then just put square brackets around it - [Tax Percentage].

Also, in Access the syntac is "IIF", rather than "IF", I think.

Cheers,
StepOne

 

[neileg]

I can't make any sense of why you want to do this. The tax bands only have discrete steps (as you know) so I don't understand why you are testing like this.

 

[paulS30berks]

Following on from this statement. I wondered if you could advise me of something else.


Surname Month Session Number Tax_YTD Year

Employee x 12 2 1200 2004
Employee x 12 3 1450 2004
Employee y 12 2 1800 2004

I am pulling data from a table that has details on temporary employee's tax payments on a weekly cycle. Weekly payments are divided up into session numbers so for example:

Each week a Tax_YTD column will accumulate tax paid for the year.

What I am after is total Tax_YTD for 2004 each employee. However some employees are only paid in session_number 2, some in 3 and some in both for December.

If I wanted to write a query statement to say:

If Employee X was paid in session_number 3 - take session 3 only (not 2 aswell)
If Employee X was not paid in session_number 3 take session_number 2.

Hope this makes sense?

Thanks

 

[stepone]

Hi there,

First set up a simple query, to show Employee ID, Session number and Tax_YTD. Sort it by EmployeeID , and Session number (ascending).

This will give you all sessions for each employee - to stop it doing this you want it just to output the last record for each employee. So, select View-> Totals to see the Totals row in the query design. Set the Employee ID field to "Group By" and the other 2 fields to "Last". Run the query - it should display one record per employee.

I think this will give you what you want,

cheers,
StepOne

 

[paulS30berks]

Sorry me again!!!

Have experienced some problems with the different types of data in various tables and have had to look at a different angle.

would it be possible to base the number of months paid based on start dat and leave date?

So for example:

Start Date = 01/04/2003
Leave Date = 00/00/0000

Therefore 12 (months)

Start Date = 01/04/2003
leave Date = 01/08/2003

4 months?

Hope thhis makes sense

Thanks

 

[paulS30berks]

The results I have achieved using the queries we have worked on assumes that each employee has worked the full financial year.

For example:

Start Date Leaving Date
------------ ---------------
15/09/2003 19/12/2003

There is no way of calculating an employee's length of time within the company if they start and leave during the financial year. Is there a way of calculating by number of months within the company based on start and leave date?

Thanks

 

[Rich]

Look up theDateDiff function

 

[paulS30berks]

Thanks for your reply.

I have tried to calculate the difference in months between Start Date and Leaving Date:

months paid:datediff("mm",[Start Date],[Leaving Date])

 

[Rich]

it's datediff("m" and what problem are you getting, a little more detail would be helpful

 

[paulS30berks]

Can anyone help?

I am using the following expression which assumes:

Start is start date field name (be sure to use the actual field name)
Leaving Date field name (ditto)
Current employees have a blank end date
All employees have a start date

To return the months of service for one year at a time such that the maximum value = 12.
Any part month is seen as 1 month (e.g. start on 20/05/04 and end on 25/05/04 is seen as 1 month)
Employees who start after the end of the financial year and employees who leave before the start of the financial year have 0 months service.

Beginning of Financial Year ( prompted to enter this, e.g. 01/04/03
EOFY = End of Financial Year (prompted to enter this, e.g. 31/03/04,


MthsService : IIf([Leaving Date]<[Beginning Of Financial Year] Or [Start Date]>[End Of Financial Year],0,DateDiff("m",IIf([Start Date]>[Beginning Of Financial Year],[Start Date],[Beginning of Financial Year]),IIf([Leaving Date]<[End Of Financial Year],[Leaving Date],[End Of Financial Year]))+IIf(Day(IIf([Leaving Date]<[End Of Financial Year],[Leaving Date],[End Of Financial Year]))>=Day(IIf([Start Date]>[Beginning Of Financial Year],[Start Date],[Beginning Of Financial Year])),1,0))

However this returns total number of months since start date.

Thanks