Select Record Based on Column Name

  • Thread starter Thread starter Jerry Baker
  • Start date Start date
J

Jerry Baker

I need to construct a query that works something like this:

SELECT COLUMN(x) FROM TABLE
WHERE ROW1 = 1

The part I can't figure out is how to select a particular column whose
name I don't know ahead of time. What I am wanting to do is look up a
value in one column on a particular row and the column can be different
for each iteration.
 
I need to construct a query that works something like this:

SELECT COLUMN(x) FROM TABLE
WHERE ROW1 = 1

The part I can't figure out is how to select a particular column whose
name I don't know ahead of time. What I am wanting to do is look up a
value in one column on a particular row and the column can be different
for each iteration.
Click to expand...

The only way to do this would be to build the SQL of the query
dynamically in VBA code.

The NEED to do this very strongly suggests that you're storing data in
fieldnames - i.e. that your table structure is improperly normalized!
Could you describe the structure of the table and the rationale for
this query?

John W. Vinson[MVP]
 
John said:
The NEED to do this very strongly suggests that you're storing data in
fieldnames - i.e. that your table structure is improperly normalized!
Could you describe the structure of the table and the rationale for
this query?
Click to expand...

I have two tables: One is a table of employees where one of the columns
indicates which "pay step" they are on. The other table is a schedule of
salaries where each row is for a certain position, and each column is
a step. I need to be able to store an employee's step and then look up
their pay level.

EMPLOYEE TABLE
---------------
EMPID STEP POSITION
1 3 A
2 4 C
3 2 C
4 1 B
5 3 B

PAY SCHEDULE TABLE
--------------------
POSITION STEP 1 STEP 2 STEP 3 STEP 4
A $1000 $2000 $3000 $4000
B $1670 $2212 $2766 $3345
C $1233 $1677 $2289 $2561

I need to look up the step from the employee table for a given employee
and then look up the pay that equates to that employee's position and
step level in the pay schedule.
 
PAY SCHEDULE TABLE
--------------------
POSITION STEP 1 STEP 2 STEP 3 STEP 4
A $1000 $2000 $3000 $4000
B $1670 $2212 $2766 $3345
C $1233 $1677 $2289 $2561

I need to look up the step from the employee table for a given employee
and then look up the pay that equates to that employee's position and
step level in the pay schedule.
Click to expand...

This table is the culprit; as I suspected it is incorrectly
structured. "Fields are expensive, records are cheap"!

Consider using a properly normalized tall-thin table with fields
Position, Step, and PayRate, with records like

POSITION STEP PAYRATE
A 1 $1000
A 2 $2000
A 3 $3000
A 4 $4000
B 1 $1670
B 2 $2212

and so on. Join this to your table by Position and Step.

John W. Vinson[MVP]
 
John said:
POSITION STEP PAYRATE
A 1 $1000
A 2 $2000
A 3 $3000
A 4 $4000
B 1 $1670
B 2 $2212

and so on. Join this to your table by Position and Step.

John W. Vinson[MVP]
Click to expand...

I was trying to avoid that for reasons of data portability. I am
importing this data from a pre-existing list of several thousand
positions and pay steps. They will be changing every year and
rearranging them would be daunting. Looks like I'll have to write a Perl
script or something to do this.

Thanks.
 
I was trying to avoid that for reasons of data portability. I am
importing this data from a pre-existing list of several thousand
positions and pay steps. They will be changing every year and
rearranging them would be daunting. Looks like I'll have to write a Perl
script or something to do this.
Click to expand...

A "Normalizing Union Query" will zip the data out of this spreadsheet
into a tall-thin table with no problems whatsoever:

SELECT Position, (1) AS Step, [Step 1] AS PAYRATE
FROM flattable
WHERE [Step 1] IS NOT NULL
UNION ALL
SELECT Position, (2), [Step 2]
FROM flattable
WHERE [Step 2] IS NOT NULL
UNION ALL
SELECT Position, (3), [Step 3]
FROM flattable
WHERE [Step 3] IS NOT NULL
UNION ALL
SELECT Position, (4), [Step 4]
FROM flattable
WHERE [Step 4] IS NOT NULL;

Base an Append query upon this UNION query and run it - zap, you've
restructured the data.

John W. Vinson[MVP]
 
Back
Top