Query selecting NOT Attended

[Brigitte P]

I have a Seminar table and an Attendance table, seminars and Attendance
joint by Seminar Number (Attendance table also has attendees Social Security
Number). Some seminars are mandatory and I have a field marking them as such
(simple "Yes") Now I need to query all these persons out that have NOT
attended mandatory training sessions, but they may have attended sessions
that are not marked as mandatory..
My query has both tables, with a joint that selects all from Attendance and
only those from Seminars that meet the mandatory criteria. Then I set in the
Attendance Social Security Number (Seminar Number field should have worked
also), to "Is Null" (the field is a text field).
Doesn't work; it displays only those who have attended, not those who have
NOT attended.
I thought it was simple, but I'm thinking wrong someplace. PLEASE HELP!
Brigitte P.

 

[Ken Snell [MVP]]

From where do you get the list of all possible attendees? Your description
seems to be of a table structure that has a table of seminars that can be
taken, and a list of attendees for each seminar (those who attended that
seminar). As such, your data cannot show which attendees still need to take
mandatory seminars because you don't have table showing all the possible
attendees.

 

[Steve Schapel]

Brigitte,

It seems to me that you also need an Attendees table into the mix here.
Do you have such in your database?

 

[Brigitte P]

Yes, I have a third table with all Staff which is connected by Social
Security Number to the Attendee table. (Attendee table has nothing but
Social Security Number and Seminar Number with one connected to the matching
field in the Staff table and the other to the matching field in the Seminar
table). I think it is normalized right.
I think I could connect the Staff table after I selected those that didn't
have the mandatory training (to pull names etc). After I posted, I kind of
understand why my original query didn't work. So I tried to first do a query
with all records from the Staff table, and those from the Attendees table
that have a related record (which should show who didn't have any training
at all). However, all have training because they have other than mandatory
training.
Seems to me that I have to do a query that connects seminar and attendance,
then check where the seminar number and attendance seminar number don't
match, and then look for Null values. But it doesn't work and it's a wild
query with thousands and thousands of records to compare. I'm still puzzled
and I think I struggled with this before.
Any help is still very much appreciated, and if I figure it out in between,
I'll post the solution to maybe help others. I noticed a similar post in the
group and the Is Null solution was given, but it doesn't work in my
instance.
Brigitte P

 

[Steve Schapel]

Brigitte,

The most straighforward way to do this is in two steps. First create a
query, let's say it is named MandatoryRequired, to return a list of all
staff who are supposed to attend the madatory seminars. The SQL view of
such a query will look something like...
SELECT Staff.[Social Security Number], Seminar.[Seminar Number]
FROM Seminar, Staff
WHERE Seminar.Mandatory="Yes"

Then, make a second query, based on this first query and also the
Attendee table, to show the non-attenders. The SQL view will be
something like this...
SELECT MandatoryRequired.[Social Security Number],
MandatoryRequired.[Seminar Number]
FROM MandatoryRequired LEFT JOIN Attendees ON
(MandatoryRequired.[Seminar Number] = Attendees.[Seminar Number])
AND (MandatoryRequired.[Social Security Number] = Attendees.[Social
Security Number])
WHERE (Attendees.[Social Security Number] Is Null) AND
(Attendees.[Seminar Number] Is Null)

 

[Brigitte P]

