select only the "Good" groups

[Guest]

Suppose I have a table like this...

tblFIXData
----------
ID...........FixNo.........FixStatus
1............1...............Good
2............2...............Good
3............3...............Good
4............4...............Good
5............1...............Bad
6............2...............Good
7............3...............Good
8............4...............Good
9............1...............Good
10..........2................Bad
11..........3................Good
12..........4...............Good

I want to be able to query only the first four ID's since the first four
FixNo's (group1-4) are all good. ID's 5-8 start with a bad so all of that
group is considered bad. The group of ID's 9-12 have a bad within the 4
fixno's(in position 2) so They are also considered all bad.

Is there a way to filter this using a query? Note that the number of
fixno's can vary from 1-6.

Thanks a million.

Eskimo

 

[Michel Walsh]

Hi,


What defines a group?

with fields like:

ID GroupN FixNo FixStatus



it would be a matter to use:

SELECT a.*
FROM myTable As a
WHERE 'Good' = ALL(SELECT b.FixStatus FROM myTable As b WHERE b.GroupN =
a.GroupN)



or, with a join, to retrace the valid groups:

SELECT a.GroupN
FROM myTable As a LEFT JOIN (SELECT * FROM myTable WHERE FixStatus='Good')
As b
GROUP BY a.GroupN
HAVING COUNT(*)=COUNT(b.GroupN)


since a record with a FixStatus <> 'Good' will miss for the match, table
aliased "b" will supply Null for it, and its group will have a different
COUNT(*) than COUNT(b.GroupN).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

I was able to work out a solution in the following manner. (all from help
from this site).

First I created a new column in the Table called FixGroup. Then I re-sorted
the table first by FixNo's then ID. Then I filled the fixgroup columns with
sequence 1,2,3,4....etc where rows that have fixNo 1's Then I resorted the
whole table with the ID.

At that point I had a table with how I had before, but with the FixGroup
columns filled with a spaced sequence from 1- 15000 or so. This allowed me to
use a code to fill in the rest of the blanks. (where Records ID's 1-4 had all
fix groups of 1, ID's 5-9 had Fixgroup of 2's and so on and so forth.)

The code is as follows (thanks to John Spencer)

Dim lngGroup as Long
Dim rst as DAO.Recordset
Dim dbAny as DAO.Database

Set dbAny = Currentdb()
Set rst = dbAny.OpenRecordset ("SELECT ID, FixNo, FixGroup FROM FixData
ORDER BY Id, FixNo, FixGroup)

lngGroup= 0
With rst
.moveFirst
While Not .eof
If .Fields("fixNo") = 1 Then lngGroup= lngGroup+ 1
.Edit
.Fields("FixGroup") = lngGroup
.Update
.movenext
Wend
End with

From there I was able to run a series of queries first by showing any
fixstatus that had a bad, then I would delete query that. In the end, I had
only the fix groups where all of the records included a fixstatus GOOD.

Thanks