views:

105

answers:

1

I have an Access database that contains a table with information about parts we sort. This table has an autonumber ID field and a 110ID that links to another table with the part information. It also contains a sortDate, sortShift, sorted, scrapped, and repaired. I need to find how many parts have been sorted since the last defect (none scrapped or repaired) was found for each 110ID.

The problem is that I cannot guarantee that the information will be entered into the database in chronological order. So I need sum the 'sorted' field for any records that have a 'sortDate' greater than the last defect, or a 'sortDate' the same as the last defect but greater 'sortShift', or use the autonumber id as a last resort if both the 'sortDate' and 'sortShift' match.

This is the query I am currently using:

SELECT SortInfo.[110ID], Sum(SortInfo.Sorted) AS SumOfSorted
FROM SortInfo
WHERE (
    ((SortInfo.sortdate)>(select top 1 dupe.sortdate from sortinfo as dupe where     (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)))
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)>(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))) 
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)=(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.ID)>(select top 1 dupe.id from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))
)
GROUP BY SortInfo.[110ID];

The problem is that this is extremely slow. Is there a better way to accomplish this that will yield better performance?

A: 

Instead of using all of those subqueries, you can do this with either a LEFT OUTER JOIN or a NOT EXISTS subquery. I don't use Access much, so you may need to tweak these if it's one of those areas where Access falls short of following the ANSI standard.

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
LEFT OUTER JOIN SortInfo SI2 ON
    SI2.Repaired <> 0 AND
    SI2.Scrapped <> 0 AND
    (
        SI2.SortDate > SI.SortDate OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
    )
WHERE
    SI2.ID IS NULL
GROUP BY
    SI.[110ID]

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            SortInfo SI2
        WHERE
            SI2.Repaired <> 0 AND
            SI2.Scrapped <> 0 AND
            (
                SI2.SortDate > SI.SortDate OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
            )    
    GROUP BY
        SI.[110ID]
    )
Tom H.
Initial findings: I cannot get option one to work because the logic needs to be `((SI2.Repaired <> 0) OR (SI2.Scrapped <> 0)) AND...` but it does not seem that Access will allow the extra () to group the two or'd statements. Option two doesn't seem to work either or it is just as slow.
AdmSteck
That's very odd that it isn't allowing the parentheses. Unfortunately, I don't have Access on this PC to test. Are you sure that there isn't a typo somewhere?
Tom H.
Access/Jet/ACE is very picky about its JOIN syntax. Also note that NOT IN and NOT EXISTS are often not well-optimized by Jet and may use the index on only one side of the comparison.
David-W-Fenton