Sub Query Simplify

  • Thread starter Thread starter Kiran
  • Start date Start date
K

Kiran

Table : Production
Ln Processor Date_Worked
1 Abhisekh 3/31/2009
2 Abhisekh 3/31/2009
3 Abhisekh 3/31/2009
4 Abhisekh 3/31/2009
5 Abhisekh 3/31/2009
6 Bindu 3/31/2009
7 Bindu 3/31/2009
8 Bindu 3/31/2009
9 Bindu 3/31/2009
10 Mahesh 3/31/2009
11 Mahesh 3/31/2009
12 Mahesh 3/31/2009
13 Mahesh 3/31/2009
14 Mahesh 3/31/2009
15 Mahesh 3/31/2009
16 Bindu 4/1/09
17 Bindu 4/1/09
18 Bindu 4/1/09
19 Bindu 4/1/09
20 Mahesh 4/1/09
21 Mahesh 4/1/09
22 Mahesh 4/1/09
23 Mahesh 4/1/09
24 Mahesh 4/1/09
25 Mahesh 4/1/09

Table : Quality
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Criteria
Quality < 90 100 % Loans to be checked
Quality 90-95 80 % Loans to be checked
Quality >95 50 % Loans to be checked

The resources have the Quality Score mentioned in the Quality; I need to
select the number of records to be checked from Production table based on the
above criterion. This needs to be done everyday based on their production
yesterday. After the Loans are checked the Quality Scores would be updated. I
need a query to select the records from the Production Table. I am using the
below mentioned query to get the result. However the query takes a very long
time. Is there a way to simplify this.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Reports AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Reports P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));
 
Create a table named -
QualityRequirement --
Low High Requirement
0 89.9999 1
90 95.9999 0.8
96 100 0.5

Use number fields in table above and in -
Quality --
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Then this query --
SELECT Reports.[Date Worked], Quality.Processor, Count(Reports.Ln) AS
[Records Processed], Count([Ln])*[Requirement] AS [Records to be checked]
FROM QualityRequirement, Quality INNER JOIN Reports ON Quality.Processor =
Reports.Processor
WHERE (((Quality.Score) Between [Low] And [High]))
GROUP BY Reports.[Date Worked], Quality.Processor,
QualityRequirement.Requirement;
 
Hi,
I need the records which need to be checked and not the number of
records that need to be checked, I had mentioned Production table wrongly as
Reports in the sql statement, I have the below query which gives me the
result but I needed to know if it can be simplified.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Production AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Production P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor)
and (P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));


KARL DEWEY said:
Create a table named -
QualityRequirement --
Low High Requirement
0 89.9999 1
90 95.9999 0.8
96 100 0.5

Use number fields in table above and in -
Quality --
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Then this query --
SELECT Reports.[Date Worked], Quality.Processor, Count(Reports.Ln) AS
[Records Processed], Count([Ln])*[Requirement] AS [Records to be checked]
FROM QualityRequirement, Quality INNER JOIN Reports ON Quality.Processor =
Reports.Processor
WHERE (((Quality.Score) Between [Low] And [High]))
GROUP BY Reports.[Date Worked], Quality.Processor,
QualityRequirement.Requirement;

Kiran said:
Table : Production
Ln Processor Date_Worked
1 Abhisekh 3/31/2009
2 Abhisekh 3/31/2009
3 Abhisekh 3/31/2009
4 Abhisekh 3/31/2009
5 Abhisekh 3/31/2009
6 Bindu 3/31/2009
7 Bindu 3/31/2009
8 Bindu 3/31/2009
9 Bindu 3/31/2009
10 Mahesh 3/31/2009
11 Mahesh 3/31/2009
12 Mahesh 3/31/2009
13 Mahesh 3/31/2009
14 Mahesh 3/31/2009
15 Mahesh 3/31/2009
16 Bindu 4/1/09
17 Bindu 4/1/09
18 Bindu 4/1/09
19 Bindu 4/1/09
20 Mahesh 4/1/09
21 Mahesh 4/1/09
22 Mahesh 4/1/09
23 Mahesh 4/1/09
24 Mahesh 4/1/09
25 Mahesh 4/1/09

