Condense rows in a query 1

[fishtek]

I have a query that returns the following with each row representing a different date in 2008:

Facility	Year	Jan	Feb	Mar	Apr
Dare County	2008	Pass	*	*	*
Dare County	2008	*	*	*	Pass
Dare County	2008	*	Pass	*	*
Dare County	2008	*	*	Fail	*

Is it possible in the query to condense the rows like so:

Facility	Year	Jan	Feb	Mar	Apr
Dare County	2008	Pass	Pass	Fail	Pass

Thanks for any help.

 

[PHV]

I have a query that returns the following
What is the SQL code of this query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fishtek]

Thanks PHV. The SQL code is as follows:

SELECT tblFacility.Facility, tblTesRec.[Test Date], Year([Test Date]) AS [Year], tblTesRec.Result, IIf(Month([Test Date])=1,[Result],"*") AS Jan, IIf(Month([Test Date])=2,[Result],"*") AS Feb, IIf(Month([Test Date])=3,[Result],"*") AS Mar, IIf(Month([Test Date])=4,[Result],"*") AS Apr
FROM tblFacility LEFT JOIN tblTesRec ON tblFacility.NPDES = tblTesRec.NPDES
WHERE (((Year([Test Date])) Between Year(Now()) And Year(Now())-4));

 

[dhookom]

With questioning your current SQL, you could simply create another query with SQL of:

SELECT Facility, [Year], Max(Jan) AS MaxOfJan, Max(Feb) AS MaxOfFeb, Max(Mar) AS MaxOfMar, Max(Apr) AS MaxOfApr
FROM YourQueryNameHere
GROUP BY Facility, [Year];

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

SELECT F.Facility, Year(T.[Test Date]) AS [Year]
, Max(IIf(Month(T.[Test Date])=1,T.Result," ")) AS Jan
, Max(IIf(Month(T.[Test Date])=2,T.Result," ")) AS Feb
, Max(IIf(Month(T.[Test Date])=3,T.Result," ")) AS Mar
, Max(IIf(Month(T.[Test Date])=4,T.Result," ")) AS Apr
FROM tblFacility F INNER JOIN tblTesRec T ON F.NPDES = T.NPDES
WHERE Year(T.[Test Date]) Between Year(Now()) And (Year(Now())-4)
GROUP BY F.Facility, Year(T.[Test Date])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fishtek]

Thanks dhookom and PHV. Looks like it works.