advanced filtering/function question

[TLuebke]

I am querying a Scholarship database to find records for use in
Award/Rejection letters. I'm using a checkbox called Award with yes/no in the
criteria. pulling the yes's for an award letter is easy or when pulling
someone who didn't get anything.

The problem comes when a student with multiple applications is awarded one
and rejected for others. The DB users wants it setup so that if there is an
award for one Scholarship the student doesn't recieve rejection letters for
other scholarships applied for.

How do I structure the query so that if a student has an award they are
dropped from the rejection record set?

Thanks,

Todd

 

[Gary Walter]

"TLuebke"wrote:

I am querying a Scholarship database to find records for use in
Award/Rejection letters. I'm using a checkbox called Award with yes/no in
the
criteria. pulling the yes's for an award letter is easy or when pulling
someone who didn't get anything.

The problem comes when a student with multiple applications is awarded one
and rejected for others. The DB users wants it setup so that if there is
an
award for one Scholarship the student doesn't recieve rejection letters
for
other scholarships applied for.

How do I structure the query so that if a student has an award they are
dropped from the rejection record set?

In a loose sense...
I think your "rejection letter" query would look like:

SELECT
.....
FROM
Scholarship
WHERE
[Award] = 0
AND
(SELECT
Min(t.Award=-1)
FROM
Scholarship As t
Group By t.StudentID
WHERE
t.StudentID=Scholarship.StudentID)>-1;

you might double-check my logic, but I think....

if *all* Awards for a StudentID were 0 (No),
Min(t.Award=-1) would be 0

if all Awards for a StudentID were -1 (Yes),
Min(t.Award=-1) would be -1

if some Awards were 0, and at least one was -1,
Min(t.Award=-1) would be -1

the expression in Min(xxx) may have to be changed
if any Award fields are Null

Min(Nz(t.Award,-1)=-1)

 

[Gary Walter]

If you have a lot of data (and since this
is for a report where you won't need to
edit results), it may be more efficient to
create a preliminary query and join to
Scholarship in query for your report.

qryPreLim

SELECT
t.StudentID
FROM
Scholarship As t
Group By
t.StudentID
WHERE
Min(t.Award=-1) >-1;

"TLuebke"wrote:

I am querying a Scholarship database to find records for use in
Award/Rejection letters. I'm using a checkbox called Award with yes/no in
the
criteria. pulling the yes's for an award letter is easy or when pulling
someone who didn't get anything.

The problem comes when a student with multiple applications is awarded
one
and rejected for others. The DB users wants it setup so that if there is
an
award for one Scholarship the student doesn't recieve rejection letters
for
other scholarships applied for.

How do I structure the query so that if a student has an award they are
dropped from the rejection record set?

In a loose sense...
I think your "rejection letter" query would look like:

SELECT
....
FROM
Scholarship
WHERE
[Award] = 0
AND
(SELECT
Min(t.Award=-1)
FROM
Scholarship As t
Group By t.StudentID
WHERE
t.StudentID=Scholarship.StudentID)>-1;

you might double-check my logic, but I think....

if *all* Awards for a StudentID were 0 (No),
Min(t.Award=-1) would be 0

if all Awards for a StudentID were -1 (Yes),
Min(t.Award=-1) would be -1

if some Awards were 0, and at least one was -1,
Min(t.Award=-1) would be -1

the expression in Min(xxx) may have to be changed
if any Award fields are Null

Min(Nz(t.Award,-1)=-1)

 

[John Spencer]

Rejections:

SELECT StudentID
FROM Scholarships
WHERE StudentID IN
(SELECT StudentID
FROM Scholarships
GROUP BY StudentID
Having Sum(Award) <> 0)

 

[TLuebke]

Thanks Guys I'll give them a try.

Rejections:

SELECT StudentID
FROM Scholarships
WHERE StudentID IN
(SELECT StudentID
FROM Scholarships
GROUP BY StudentID
Having Sum(Award) <> 0)

 

[TLuebke]

Thanks John,
Not only did this solve my issue but I learned you can put a qry in the
criteria field. This answered another issue I was dealing with