Help with criteria

  • Thread starter Thread starter Eric G
  • Start date Start date
E

Eric G

I'm trying to figure out how I can restrict records that are being
returned in a query

The field in question (number) might have the following values:
Nov 9 9
Nov 10 4
Nov 11 3
Nov 12 4
Nov 13 9
Nov 14 5

There are a total of 9 possible values.
Would someone be able to tell me the SQL that would pull up the
earliest of the values, excluding 9, *unless* 9 is the only number in
those fields ?

So with the above example, return
Nov 10 4

And in the following example:
Nov 2 9
Nov 3 9
Nov 4 9
Nov 5 9

return
Nov 2 9

Thanks for your help! Eric
 
Try something along the following lines
SELECT TOP 1 dateColumn, numberColumn
FROM YourTable
WHERE dateColumn IN (SELECT Min(dateColumn) FROM YourTable
WHERE numberColumn < 9)
OR dateColumn IN (SELECT Min(dateColumn) FROM YourTable
WHERE numberColumn = 9)
ORDER BY dateColumn DESC

Hope This Helps
Gerald Stanley MCSD
 
Hi Gerald,

Thanks for helping out.
I tried to simplify my query to start out my post.

There are other required fields and they seem to be getting in the way
of the code you provided.

Here is your code applied to my situation

SELECT TOP 1 Detentions.DateDet, Detentions.ReasonID
FROM Detentions
WHERE (((Detentions.DateDet) In (SELECT Min(DateDet) FROM Detentions
WHERE ReasonID < 9) Or (Detentions.DateDet) In (SELECT Min(DateDet)
FROM Detentions WHERE ReasonID = 9)))
ORDER BY Detentions.DateDet DESC;

I need to incorporate the above code with the following code:

SELECT Detentions.StudentID, First(Detentions.TeacherID) AS
FirstOfTeacherID, Students.LastName
FROM Students INNER JOIN Detentions ON Students.StudentID =
Detentions.StudentID
WHERE (((Detentions.Status) Is Null))
GROUP BY Detentions.StudentID, Students.LastName
ORDER BY Students.LastName;

When I add the above code I get aggregate function errors and such.

Eric
 
Eric

The following SQL assumes that you wish the date and reason
to apply per student and that the teacherId applies to the
detention date.

SELECT Students.LastName, D1.StudentID, D1.TeacherID,
D1.dateDet, D1.reasonId
FROM Students INNER JOIN Detentions AS D1 ON
Students.StudentID = D1.StudentID
WHERE D1.Status Is Null
AND D1.dateDet IN
(SELECT TOP 1 D2.DateDet
FROM Detentions AS D2
WHERE D2.DateDet In (SELECT Min(DateDet) FROM Detentions
WHERE ReasonID < 9 AND studentId = D1.studentId) Or
D2.DateDet In (SELECT Min(DateDet)
FROM Detentions WHERE ReasonID = 9 AND studentId =
D1.studentId)
ORDER BY Detentions.DateDet DESC)
ORDER BY Students.LastName;

Hope This Helps
Gerald Stanley MCSD
 
Hi Gerald,

I'm not sure if I'm following your instructions properly.
This time all I did was cut and paste your code into my SQL and tried
to run it.
I'm prompted for a value for Detentions.DateDet.
If I hit Enter or enter a date I get an hourglass and nothing happens.

Oh one more thing. When I look at Design view, I see a new table D1
linked to Students on StudentID but the Detentions table isn't shown.

Thanks. Eric
 
Eric

This information is going to take a while to produce given
the requirement to find the minimum of two dates per
student. Let's see if a UNION query can speed things up.
Give the following a try

