show the attributes of the selected records

[Jenny]

Marshall,
The codes didn't work. The DISTINCT in the codes "DISTINCT Ttable.DateTime,
Ttable.QFINAL" is on both attributes. The multiple records do have different
DateTime, so the duplicates for QFINAL can't be removed by this codes.

One more thing is about my question in my first reply on Dec 12th. I have a
question about excluding the date whenever there is a Bad record on that
date. QFINALFlag="B". It is not about zero values. Some nonzero values are
also bad data. I don't know how to exclude a date whenever there is a bad
record? Do you have any idea to do that? Thank you a lot!

 

[Marshall Barton]

The codes didn't work. The DISTINCT in the codes "DISTINCT Ttable.DateTime,
Ttable.QFINAL" is on both attributes. The multiple records do have different
DateTime, so the duplicates for QFINAL can't be removed by this codes.

One more thing is about my question in my first reply on Dec 12th. I have a
question about excluding the date whenever there is a Bad record on that
date. QFINALFlag="B". It is not about zero values. Some nonzero values are
also bad data. I don't know how to exclude a date whenever there is a bad
record?

This is getting fairly complicated, but, if I understand
what you want, I think (speed issues aside) this might do
it:

SELECT Min(Ttable.DateTime), Ttable.QFINAL
FROM Ttable INNER JOIN
(SELECT DateValue(X.DateTime) AS DayMin,
Min(X.QFINAL) AS MinQFINAL,
Sum(QFINALFlag="B") As Bad
FROM Ttable AS X
GROUP BY DateValue(X.DateTime)
HAVING Sum(QFINALFlag="B") = 0
) AS XTABLE
ON Ttable.QFINAL = XTABLE.MinQFINAL
And DateValue(Ttable.DateTime) = XTABLE.DayMin
GROUP BY Ttable.QFINAL