Seaching a Table (1 Viewer)

[abinjoseph]

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.

 

[Uncle Gizmo]

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]

BTW, welcome to AWF.

Cheers TØny...

 

[abinjoseph]

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]

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]

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]

To ask the question find the column 50% you will need to provide that information how do you propose to do that?

 

[abinjoseph]

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]

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.

 

[Uncle Gizmo]

This is a rough idea of what you have to do:-

Convert Excel to MS Access

More information about process and a free tool to help you do it here:- Excel in Access