access filter needed

Question

Here is a sample data set

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

12    0004     MB          10            0                0          12          3
13    0004     MB          18            0                1          12          3
14    0004     MB          20            0                0          12          3 
15    0004     MB          14            0                0          07          6
16    0004     MB          100           0                1          07          6
17    0004     MB          100           0                0          07          6

I have a query that returns the above table. Note that in each group of six, there WILL be atleast one row that has value 1 in include column. Look at ref: access query needed but not needed.

Also for each group of six, there are three rows that has grade = 3 and 3 rows that has grade = 6. And corresspondingly, the grade 3 and grade 6 have the same quantity in that group.

What I want to do is filter out all the rows that have less then 15 quantity. However, I still want to group them by 6.

I want to remove a "group" that has both quantity < 15 for both grade 3 and 6. From the above data set I wwant the following result:

-----------------------------------------------------------------------------------------
id   nameid   name        score         diff          include     quantity     grade
---------------------------------------------------------------------------------------

7     0002     MO          10            0                0          25          3
8     0002     MO          18            0                1          25          3
9     0002     MO          20            0                0          25          3
10    0002     MO          14            0                0          17          6
11    0002     MO          100           0                0          17          6
11    0002     MO          100           0                0          17          6

12    0003     MA          10            0                0          12          3 
13    0003     MA          18            0                0          12          3
14    0003     MA          20            0                0          12          3
15    0003     MA          14            0                0          25          6
16    0003     MA          100           0                1          25          6
17    0003     MA          100           0                0          25          6

So basically if a group of six has include = 1 in any row, and either grade 3 or 6 quantity > 15 then I want the entire group.

the column `nameid` can have upto 6 of the same names. – masfenix Jul 04 '11 at 18:48 — masfenix, Jul 04 '11 at 18:48
I have edited a few columns that I should've done before – masfenix Jul 04 '11 at 18:49 — masfenix, Jul 04 '11 at 18:49

score 1 · Accepted Answer · answered Jul 04 '11 at 19:14

"So basically if a group of six has include = 1 in any row, and either grade 3 or 6 quantity > 15 then I want the entire group."

My guess is this query will identify the candidate nameid groups:

SELECT DISTINCT nameid
FROM YourTable
WHERE
    include = 1
    AND quantity > 15
    AND (grade = 3 OR grade = 6);

If I guessed correctly, you can save it as a separate query, or use it as a subquery, and INNER JOIN it to YourTable to limit the rows returned to only those where nameid meets your criteria. It might look close to this untested SELECT statement:

SELECT y.id, y.nameid, y.[name], y.score, y.diff, y.include, y.quantity, y.grade
FROM
    YourTable AS y
    INNER JOIN [
        SELECT DISTINCT nameid
        FROM YourTable
        WHERE
            include = 1
            AND quantity > 15
            AND (grade = 3 OR grade = 6)
        ]. AS q
        ON y.nameid = q.nameid
ORDER BY y.nameid;

Edit: Add an index on nameid if you don't already have one.

right I think that works. just need to QA on that. However it is a bit on the slow side. I can't add an index on the table.. since the table itself is another query. — masfenix, Jul 04 '11 at 19:23
If there's no other practical solution, consider creating an auxiliary table to hold those DISTINCT nameid values. You could convert that one query into an "append query" ... `INSERT INTO AuxTable (nameid) SELECT DISTINCT nameid FROM YourTable...` Then INNER JOIN YourTable with AuxTable ON nameid. That approach wouldn't be my first choice, but if it gives enough performance improvement, and you can't find a better way, .... try it. :-) — HansUp, Jul 04 '11 at 20:43

access filter needed

1 Answers1