Using a Query Result as a Column Name in a Different Query

[milam]

Greetings,

I would like to use a query result as a column name in another query, I
can't seem to get this to work using Subqueries. Is there a good way to
do this?

Example:

Table "Employees" :
Name Title
Dave Manager
John Accountant
Ricky Fireman

Table "Rates":
Rate_Schedule Manager Accountant Fireman
A $5 $1 $2
B $8 $3 $6
C $7 $2 $1

Table "Projects":
Project Rate_Schedule
Mesa A
Houston B
Dallas A
Dulles C

I know that I could rearrage my information storage to make this easy
by combining these tables. But, I would like to keep the Rates table as
is so that it matches the contact documents. This is the way my
company has always thought about this subject and I hate to change our
culture because of a database limitation.

So, how can I formulate a series of queries that will get me the rate
for John for the Dallas project?

I am using Access 2002, and can code the solution in VB if necessary,
though I'd rather not, just for maintenance sake.

TIA,
Dave

[arthurjr07]

rs.Open "Select Title from Employees where Name = John"
Select case rs!Title
case "Manager"
SELECT Rates.Manager
FROM Rates INNER JOIN Projects ON
Rates.Rate_Sche dule = Projects.Rate_S chedule
WHERE Projects.Projec t = "Dallas"
case "Accountant "
SELECT Rates.Accountan t
FROM Rates INNER JOIN Projects ON
Rates.Rate_Sche dule = Projects.Rate_S chedule
WHERE Projects.Projec t = "Dallas"
case "Fireman"
SELECT Rates.Fireman
FROM Rates INNER JOIN Projects ON
Rates.Rate_Sche dule = Projects.Rate_S chedule
WHERE Projects.Projec t = "Dallas"
End Select

Hope this helps.

[CDMAPoster]

milam wrote:

Greetings,

I would like to use a query result as a column name in another query, I
can't seem to get this to work using Subqueries. Is there a good way to
do this?

Example:

Table "Employees" :
Name Title
Dave Manager
John Accountant
Ricky Fireman

Table "Rates":
Rate_Schedule Manager Accountant Fireman
A $5 $1 $2
B $8 $3 $6
C $7 $2 $1

Table "Projects":
Project Rate_Schedule
Mesa A
Houston B
Dallas A
Dulles C

I know that I could rearrage my information storage to make this easy
by combining these tables. But, I would like to keep the Rates table as
is so that it matches the contact documents. This is the way my
company has always thought about this subject and I hate to change our
culture because of a database limitation.

So, how can I formulate a series of queries that will get me the rate
for John for the Dallas project?

I am using Access 2002, and can code the solution in VB if necessary,
though I'd rather not, just for maintenance sake.

TIA,
Dave

Here's another way. After changing Name to EmployeeName and adding ID
fields to the tables from your example I decided to see if I could roll
a pseudo-normal schema using the same ideology as the one behind the
rational fortran (RATFOR) language paradigm. qryEmployees is used to
create what Employees should have been. Call this plan RATSCHEMA if
you'd like. An additional table 'Roles' is used to create the quasi
foreign key. Besides, who knows what else will break if you try to fix
it at this point?

Roles
RoleID AutoNumber
RoleName Text

RoleID RoleName
1 Manager
2 Accountant
3 Fireman

qryEmployees:
SELECT EmployeeID, EmployeeName,
Switch([EmployeeTitle]="Manager",1 ,[EmployeeTitle]="Accountant",2 ,[EmployeeTitle]="Fireman",3 )
AS RefRoleID FROM Employees;

!qryEmployees:
EmployeeID EmployeeName RefRoleID
1 Dave 1
2 John 2
3 Ricky 3

qryProjectRates :
SELECT EmployeeName, Project,
Switch(RefRoleI D=1,[Manager],RefRoleID=2,[Accountant],RefRoleID=3,[Fireman])
AS theRate FROM Rates INNER JOIN Projects ON Rates.Rate_Sche dule =
Projects.Rate_S chedule, qryEmployees INNER JOIN Roles ON
qryEmployees.Re fRoleID = Roles.RoleID WHERE qryEmployees.Em ployeeID=2
AND Projects.Projec tID=3;

!qryProjectRate s:
EmployeeName Project theRate
John Dallas $1

James A. Fortune
CD********@Fort uneJames.com

[pietlinden]

I know you're not going to like this, but the structure of your Rates
table is not very helpful. If you structure it like this:

CREATE TABLE Rates(
RateSchedule CHAR,
EmployeeType VARCHAR2(25).
HourlyRate DECIMAL,
PRIMARY KEY (RateSchedule, EmployeeType))

then the joins in your query will do the work for you. I would
definitely go this route if you can have lots of employee types and
rate schedules. In the long run, it will much easier to maintain.

Just my two cents....