Flatten query results into one row

[acotgreave]

hi,
I've got a problem with multiple rows that I want flattened into one row.

In the attached database, I've created a simplified version of my problem.

The database has two tables, TestItems and Results. In Results, there are four Result columns, representing results of different types of tests.

The two tables are linked on ItemID.

For each record in Results, only one result column will have a value, the others will be NULL. And there is only one result of each type for each test item ID.

The "Test Results" query returns all results, with a separate row for each Result record. For example:

ItemID ItemName	ResultType1	ResultType2	ResultType3	ResultType4
1      antelope	1			
1      antelope			3		
1      antelope					3	
1      antelope							yes

All well and good. BUT, I need the results collapsed into one row so that I get all results for each test item on a single row:

ItemID ItemName	ResultType1	ResultType2	ResultType3	ResultType4
1      antelope	1		3		3		yes

Does anyone have any idea how I can do this?

Many thanks
Andy

 

[acotgreave]

Hmm, i've cracked it. I can fix it with a Group By on the first two columns, and then selecting MAX values on the results.

Isn't it always the way that you bang your head against the wall all day, then spend ages formatting a post to a forum, and THEN you see the answer?