Row Data As Column Headers in Query

[scadav]

Folks,

I have a table that looks something like this:

UniqueID InspectionID InspectionType InspectionResult
1 10 Fire Extinguisher Passed
2 10 Flares Passed
3 10 Reflectors Passed
4 10 Jack Passed
5 11 Fire Extinguisher Passed
6 11 Flares Passed
7 11 Reflectors Passed
8 12 Fire Extinguisher Passed
9 12 Flares Failed
10 12 Reflectors Passed
11 12 Jack Passed
12 15 Fire Extinguisher Passed
13 15 Flares Failed
14 15 Jack Passed


I set it up like this so the users will have the flexibility to add
Inspection Types, without make new columns, etc.

The problem I am running into is that I want to create a query and would
like the results to look as follows (UniqueInspectionType values as column
headers):

ID FIRE EXTINGUISHER FLARES REFLECTORS JACK
10 Passed Passed Passed Passed
11 Passed Passed Passed NULL
12 Passed Failed Passed Passed
15 Passed Failed NULL Passed


Any suggestion as to how I get started with this query?

Thanks.

 

[John Spencer]

Take a look at crosstab query

TRANSFORM First(InspectionResult) as PassFail
SELECT InspectionID
FROM YourTable
GROUP BY InspectionID
PIVOT InspectionType

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KARL DEWEY]

Try this ---
TRANSFORM First(Inspection.InspectionResult) AS FirstOfInspectionResult
SELECT Inspection.InspectionID
FROM Inspection
GROUP BY Inspection.InspectionID
PIVOT Inspection.InspectionType;

 

[scadav]

EXCELLENT!! That works. Thanks.

 

[John W. Vinson]

...
The problem I am running into is that I want to create a query and would
like the results to look as follows (UniqueInspectionType values as column
headers):

ID FIRE EXTINGUISHER FLARES REFLECTORS JACK
10 Passed Passed Passed Passed
11 Passed Passed Passed NULL
12 Passed Failed Passed Passed
15 Passed Failed NULL Passed


Any suggestion as to how I get started with this query?

Well... a Crosstab Query will *display* the data in this way. However, a
Crosstab query is not editable, so it's not going to help much!

There's no really easy way to do this. One way would be to use a denormalized
wide-flat temp table (which would need to be rebuilt every time a
UniqueExtensionType value is changed or added - perhaps every time you open
the form!); another would be to use an unbound form with textboxes for all the
types, but then you'ld need to redesign the form whenever there is a new type.

Would your users be willing to tolerate this if the data were turned
"sideways" - i.e. select an ID on a main form, and have a vertical subform
with all possible types down the left side and a combo box next to it with
choices for passed/failed/NULL?

John W. Vinson [MVP]