Sorry to be so dense, but the problem is that I can't connect the Staff and
the Seminar tables.
Let me explain:
Table Staff has SSN + Name + Department etc. - there is nothing that says
mandatory, it's just demographics
Table Seminars has SeminarNumber, Title, Presenter, Date etc.
Table Attendance has StudentSSN + NumberSeminar
I link Attendance StudentSSN to Staff SSN and Attendance to Seminars
SeminarNumber (actually, the other way around since they are all primary
keys, but in the Seminar and Staff table, they are unique, in the Attendance
table the combo of the two keys is unique.
I can see the direction you are going, but my head is spinning as long as I
work on this issue. Can you direct me a little more, please?
Thank you.
Brigitte

Brigitte,

The most straighforward way to do this is in two steps. First create a
query, let's say it is named MandatoryRequired, to return a list of all
staff who are supposed to attend the madatory seminars. The SQL view of
such a query will look something like...
SELECT Staff.[Social Security Number], Seminar.[Seminar Number]
FROM Seminar, Staff
WHERE Seminar.Mandatory="Yes"

Then, make a second query, based on this first query and also the
Attendee table, to show the non-attenders. The SQL view will be
something like this...
SELECT MandatoryRequired.[Social Security Number],
MandatoryRequired.[Seminar Number]
FROM MandatoryRequired LEFT JOIN Attendees ON
(MandatoryRequired.[Seminar Number] = Attendees.[Seminar Number])
AND (MandatoryRequired.[Social Security Number] = Attendees.[Social
Security Number])
WHERE (Attendees.[Social Security Number] Is Null) AND
(Attendees.[Seminar Number] Is Null)

--
Steve Schapel, Microsoft Access MVP

Yes, I have a third table with all Staff which is connected by Social
Security Number to the Attendee table. (Attendee table has nothing but
Social Security Number and Seminar Number with one connected to the matching
field in the Staff table and the other to the matching field in the Seminar
table). I think it is normalized right.
I think I could connect the Staff table after I selected those that didn't
have the mandatory training (to pull names etc). After I posted, I kind of
understand why my original query didn't work. So I tried to first do a query
with all records from the Staff table, and those from the Attendees table
that have a related record (which should show who didn't have any training
at all). However, all have training because they have other than mandatory
training.
Seems to me that I have to do a query that connects seminar and attendance,
then check where the seminar number and attendance seminar number don't
match, and then look for Null values. But it doesn't work and it's a wild
query with thousands and thousands of records to compare. I'm still puzzled
and I think I struggled with this before.
Any help is still very much appreciated, and if I figure it out in between,
I'll post the solution to maybe help others. I noticed a similar post in the
group and the Is Null solution was given, but it doesn't work in my
instance.
Brigitte P

 

[Brigitte P]

GOT IT! It didn't quiet work this way because my tables are a little
different, but it got me in the right direction. Please ignore my previous
follow up post where I explained my table structure.
As always, many thanks for your quick and wonderful help. I have learned so
very much from these groups.
Brigitte P.

Brigitte,

The most straighforward way to do this is in two steps. First create a
query, let's say it is named MandatoryRequired, to return a list of all
staff who are supposed to attend the madatory seminars. The SQL view of
such a query will look something like...
SELECT Staff.[Social Security Number], Seminar.[Seminar Number]
FROM Seminar, Staff
WHERE Seminar.Mandatory="Yes"

Then, make a second query, based on this first query and also the
Attendee table, to show the non-attenders. The SQL view will be
something like this...
SELECT MandatoryRequired.[Social Security Number],
MandatoryRequired.[Seminar Number]
FROM MandatoryRequired LEFT JOIN Attendees ON
(MandatoryRequired.[Seminar Number] = Attendees.[Seminar Number])
AND (MandatoryRequired.[Social Security Number] = Attendees.[Social
Security Number])
WHERE (Attendees.[Social Security Number] Is Null) AND
(Attendees.[Seminar Number] Is Null)

--
Steve Schapel, Microsoft Access MVP

Yes, I have a third table with all Staff which is connected by Social
Security Number to the Attendee table. (Attendee table has nothing but
Social Security Number and Seminar Number with one connected to the matching
field in the Staff table and the other to the matching field in the Seminar
table). I think it is normalized right.
I think I could connect the Staff table after I selected those that didn't
have the mandatory training (to pull names etc). After I posted, I kind of
understand why my original query didn't work. So I tried to first do a query
with all records from the Staff table, and those from the Attendees table
that have a related record (which should show who didn't have any training
at all). However, all have training because they have other than mandatory
training.
Seems to me that I have to do a query that connects seminar and attendance,
then check where the seminar number and attendance seminar number don't
match, and then look for Null values. But it doesn't work and it's a wild
query with thousands and thousands of records to compare. I'm still puzzled
and I think I struggled with this before.
Any help is still very much appreciated, and if I figure it out in between,
I'll post the solution to maybe help others. I noticed a similar post in the
group and the Is Null solution was given, but it doesn't work in my
instance.
Brigitte P

 

[Steve Schapel]

Brigitte,

Glad to know we have made progress. Let us know if you need any more help.

As a side issue, it looks like you are usiung Social Security Number as
a primary key field, and as the basis of relationships between tables.
This is generally not a good idea, and I would recommend an AutoNumber
primary key field.

 

[Brigitte P]

THANKS. Why is the SSN not a good idea. I thought any unique value that also
exists in the related table would work fine. I have a one-to-many
relationship on the SSN, that is one in Staff and many in Attendance since
the same staff attend several seminars.
I have some other databases where I use the AutoNumber because there are no
unique other values, in fact, the attendance table has an AutoNumber Key,
but the primary index is the combination of SSN and SeminarNumber. I read
someplace that AutoNumbers may reset themselves when you compact the
database. I also sometimes archive part of the databases, but may need to
pull them back together later. If it is true that AutoNumber fields may
reset themselves, then my relationships would be wrong if I would have the
AutoNumber as primary index and relate this to other tables. Maybe I have it
all wrong, but I'm a bit concerned now, because I have a huge staff database
that runs for several years without problems. But all connections are by
Staff SSN.
Thanks for any additional input.
Brigitte P.

 

[Steve Schapel]

Brigitte,

To be honest, I don't really know what a SSN is, not being an American.
I simply passed on the suggestion based on the advice of others whose
opinion I respect. A Primary Key should be something that is universal
and unique. My understanding is that even within the U.S., SSNs are
neither. There are millions of people who don't have one, and there are
millions of people who use forged, invalid, duplicated numbers.

In Access 97 and before, compacting would reset the seed for an
Autonumber (i.e. the number to be used for the next new record) to 1
greater than the exisiting highest number. This does not apply in later
versions, and in any case is unlikely to be a problem in the type of
scenario you are working with.

 

[Brigitte P]

Thanks. SSN is common in the US for Social Security Number. I'm also not
American but live here for 30 + years, so I've learned the "lingo." A SSN is
suppose to be unique, that is, one person - one number. But you are right,
who knows. Thanks for the info regarding the AutoNumber; my databases are in
Access XP, but I'm helping someone from a sister facility, and they are
still in A97, but probably not much longer. So the AutoNumber issue becomes
a non-issue.
Again, thank you so much for your help.
Brigitte P.