Query Help

[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

 

[KARL DEWEY]

Try adding an Autonumber field named Auto and use this query --
SELECT tblEvents.Auto, tblEvents.ItemID, tblEvents.EventDate,
tblEvents.EventType
FROM (tblEvents INNER JOIN tblEvents AS tblEvents_1 ON tblEvents.ItemID =
tblEvents_1.ItemID) INNER JOIN tblEvents AS tblEvents_2 ON tblEvents.ItemID =
tblEvents_2.ItemID
WHERE (((tblEvents.EventType)="Failed inspection") AND
((tblEvents_1.Auto)=[tblEvents].[Auto]-1) AND
((tblEvents_1.EventType)="successful inspection") AND
((tblEvents_2.Auto)=[tblEvents_1].[Auto]-1) AND
((tblEvents_2.EventType)="successful inspection"));

 

[Tom]

Karl/Ken:

Thanks for your suggestions - I got side tracked fixing a bug in the
DB and will get back to this over the weekend. I'll post a follow up.

Tom

 

[lars meyn]

Director Worldwide Security
Communitiesnews:[email protected]...

Try adding an Autonumber field named Auto and use this query --
tblEvents.EventType
FROM (tblEvents INNER JOIN tblEvents AS tblEvents_1 ON tblEvents.ItemID =
tblEvents_1.ItemID) INNER JOIN tblEvents AS tblEvents_2 ON
tblEvents.ItemID =
tblEvents_2.ItemID
WHERE (((tblEvents.EventType)="Failed inspection") AND
((tblEvents_1.Auto)=[tblEvents].[Auto]-1) AND
((tblEvents_1.EventType)="successful inspection") AND
((tblEvents_2.Auto)=[tblEvents_1].[Auto]-1) AND
((tblEvents_2.EventType)="successful inspection"));

--
Build a little, test a little.

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

 

[Tom]

Just wanted to post back the solution I ended up using. As is
typically the case, a little time away from the problem, some helpful
hints from this group and the effort put into distilling the problem
down to something understandable for posting lead to great insights
into the issue (well, all that and a beer or two).

I ended up making a query to pull out only the inspections, and a true/
false field indicating a pass or a fail – namely
qryPassFailInspection. It returns 3 fields, ItemID, EventDate and
PassedInspection.

I then set up a second query called qryRemonitor_All as follows:

SELECT qryFail.ItemID, qryFail.EventDate, Sum(IIf([qrypass].[eventdate]

[qryfail].[eventdate] And [qrypass].[passedinspection]=True,1,0)) AS

CountSuccessAfter
FROM qryPassFailInspection AS qryFail INNER JOIN qryPassFailInspection
AS qryPass ON qryFail.ItemID = qryPass.ItemID
WHERE (((qryFail.PassedInspection)=0))
GROUP BY qryFail.EventTagNumber, qryFail.EventDate
HAVING (((Sum(IIf([qrypass].[eventdate]>[qryfail].[eventdate] And
[qrypass].[passedinspection]=True,1,0)))=0 Or (Sum(IIf([qrypass].
[eventdate]>[qryfail].[eventdate] And [qrypass].[passedinspection]
=True,1,0)))=1));

Basically aliasing qryPassFailInspection as both qryPass and qryFail,
joining them on ItemID and returning only the records from qryFail
where there aren’t two subsequent records in qryPass that were
successful inspections.

Thanks again to those who offered advice and to the group as a whole
for being such a great resource.