Help with Compound SQL statement??? 1

[bjdobs]

Given the following table

Table Foo
Field Type as String
Field Class As String
Field Value As double

Where each Type could have H classes
The Data could be;

Type,Class,Value
X, B, 102
X, D, 22
X, F, 53
y, A, 643
Z, E, 63

I need a query that takes Type as a parameter and returns
a flat record of all classes for a given type ... ei Type X;
A B C D E F G H
0 102 0 22 0 53 0 0

The SQL builder in Access only does a single select ... I suspect a compound select statement is required but am having difficulty visioning it

If I use the joins from the Access wizard, A, C, E, G and H will stop the select statement because the data is not there.

The data has to be in a query in order to use the spreadsheet export function ... I could create a CSV file from code and import into excel but was expecting there should be some way to do this in SQL???

 

[PHV]

You may try a CrossTable query (PivotTable).
Another way:
SELECT DISTINCT X.Type
,Nz((SELECT A.Value FROM foo A WHERE A.Type=X.Type AND A.Class='A'),0) AS A
,Nz((SELECT B.Value FROM foo B WHERE B.Type=X.Type AND B.Class='B'),0) AS B
,Nz((SELECT C.Value FROM foo C WHERE C.Type=X.Type AND C.Class='C'),0) AS C
,Nz((SELECT D.Value FROM foo D WHERE D.Type=X.Type AND D.Class='D'),0) AS D
,Nz((SELECT E.Value FROM foo E WHERE E.Type=X.Type AND E.Class='E'),0) AS E
,Nz((SELECT F.Value FROM foo F WHERE F.Type=X.Type AND F.Class='F'),0) AS F
,Nz((SELECT G.Value FROM foo G WHERE G.Type=X.Type AND G.Class='G'),0) AS G
,Nz((SELECT H.Value FROM foo H WHERE H.Type=X.Type AND H.Class='H'),0) AS H
FROM foo X

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244