SQL to group data in columns

[Ron H]

I have a table with test, scan, point, and data fields.

for each test there are many scans and for each scan there are a many
predefined points. (always the same number of points). There is 1 piece of
data for each predefined point in each scan in each test.

PROBLEM:

I need to write a query that returns a column of data for each scan sorted
by point.

Point Scan 1 Scan 2 Scan n
1 29.0 28.7 30.0
2 30.1 29.5 30.1
3 28.7 29.4 29.9
..
..
..
..
240 26.9 28.6 30.3


If I use subqueries to define columns, they only return a single row.... How
can I do this?


Ron H.







x-- 100 Proof News - http://www.100ProofNews.com
x-- 30+ Days Binary Retention with High Completion
x-- Access to over 1.9 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD

 

[Lynn Trapp]

You probably need to use a Crosstab query for this.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Ron H]

Lynn,
Thanks for the tip. I used the wizard and generated the folowing SQL
staement. It returns a table with the correct number of rows and columns and
with the correct row headings but no column headings OR data! What did I
miss?

TRANSFORM Var(ScanData.Data) AS VarOfData
SELECT ScanData.Point
FROM ScanData
GROUP BY ScanData.Point
PIVOT ScanData.Scan;

Ron H.

You probably need to use a Crosstab query for this.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

x-- 100 Proof News - http://www.100ProofNews.com
x-- 30+ Days Binary Retention with High Completion
x-- Access to over 1.9 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD

 

[Duane Hookom]

What are typical values in your table? Did you really want the "Var" of the
value or do you want Avg or Min or Max or First?