Concatenating field value and text to get new field name

[William P.]

Hi all,

I am trying to create a query that will display an
employees salary. In my problem, I have 2 tables,
tblEmployeeData and tblClassifications. Each table has a
common field called Classification Code. The
tblEmployeeData table contains another field called Pay
Step, which contains a numeric value from 0-50. The
tblClassifications table has fields for pay steps, ie.
Step 0, Step 1, Step 2, etc. The tblClassifications table
looks like this:

Classifcation Code Step 0 Step 1 Step 2

A 10,532 12,847 13,700
C 15,450 16,310 17,216
TE 16,290 17,830 18,420

There is no pattern to the value increases between steps
(% or $ amount). I am trying to create a query that will
list the salary of all employees based on the
classification code and the pay step. I tried creating a
new field in my query using this method but it didn't
work: (because of the outermost braces)

Salary: ["Step" & " " & [Pay Step]]

Is there any way to concatenate a field from a record
with text and use the result as the name of another
field? I was able to create the field name "Step 0" in as
a value by eliminating the outermost braces but haven't
been able to figure out any way to use that result as the
field name.

If anyone has any suggestions I am open to your thoughts.
I would also appreciate any alternatives on how I might
set this up. Would a pivot table work in this situation?
As you can probably guess, I am not very well versed in
Access, but I am learning!

Thanks,

William P.

 

[Rick]

I would think that your classigfication table should contain three fields,
Classification Code, Step, and Rate. You could then build a query
containing both tables and simply join the two tables on the Classification
Code and the Step fields. You could then pull the employee name and the
rate.

This method seems more like normal database structure and would allow you to
get the results you want. i tested it and it looked good.

My query was...

SELECT tblTestEmplyee.Employee, tblTestEmplyee.Class, tblTestEmplyee.Step,
tblTestSalary.Rate
FROM tblTestEmplyee INNER JOIN tblTestSalary ON (tblTestEmplyee.Step =
tblTestSalary.Step) AND (tblTestEmplyee.Class = tblTestSalary.Class);

 

[William P.]

Rick,

Ahhh, I see your point. The original database was created
in filePro. I am converting it to an Access database. I
exported the data from filePro and imported it into
Excel, cleaned it up a bit, then imported it into Access.
During that process, Access created fields for the step
values (Step 0, Step 1, etc.) and I just 'assumed' that
was the correct way to setup the table. Thanks for the
clarification and getting me back on track.

My new tblClassifications will look like this:

Classification Code Step Salary

A 0 10,532
A 1 12,847
A 2 13,700

etc....

Thanks again,

William P.

 

[John Spencer (MVP)]

If you cannot restructure the tblclassifications table into three fields which
would be the best solution, you MIGHT be able to use a long choose statement or
a user defined function to get the proper value.


ClassificationCode
StepNumber
PayAmount


Salary: Choose(tblEmployeeData.[Pay Step]+1,[step 0],[step 1],[Step 2],...,[Step
50])

This could fail due to the length of the statement.

You could try a DLookup function.
Salary: DLookup("Step " & [Pay Step], "tblClassifications", "[Classification
Code]='" & tblEmployeeData.Classification &"' ")

The last two solutions might work, but they will be slow. Also the Choose
function would have to be modified if ever a step 51 was added.

Your best bet is to restructure the table if at all possible. A bit of work
now, to save a lot of headaches later.