T
Tom
All:
A query is kicking my tail end and I would very much like some help.
Distilled down, I have the following table
tblEvents:
ItemID
EventDate
EventType
(there are lots of other fields, but they don’t matter for this
discussion)
Where ItemID ad EventDate form a composite key.
The EventType can contain one of the following values:
• Successful Inspection
• Failed Inspection
• Successful Repair
• Failed Repair.
Items need to be inspected monthly. If they fail, they need to be
repaired, then reinspected weekly till there has been 2 successive
successful inspections.
Items frequently fail several successive inspections until they can be
successfully repaired. So you might get an event history like this
• 1 Feb – failed inspection
• 2 Feb – failed repair
• 8 Feb – failed inspection
• 15 Feb – failed inspection
• 16 Feb – successful repair
• 22 Feb – successful inspection
• 1 March – successful inspection
• 1 April – successful inspection
• 1 May – Failed inspection
How do I design a query to return the first failed inspection that is
not followed by two successful inspections? (in the example above it
would be the 1 May inspection).
I’ve left out a lot of pesky details that only serve to clutter the
problem up, but if I can solve the basic issue, I can likely deal with
the details.
Thanks
A query is kicking my tail end and I would very much like some help.
Distilled down, I have the following table
tblEvents:
ItemID
EventDate
EventType
(there are lots of other fields, but they don’t matter for this
discussion)
Where ItemID ad EventDate form a composite key.
The EventType can contain one of the following values:
• Successful Inspection
• Failed Inspection
• Successful Repair
• Failed Repair.
Items need to be inspected monthly. If they fail, they need to be
repaired, then reinspected weekly till there has been 2 successive
successful inspections.
Items frequently fail several successive inspections until they can be
successfully repaired. So you might get an event history like this
• 1 Feb – failed inspection
• 2 Feb – failed repair
• 8 Feb – failed inspection
• 15 Feb – failed inspection
• 16 Feb – successful repair
• 22 Feb – successful inspection
• 1 March – successful inspection
• 1 April – successful inspection
• 1 May – Failed inspection
How do I design a query to return the first failed inspection that is
not followed by two successful inspections? (in the example above it
would be the 1 May inspection).
I’ve left out a lot of pesky details that only serve to clutter the
problem up, but if I can solve the basic issue, I can likely deal with
the details.
Thanks