Seaching a Table (1 Viewer)

abinjoseph

New member
Local time
Today, 09:55
Joined
Dec 7, 2015
Messages
5
Good Day All,

I am fairly new to Access and am looking for some help. I have a table that I need to search to pull out data. The value needs to found based on salary and if it falls within a range and then all a percentage rate. I have attached some samples.

Would like to see the sql version if possible as that is what I am learning.
 

Attachments

  • Allowance Value.pdf
    80.7 KB · Views: 74

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
I think you should explain more about the data, where does it come from? How often do you refresh it. Why do you have it in that format?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
BTW, welcome to AWF.

Cheers TØny...
 

abinjoseph

New member
Local time
Today, 09:55
Joined
Dec 7, 2015
Messages
5
Its a calculation base on location. Its a benefit amount that is received based on the two factors.

1. Salary Range
2. The percentage is based on location.

The table is provided, not sure of a better way to display the table.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
So you might ask the question for a salary of $60,000 (row 13) return the value from row 13 under column 50% ??

Ans 17450
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
If that's correct then what are the MS Access names for the columns, because I doubt very much if you will be allowed to use the name 50%
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
To ask the question find the column 50% you will need to provide that information how do you propose to do that?
 

abinjoseph

New member
Local time
Today, 09:55
Joined
Dec 7, 2015
Messages
5
Yea, so the 50% is based on the location. I have a separate table that has the COLA already % by location and then the table referenced has it already linked to each individual. This is the percent that would be found in the column headings.

If I changed the headings, what would the lookup value be?

I'm excel I have it working. Two step process, but I assigned a number to where the salary falls, so each salary range has an assigned number. Then I did a separate vlookup/index to find the value.

So on the referenced table, it's a list of employees and their assigned COLA % based on where they are located. I'm trying to find a way to have that value be searched in the column headings and find the value to that individuals salary range, which are also column headings.

You basically summed it up already, but just writing it out for anyone else looking.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:55
Joined
Jul 9, 2003
Messages
13,359
The way you've got it set up now is best for Excel. To move it to MS Access you will have to do a lot of modification to the design of the tables as it will be difficult to use as it is now. So unless you're serious about moving into Access I think you might find yourself better off sticking to excel. Having said that it is possible to extract information you want with a cobbled together query, but it won't be the correct way to do it.
 

Users who are viewing this thread

Top Bottom