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

[dmilamj]

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, and 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

 

[Tom Ellison]

Dear Dave:

What you have now seems to be largely what you should have. Queries are
designed to work with data in exactly the form you show.

A query to combine these two so you can see the overall structure would be:

SELECT P.Project, P.Rate_Schedule,
R.Manager, R.Accountant, R.Fireman
FROM Projects P
INNER JOIN Rates R
ON R.Project = P.Project
ORDER BY P.Project, P.RateSchedule

Indeed, rather than heading in the direction of combining these two tables
into 1, I would have designed them as 4 tables!

RateGroup (table)
RateGroup
Accountant
Fireman
Manager

If you have new rate groups, you could just add to a table, not have to add
new columns to the Rates table.

RateSchedule (table)
RateSchedule
A
B
C

Again, if you need a new one, you add it to this table. Such one column
tables are not uncommon at all.

With the two above tables, you have a cross-product. Every RateSchedule can
apply to every RateGroup. This table shows that and stores the actual
rates:

RateGroupSchedule (table)
RateGroup RateSchedule RateAmount
Accountant A 1
Accountant B 3
Accountant C 2
Fireman A 2
Fireman B 6
Fireman C 1
Manager A 5
Manager B 8
Manager C 7

With the above, you have accomplished everything you had before, but in a
properly structured environment in which new RateGroups can be added without
programming. You can turn this over to users.

Your Projects table would be unchanged.

A cross-tab query can produce the output you desire from the above.

It's a bit more work to set this up, but it is far, far easier in the long
run.

Here are some advanced ways I would use this:

There would be a form with multiple subforms: RateGroup, RateSchedule,
RateGroupSchedule, and Project.

The RateGroupSchedule can be presented in the cross-tab format, much like
you're used to seeing it. Data can be entered in the matrix style, with
horizontal scrolling that allows you to show any number of columns for
RateGroups. This is an advanced technique, in which a temporary table is
created with an appearance like the table you have, then updated back into
the table which I proposed. This makes the whole thing flexible beyond what
most have ever seen.

The above are actually my "standard" for database programming. Well, part
of it.

Tom Ellison

 

[Douglas J Steele]

First of all, you should normalize your Rates table. You're storing data in
field names. The table should look like:

Rate_Schedule Title Rate
A Manager $5
A Accountant $1
A Fireman $2
B Manager $8
B Accountant $3
B Fireman $6
C Manager $7
C Accountant $2
C Fireman $1

Then, it's a simple query to join the two tables.

 

[milam]

Thanks to you both for your comments.

I have rearranged the data as recommended. Works just fine. I was
being stubborn in my search for the most elegant data presentation, I
guess.

I also created a crosstab query that looks like our old rate schedule.
This way I can at present the crosstab query as a double-check. I think
that will work well enough for the next several years.