By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 800 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Access (2007) - Creating a query which changes wages depending upon age

P: 2
Hello Everyone,

I'm having abit of difficulty with an aspect of Access. I wish to create a query which changes a persons wage depending upon their age. This is what I have done so far:
Expand|Select|Wrap|Line Numbers
  1. SELECT Tble_Staff.[Unique Staff Number], Tble_Staff.[Age], Tble_Staff.[Age] AS Normal_Wage
  2. FROM Tble_Staff
  3. WHERE IIF([Normal_Wage]=12,13,IIF([Normal_Wage]=13,13.34,IIF([Normal_Wage]=14,13.78,IIF([Normal_Wage]=15,14,IIF([Normal_Wage]=16,14.50,IIF([Normal_Wage]=17,14.70,IIF([Normal_Wage]>=18,15.60,"Invalid Number"))))))); 
  4.  
Now, I have the feeling that this doesn't work due to the fact that I utilise the [Normal_Wage] variable twice. However, I am unsure as to how to avoild this.
Any help or suggestions would be greatly appreciated. I need to get this done ASAP.

Thanks,

Edward Gorman
Aug 22 '10 #1

✓ answered by liimra

Dear Edward,

The above mentioned statement won't resolve Null & Unspecified values but this will
Expand|Select|Wrap|Line Numbers
  1. SELECT Tble_Staff.[Unique Staff Number], Tble_Staff.Age, IIf(Age Is Null,"Invalid Number",IIf([Age]=12,13,IIf([Age]=13,13.42,IIf([Age]=14,13.78,IIf([Age]=15,14,IIf([Age]=16,14.5,IIf([Age]=17,14.7,IIf([Age]>=18,15.6,"Invalid Number")))))))) AS Normal_Wage
  2. FROM Tble_Staff;
  3.  
Regards,
Ali

Share this Question
Share on Google+
3 Replies


100+
P: 119
I think you are creating circular error. Anyways Assuming you have table named "Tble_Staff" and two Fields "Unique Staff Number" and "Age". Here is the Code and it works,

Expand|Select|Wrap|Line Numbers
  1. SELECT Tble_Staff.[Unique Staff Number], Tble_Staff.Age, IIf([Age]=12,13,IIf([Age]=13,13.34,IIf([Age]=14,13.78,IIf([Age]=15,14,IIf([Age]=16,14.5,IIf([Age]=17,14.7,IIf([Age]>=18,15.6,"Invalid*Number"))))))) AS Normal_Wage
  2. FROM Tble_Staff;
  3.  
Regards,
Ali
Aug 22 '10 #2

100+
P: 119
Dear Edward,

The above mentioned statement won't resolve Null & Unspecified values but this will
Expand|Select|Wrap|Line Numbers
  1. SELECT Tble_Staff.[Unique Staff Number], Tble_Staff.Age, IIf(Age Is Null,"Invalid Number",IIf([Age]=12,13,IIf([Age]=13,13.42,IIf([Age]=14,13.78,IIf([Age]=15,14,IIf([Age]=16,14.5,IIf([Age]=17,14.7,IIf([Age]>=18,15.6,"Invalid Number")))))))) AS Normal_Wage
  2. FROM Tble_Staff;
  3.  
Regards,
Ali
Aug 22 '10 #3

P: 2
Thank you very much liimra, I really do appreciate this.
Regards,
Edward
Aug 22 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.