Not exists

[Serendipityww]

How would write sql not exists code to find records of students who have not received earned awards?

I need to look at each student (tblJPerAwardsPacesStatus.fldFamIDFK) who has a status of earned and/or awarded in the tblJPerAwardsPacesStatus.fldStatusIDFK.

I want my query to return a list of students who earned awards but who have not been awarded awards. E.g.,

(these fields are all from the tblJPerAwardsPacesStatus table)
fldFamIDFK....fldPaceNameIDFK.....fldStatusIDFK
19 (Susie).....01 (Lesson 1).........01 (earned)
19...............01........................02 (awarded)

102 (Eric).....01 (Lesson 1).........01 (earned)

I would want my query to return Eric but not Susie because Susie already got her award.

Or if there is a better way, I am open to that.

Thanks for your help.

 

[MarkK]

In this case you can just count records that satisfy criteria. If the result is zero, the item doesn't exist. You can use the Access.Application.DCount() function, or write a query like . . .

SELECT Count(*) FROM tYourTable 
WHERE FamID = 102
   AND StatusID = 2

 

[Serendipityww]

I put this in

SELECT Count(*) FROM tblJPerXAwardsPacesStatus
WHERE fldFamIDFK = 503
AND fldFamIDFK = 3

and got the result of 3512 under a column heading of Expr1.

Also, when I ran the query, it asked me for values for fldFamIDFK and fldFamIDFK.

I didn 't get a list of student who have a status of "earned" but do not have a status of "awarded" for that same pace ID.

 

[MarkK]

My point is to use DCount() or Count(*) and if the result is zero, then it doesn't exist.

If you don't want an automatic column name, provide one, like . . .

SELECT Count(*) [COLOR="Blue"]As YourColumnNameHere[/COLOR] FROM tYourTable

And maybe to answer your requirement you need to do two counts, one to determine if earned, and the next to determine if awarded.

One option there is to use Access.Application.DSum() or the Sum() function in SQL.

SELECT Abs(Sum(StatusID = 1)) as EarnedCount, Abs(Sum(StatusID = 2)) As AwardedCount
FROM SomeTable
WHERE FamID = 102

So there, when we do Status = 1, that will either be True or False, and True is -1, so we can count True. We Abs() them so they aren't negative at the end.

Hope that helps,

 

[Serendipityww]

How will I get a list of all students who meet the criteria, if I am specifying a specific student id?

 

[MarkK]

How do you want it to work? You can write a query that uses what we've done here to find a result for a single FamID, which is what the stuff I've shown does. You can also write a query with a GROUP BY clause on FamID and return many rows of summarized info, one ID per row.

But do you have something working over there?

 

[Serendipityww]

I have not gotten anything to work.
When a student earns an award, he/she gets a record in the database with status of "earned."
Then when that same student with that same paceID gets the award, he/she gets another record in the database with status of "awarded."
I want a list of all students who have earned awards but have not been awarded awards so that I can print out a list and prepare the awards for presentation. (See my illustration in my initial post for an example.)
Thanks for your patience. I am new at this.

 

[MarkK]

Describe that point in the process where what you know how to do ends.

1) Do you know how to create a query? Yes, ok, 2) do you know how to add a table to a query? Yes, ok, 3) do you know how to . . .
. . . and at a certain point, the answer will be no. That is where the problem is. That is what you need to describe.

Be very, very specific.

Hope this helps,