Help with a troublesome Query - Think it needs embeded SubQueries

[John Ortt]

I have a friend who stores data about quality and failure rates.
He has a table with the following data (example only):

Key Part Date Failed
1 A 01/01/05
2 A 01/02/05
3 A 01/03/05
4 B 01/04/05
5 C 01/04/05
6 B 01/05/05
7 B 01/06/05
8 A 01/07/05
9 A 01/08/05
10 C 01/09/05

He wishes to produce a report which will show him the detail records for all
defects between two certain dates
BUT he only wishes the show the ones where there have been X or more
instances of failure between those dates.

Where X is a variable decided by the user.

So for example dates of 05/01/05 and 10/06/05 with a variable of 2 would
produce the following result:

2 A 01/02/05
3 A 01/03/05
4 B 01/04/05
6 B 01/05/05
7 B 01/06/05

As parts A and B both had at least two occurences during the time frame so
all detail records for those parts (within the timeframe) are shown.

Any ideas would be greatly appreciated.

Thankyou,

John

 

[John Ortt]

Where X is a variable decided by the user.

Just to clarify the start and end dates are also variables decided by the
user.

 

[Guest]

Hi John,

Try this. The trick is to count the number of failed up to the End date.

Table Name: failure rates
Field Name: Key, Part, Date Failed

SELECT [failure rates].Part, [failure rates].[Date Failed]
FROM [failure rates]
WHERE ((([failure rates].[Date Failed])>=[Start] And ([failure rates].[Date
Failed])<=[End]) AND ((DCount("Part","[failure rates]","Part='" & [Part] & "'
AND [Date Failed] <= #" & [End] & "#"))>=[Count]))
ORDER BY [failure rates].Part, [failure rates].[Date Failed];

[Start]: Frist Date (Start)
[End]: Second Date (End)

Hope this helps.

 

[Sunny]

Try this:

SELECT * FROM FailedInfo WHERE part IN
(SELECT part FROM FailedInfo WHERE datefailed between [First date] and
[Second Date] GROUP BY part having count(*) >= [Enter occurences])
AND datefailed between [First date] and [Second Date]

 

[John Ortt]

That worked a treat, Thanks Sunny

 

[John Ortt]

Hi JL,

Thanks for the tip, I did try it out but it seemed to take an awful long
time. After 20mins I pressed escape and tried Sunny's suggestion which ran
in less than a minute (the time may well have been down to a mistake by me
in ammending the code).

Thanks anyway JL,

John

Hi John,

Try this. The trick is to count the number of failed up to the End date.

Table Name: failure rates
Field Name: Key, Part, Date Failed

SELECT [failure rates].Part, [failure rates].[Date Failed]
FROM [failure rates]
WHERE ((([failure rates].[Date Failed])>=[Start] And ([failure rates].[Date
Failed])<=[End]) AND ((DCount("Part","[failure rates]","Part='" & [Part] & "'
AND [Date Failed] <= #" & [End] & "#"))>=[Count]))
ORDER BY [failure rates].Part, [failure rates].[Date Failed];

[Start]: Frist Date (Start)
[End]: Second Date (End)

Hope this helps.

I have a friend who stores data about quality and failure rates.
He has a table with the following data (example only):

Key Part Date Failed
1 A 01/01/05
2 A 01/02/05
3 A 01/03/05
4 B 01/04/05
5 C 01/04/05
6 B 01/05/05
7 B 01/06/05
8 A 01/07/05
9 A 01/08/05
10 C 01/09/05

He wishes to produce a report which will show him the detail records for all
defects between two certain dates
BUT he only wishes the show the ones where there have been X or more
instances of failure between those dates.

Where X is a variable decided by the user.

So for example dates of 05/01/05 and 10/06/05 with a variable of 2 would
produce the following result:

2 A 01/02/05
3 A 01/03/05
4 B 01/04/05
6 B 01/05/05
7 B 01/06/05

As parts A and B both had at least two occurences during the time frame so
all detail records for those parts (within the timeframe) are shown.

Any ideas would be greatly appreciated.

Thankyou,

John