Table : Quality
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Criteria
Quality < 90 100 % Loans to be checked
Quality 90-95 80 % Loans to be checked
Quality >95 50 % Loans to be checked

The resources have the Quality Score mentioned in the Quality; I need to
select the number of records to be checked from Production table based on the
above criterion. This needs to be done everyday based on their production
yesterday. After the Loans are checked the Quality Scores would be updated. I
need a query to select the records from the Production Table. I am using the
below mentioned query to get the result. However the query takes a very long
time. Is there a way to simplify this.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Reports AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Reports P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));
Click to expand...
Click to expand...
 
I might try the following and see if you get the results you want.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Production AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (P1.ln In
(Select Top 50 Percent P2.Ln from Production P2
where P2.Date_Worked = [Please Enter the date]
order by P2.Ln) AND Quality.Score>"95")

OR (P1.ln In
(Select Top 80 percent P2.Ln from Production P2
WHERE P2.Date_Worked = [Please Enter the date]
order by P2.Ln) AND
Quality.Score Between "90" And "95")

OR (P1.DateWorked = [Please Enter the date] AND Quality.Score)<"90")

Also make sure you have indexes on the DateWorked, Score, LN and Processor
fields. Also if Score is a number field then you should remove the quotes
around the 90 and 95.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,
I need the records which need to be checked and not the number of
records that need to be checked, I had mentioned Production table wrongly as
Reports in the sql statement, I have the below query which gives me the
result but I needed to know if it can be simplified.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Production AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Production P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor)
and (P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));


KARL DEWEY said:
Create a table named -
QualityRequirement --
Low High Requirement
0 89.9999 1
90 95.9999 0.8
96 100 0.5

Use number fields in table above and in -
Quality --
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Then this query --
SELECT Reports.[Date Worked], Quality.Processor, Count(Reports.Ln) AS
[Records Processed], Count([Ln])*[Requirement] AS [Records to be checked]
FROM QualityRequirement, Quality INNER JOIN Reports ON Quality.Processor =
Reports.Processor
WHERE (((Quality.Score) Between [Low] And [High]))
GROUP BY Reports.[Date Worked], Quality.Processor,
QualityRequirement.Requirement;

Kiran said:
Table : Production
Ln Processor Date_Worked
1 Abhisekh 3/31/2009
2 Abhisekh 3/31/2009
3 Abhisekh 3/31/2009
4 Abhisekh 3/31/2009
5 Abhisekh 3/31/2009
6 Bindu 3/31/2009
7 Bindu 3/31/2009
8 Bindu 3/31/2009
9 Bindu 3/31/2009
10 Mahesh 3/31/2009
11 Mahesh 3/31/2009
12 Mahesh 3/31/2009
13 Mahesh 3/31/2009
14 Mahesh 3/31/2009
15 Mahesh 3/31/2009
16 Bindu 4/1/09
17 Bindu 4/1/09
18 Bindu 4/1/09
19 Bindu 4/1/09
20 Mahesh 4/1/09
21 Mahesh 4/1/09
22 Mahesh 4/1/09
23 Mahesh 4/1/09
24 Mahesh 4/1/09
25 Mahesh 4/1/09

Table : Quality
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Criteria
Quality < 90 100 % Loans to be checked
Quality 90-95 80 % Loans to be checked
Quality >95 50 % Loans to be checked

The resources have the Quality Score mentioned in the Quality; I need to
select the number of records to be checked from Production table based on the
above criterion. This needs to be done everyday based on their production
yesterday. After the Loans are checked the Quality Scores would be updated. I
need a query to select the records from the Production Table. I am using the
below mentioned query to get the result. However the query takes a very long
time. Is there a way to simplify this.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Reports AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Reports P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));
Click to expand...
Click to expand...
Click to expand...
 
Two more queries to pull the records.
This part of query pulls from last of records of the date --
- AND Q1.[Ln] > Q.[Ln])+1 AS Rank
reverse to pull first of records --
- AND Q1.[Ln] > Q.[Ln])+1 AS Rank

If you use something different than [Ln] you could randomize it.
If you have a text field then like this --
- AND Right(Left(Q1.[Text],5),3) > Right(Left(Q.[Text],5),3) )+1 AS
Rank

