Restricting query result

[Eric G]

A main report is based on the following query:

SELECT Detentions.StudentID, Count(Detentions.StudentID) AS
CountOfStudentID, First(Detentions.TeacherID) AS FirstOfTeacherID,
First(Detentions.ReasonID) AS FirstOfReasonID, 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;


Would someone be able to tell me how I can change the code so that
First(Detentions.ReasonID) AS FirstOfReasonID *excludes* reason 9,
*unless* there are no other reasons except for reason 9 ?

TIA Eric

 

[Jeff Boyce]

Eric

You may want to re-check Access HELP for a description of the First()
aggregation function -- there's a really good chance that it is not doing
what you believe it is.

If you are looking for the smallest ID # (?first entered, if sequential), or
the earliest date/time, consider using the Min() function instead.

When you say

*unless* there are no other reasons except for reason 9 ?

it makes me wonder how there could be more than one reason "9"? Since we
can't see your data structure, nor your data, this could be tough to
diagnose.

 

[Eric G]

Hi Jeff,

Thanks for helping out.

You may want to re-check Access HELP for a description of the First()
aggregation function -- there's a really good chance that it is not doing
what you believe it is.

It _seems to be working quite nicely. I just want to refine the
report a little bit.

If you are looking for the smallest ID # (?first entered, if sequential), or
the earliest date/time, consider using the Min() function instead.

Typical data in the table would look like:

DetID StudentID Status AssignBy ReasonID Date
12 367 Sus MSA 3 27-Oct-03
14 575 Sus SMA 1 27-Oct-03
15 357 SMA 1 27-Oct-03
18 539 . AHA 7 28-Oct-03
24 498 Oth AHA 7 28-Oct-03


We would be looking for the First Reason in a list of multiple records
for a student whose Status was 'blank'. So not smallest reason number,
but the first chronologically (earliest).

When you say
it makes me wonder how there could be more than one reason "9"? Since we
can't see your data structure, nor your data, this could be tough to
diagnose.

So a given student might have the following records in the table:

DetID StudentID Status AssignBy ReasonID Date
28 367 MSA 9 27-Oct-03
32 367 SMA 1 28-Oct-03
42 367 SMA 1 29-Oct-03
48 367 EGR 3 30-Oct-03

In which case the query now returns Reason 9 as the "First" reason.
But I would like 9 to be excluded from the query selection. So in this
case I would like Reason 1 to be returned as "First".
If however, there are only records for a student (whose Status is
blank) with Reason 9, then return 9.

I hope it's clearer now.
Thanks again! Eric

 

[Jeff Boyce]

Eric

Can you also have a situation where there are none? You've described
multiple, including 9, and 9 only.

If you want the earliest chronological record, use Min([YourDateField]) ...
just because the First() isn't biting you now doesn't mean it won't!

Conceptually, it sounds like you need to know how many "reason" records
there are for a student, how many 9s there are, and need to make a decision
based on the comparison.

Because you'd need to select a different row if there's more than one and if
not all are 9s, one approach would be to write a procedure to do this.
Again, conceptually:

Return all rows (for a student)
Count the rows
Count the number of rows that are NOT 9s
If # of 9s is 0, and # of rows is at least 1, then
Re-query the source, using Min([YourDateField]) and, as a selecting

otherwise, if count of 9s is at least 1 and matches count of rows, then
use the Min([YourDateField]) with = 9 as the criterion

 

[Eric G]

HI Jeff,

Can you also have a situation where there are none? You've described
multiple, including 9, and 9 only.

You can have no 9's or none of any Reason ID number. No 1's, no 2's
etc. You may have six records of just 4's. Really any possible
combination you can think of. There are 9 ReasonID's so any one may
pop up.

If you want the earliest chronological record, use Min([YourDateField]) ...
just because the First() isn't biting you now doesn't mean it won't!

I understand now. I am using Min in other queries for the same
reason. But this query slipped by.

Conceptually, it sounds like you need to know how many "reason" records
there are for a student, how many 9s there are, and need to make a decision
based on the comparison.