SELECT S1.LastName,D1.StudentID, D1.TeacherID, D1.dateDet,
D1.reasonId
FROM Students AS S1 , Detentions AS D1,
(SELECT studentId , Min(dateDet) as MinDateDet
FROM Detentions
GROUP BY studentId
HAVING studentId IN (SELECT DISTINCT studentId FROM
Detentions GROUP BY studentid HAVING Count(1) =
Count(IIf(reasonId=9,1,0)))
UNION
SELECT studentId , Min(dateDet) as MinDateDet
FROM Detentions
WHERE reasonId < 9
GROUP BY studentId
HAVING studentId NOT IN (SELECT DISTINCT studentId FROM
Detentions GROUP BY studentid HAVING Count(1) =
Count(IIf(reasonId=9,1,0)))) AS Q1
WHERE D1.Status Is Null
AND S1.studentId = D1.studentId
AND D1.studentId = Q1.studentId
AND D1.dateDet = Q1.minDateDet

The first part of the union query is restricted to those
students who are in the detentions table with a reasonId of
9 only. The second part is restricted to the other
students. You should ensure that the Detentions table has
indexes for studentId and reasonId. It may also be worth
trying am index for the two combined. Answers to your
previos questions below.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald,

I'm not sure if I'm following your instructions properly.
This time all I did was cut and paste your code into my SQL and tried
to run it.
I'm prompted for a value for Detentions.DateDet.
Click to expand...

That was an error. The ORDER BY clause should have
included the Alias name not the Table name.
If I hit Enter or enter a date I get an hourglass and
Click to expand...
nothing happens.

The query would take a long time to run given that it needs
to find the minimum date per student based on whether the
reason id is 9 or not.
Oh one more thing. When I look at Design view, I see a new table D1
linked to Students on StudentID but the Detentions table
Click to expand...
isn't shown.

That is because I used the Alias D1 which was requird in
order to link it into the subqueries.
 
Hi Gerald,

I was able to get a query result with your code but it took over a
minute, more than 60 times slower than my usual code and the results
were not what I am expecting. For example, 45 records should have been
returned, but only 16 showed up. Also, for a particular student,
ReasonID 9 showed up with a Date of 13-Apr-04 but Reason 2 should have
showed up with Date 14-Apr-04.

The Detentions table _is indexed for StudentID and ReasonID.

I'll give you the contents of the four main tables involved:
Detentions:
DetID, StudentID, Status, TeacherID, ReasonID, DateDet, Notes
Students:
StudentID, LastName, FirstName
Teachers:
TeacherID, LastName, FirstName, Password, CLCount
Reasons:
ReasonID, Reason

So basically the query that I need should return the following:
All Detentions records where Status IS NULL. with the earliest
ReasonID excluding Reason 9 *UNLESS* Reason 9 is the only reason for
that particular student's set of records.

This information is going to take a while to produce given
the requirement to find the minimum of two dates per
student.
Click to expand...

Well, it's the minimum of the DateDet field. There could be 10
records per student, but there's just one date field.

I hope I've made myself clearer this time.

Thans again, I really appreciate it. Eric
 
Eric

It looks as if this is beyond my level of SQL expertise.
At least I have spotted why the wrong date was being picked
up. The HAVING clauses should have been HAVING Count(1) =
Sum(IIf(reasonId=9,1,0)). Also, the WHERE status IS Null
clause should also be applied to the two parts of the UNION
query.

The stumbling block for me is the requirement to ignore the
rows where reasonId = 9 rows unless that was the only
reasonId for a student and I cannot hink of a way of
fulfilling that in a timely fashion.

The original response on this thread tried looking for the
minimum of two dates to allow for the possiblilty that the
student could have rows with reasonId of 9 only. The union
query tried instead to identify whether the student was one
only associated to reasonId 9 or not and then find the
minimum date accordingly.

As neither approach brings back the desired results in a
timely fashion, I am all out of suggestions for a pass
through query. Maybe you could try building a temp table
but that would still be time-consuming.

Gerald Stanley MCSD
 
Hi Gerald,

Thanks for your help. I really appreciate it, even though we weren't
able to come up with a solution at this time.

Eric
 
Back
Top