Help with a troublesome Query - Think it needs embeded SubQueries

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

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
 
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.
 
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]
 
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


JL said:
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.


John Ortt said:
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
Click to expand...
Click to expand...
 
Back
Top