I don't think you are in total understanding of my situation. It is
not required to know how many 9's there are. As long as there are _any
reasons in addition to a 9, show the earliest of the other reasons.
If there are _only Reason 9's, then show Reason 9. It could be the
earliest, but any will do.

Because you'd need to select a different row if there's more than one and if
not all are 9s, one approach would be to write a procedure to do this.
Again, conceptually:

Return all rows (for a student)
Count the rows
Count the number of rows that are NOT 9s
If # of 9s is 0, and # of rows is at least 1, then
Re-query the source, using Min([YourDateField]) and, as a selecting

otherwise, if count of 9s is at least 1 and matches count of rows, then
use the Min([YourDateField]) with = 9 as the criterion

The first 5 rows would work.

otherwise, if count of 9s is at least 1 and matches count of rows, then
use the Min([YourDateField]) with = 9 as the criterion

This part doesn't seem quite right.
You could have a count of say, two 9's and a count of six rows.
In that case, ignore the 9's and show the earliest of the other four
rows.
And of course you could have something like you state above; a count
of say three 9's, and a count of three rows. Then the earliest of the
9's, or really any of them would do.

Thanks again! Eric

 

[Jeff Boyce]

You're welcome. I'm not there, so I wouldn't expect to get it right the
first time. My post was more for the thought process, and it sounds like it
helped you clarify what you need to do.

Regards

Jeff Boyce
<Access MVP>

 

[Eric G]

You're welcome. I'm not there, so I wouldn't expect to get it right the

first time. My post was more for the thought process, and it sounds like it
helped you clarify what you need to do.

Regards

Jeff Boyce
<Access MVP>

Thanks Jeff.
I'll have a go at it and see if I can come up with the query.

 

[Eric G]

You're welcome. I'm not there, so I wouldn't expect to get it right the

first time. My post was more for the thought process, and it sounds like it
helped you clarify what you need to do.

Regards

Jeff Boyce
<Access MVP>

Hmmmm,...

Now it looks like I'm back to square one.

First, when I changed to DetDate Min to find the earliest of the
records, I started to get multiple listings for each teacher's
assigned detentions, rather than the earliest. If I added ReasonID
First, then I'd eliminate these multiple listings.

This may have been the reason that I wasn't going with DetDate Min in
this query. A few questions.

Can I achieve the results I need with one query or will more be
required?

Following your outline, how can I count the rows that are not ReasonID
9 ?
If I put in 9 or "9" for the Criteria while using Count as the total,
it doesn't produce the desired results.

Thanks again for your patience. Eric

 

[Jeff Boyce]

Actually, Eric, I wasn't suggesting staying in "queries" at all. The
general approach I outlined would be done in a procedure. My rationale for
suggesting this approach is because it seemed like you'd need to know if
there were any other than "9s" (and do one thing), or only "9s" (and do
another).

If you were only working with a value that would differ, you could use the
IIF() function in a query. But since you are looking to entirely different
rows, it seemed more like you'd need to use code to check the two
possibilities (probably via recordsets), then create a SQL statement on the
fly, based on the outcome of your check. THAT SQL statement would then get
run to return the date value from the appropriate row.

 

[Eric G]

Hi Jeff,

Actually, Eric, I wasn't suggesting staying in "queries" at all. The
general approach I outlined would be done in a procedure. My rationale for
suggesting this approach is because it seemed like you'd need to know if
there were any other than "9s" (and do one thing), or only "9s" (and do
another).
......
Count the rows
Count the number of rows that are NOT 9s
If # of 9s is 0, and # of rows is at least 1, then
Re-query the source, using Min([YourDateField]) and, as a

selecting

otherwise, if count of 9s is at least 1 and matches count of rows, then
use the Min([YourDateField]) with = 9 as the criterion

I've written procedures throughout my db but simple ones, like 'run
this query' or 'open that form' etc.
Would you be able to give me some guidance as to how to code for some
of the above? Recordset language seems very tricky.
When I create a query in Design view the SQL is automatically created.
It seems I don't have any of this type of help when procedures need to
be created.

Thanks! Eric