show the attributes of the selected records

  • Thread starter Thread starter Jenny
  • Start date Start date
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!
 
Jenny said:
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?
Click to expand...


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
 
Back
Top