cross tab query to show all fields in a query

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Need to be able to turn a table where I have a b c d e f
fields in a table. I would like to have a b c as
d e f columns
rows a b c
example

system table1
state county service Units AvgDays AvgCost
NC wake 1 15 5.1 $123.00

system table2
state county service Units AvgDays AvgCost
NC wake 1 20 6.5 $100.00

final results

state county service table1.Units table2.Units
table1.AvgDays table2.AvgDays table1.AvgCost
table2.AvgCost

I already can do this in several queries using a union
query. I was wondering if anyone knew of another way?

al
 
Dear Al:

Assuming state/county/service is a unique index in both tables (you
only have one row of any combination of values, you can simply JOIN on
these three columns:

SELECT T1.state, T1.county, T1.service, T1.Units T1Units,
T2.Units T2Units, T1.AvgDays T1AvgDays, T2.AvgDays T2AvgDays,
T1.AvgCost T1AvgCost, T2.AvgCost T2AvgCost
FROM table1 T1
INNER JOIN table2 T2 ON T2.state = T1.state
AND T2.county = T1.County AND T2.service = T1.service

If you need to aggregate multiple rows from table1 and/or table2 to
get to a single value, then this would be more complex.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
problem is one table may not have the same state county
service as the other.
I actualy have that query working already. However I am
building a temp table to handle all instances of the
state county service in a singe table first then joining.
based on the temp table for both. Just seems like an
awful lot of build up work to get this to work.

al
 
Back
Top