By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

May 1 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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_Schedule = Projects.Rate_Schedule
WHERE Projects.Project = "Dallas"
case "Accountant"
SELECT Rates.Accountant
FROM Rates INNER JOIN Projects ON
Rates.Rate_Schedule = Projects.Rate_Schedule
WHERE Projects.Project = "Dallas"
case "Fireman"
SELECT Rates.Fireman
FROM Rates INNER JOIN Projects ON
Rates.Rate_Schedule = Projects.Rate_Schedule
WHERE Projects.Project = "Dallas"
End Select

Hope this helps.

May 2 '06 #2

P: n/a
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(RefRoleID=1,[Manager],RefRoleID=2,[Accountant],RefRoleID=3,[Fireman])
AS theRate FROM Rates INNER JOIN Projects ON Rates.Rate_Schedule =
Projects.Rate_Schedule, qryEmployees INNER JOIN Roles ON
qryEmployees.RefRoleID = Roles.RoleID WHERE qryEmployees.EmployeeID=2
AND Projects.ProjectID=3;

!qryProjectRates:
EmployeeName Project theRate
John Dallas $1

James A. Fortune
CD********@FortuneJames.com

May 2 '06 #3

P: n/a
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....

May 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.