Help with a troublesome Query - Think it needs embeded SubQueries

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
 
J

JL

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

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

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top