Kiran_Rank --
SELECT Q.Processor, Q.[Date Worked], Q.Ln, (SELECT COUNT(*) FROM [Reports] Q1
WHERE Q1.[Processor] = Q.[Processor]
AND Q1.[Date Worked] = Q.[Date Worked]
AND Q1.[Ln] > Q.[Ln])+1 AS Rank
FROM Reports AS Q
ORDER BY Q.Processor, Q.[Date Worked], Q.Ln DESC;

SELECT Reports.*
FROM (Reports INNER JOIN Kiran_Rank ON (Reports.Ln = Kiran_Rank.Ln) AND
(Reports.[Date Worked] = Kiran_Rank.[Date Worked]) AND (Reports.Processor =
Kiran_Rank.Processor)) INNER JOIN Kiran ON (Reports.[Date Worked] =
Kiran.[Date Worked]) AND (Reports.Processor = Kiran.Processor)
WHERE (((Kiran_Rank.Rank)<=Round([Records to be checked])));


Kiran said:
Hi,
I need the records which need to be checked and not the number of
records that need to be checked, I had mentioned Production table wrongly as
Reports in the sql statement, I have the below query which gives me the
result but I needed to know if it can be simplified.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Production AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Production P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor)
and (P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Production P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));


KARL DEWEY said:
Create a table named -
QualityRequirement --
Low High Requirement
0 89.9999 1
90 95.9999 0.8
96 100 0.5

Use number fields in table above and in -
Quality --
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Then this query --
SELECT Reports.[Date Worked], Quality.Processor, Count(Reports.Ln) AS
[Records Processed], Count([Ln])*[Requirement] AS [Records to be checked]
FROM QualityRequirement, Quality INNER JOIN Reports ON Quality.Processor =
Reports.Processor
WHERE (((Quality.Score) Between [Low] And [High]))
GROUP BY Reports.[Date Worked], Quality.Processor,
QualityRequirement.Requirement;

Kiran said:
Table : Production
Ln Processor Date_Worked
1 Abhisekh 3/31/2009
2 Abhisekh 3/31/2009
3 Abhisekh 3/31/2009
4 Abhisekh 3/31/2009
5 Abhisekh 3/31/2009
6 Bindu 3/31/2009
7 Bindu 3/31/2009
8 Bindu 3/31/2009
9 Bindu 3/31/2009
10 Mahesh 3/31/2009
11 Mahesh 3/31/2009
12 Mahesh 3/31/2009
13 Mahesh 3/31/2009
14 Mahesh 3/31/2009
15 Mahesh 3/31/2009
16 Bindu 4/1/09
17 Bindu 4/1/09
18 Bindu 4/1/09
19 Bindu 4/1/09
20 Mahesh 4/1/09
21 Mahesh 4/1/09
22 Mahesh 4/1/09
23 Mahesh 4/1/09
24 Mahesh 4/1/09
25 Mahesh 4/1/09

Table : Quality
Processor Score
Abhisekh 92
Bindu 89
Mahesh 95

Criteria
Quality < 90 100 % Loans to be checked
Quality 90-95 80 % Loans to be checked
Quality >95 50 % Loans to be checked

The resources have the Quality Score mentioned in the Quality; I need to
select the number of records to be checked from Production table based on the
above criterion. This needs to be done everyday based on their production
yesterday. After the Loans are checked the Quality Scores would be updated. I
need a query to select the records from the Production Table. I am using the
below mentioned query to get the result. However the query takes a very long
time. Is there a way to simplify this.

SELECT P1.ln, P1.Processor, Quality.Score
FROM Reports AS P1 INNER JOIN Quality ON P1.Processor=Quality.Processor
WHERE (((P1.ln) In (Select Top 50 Percent P2.Ln from Reports P2 where
((P2.Processor = P1.Processor) and (P2.Date_Worked = [Please Enter the
date])) order by P2.Ln)) AND ((Quality.Score)>"95")) OR (((P1.ln) In (Select
Top 80 percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score) Between "90" And "95")) OR (((P1.ln) In (Select Top 100
Percent P2.Ln from Reports P2 where ((P2.Processor = P1.Processor) and
(P2.Date_Worked = [Please Enter the date])) order by P2.Ln)) AND
((Quality.Score)<"90"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top