Query Help

  • Thread starter Thread starter John Menken
  • Start date Start date
J

John Menken

I have two tables that you see below. The first table called
CompletesAndNonCompletes holds information on who completed a course
in the company. If the field called CompletionDate is not null then
that means that the person completed the course. (in other words if
there is a date in this field then that means that the person
completed the course.)

The second table entitled CoachingSession is a table on everyone that
took a follow up coaching session after they completed the course.
Username from the first table and ID4LIFE from the second table are
the related fields. A person that completed the course may have more
than one coaching session. I am trying [unsuccessfully] to create a
query that simply gives me a list of everyone that has completed the
course but has not yet had a coaching session. I am using Access 2007.
If anyone could help me with this I would be extremely appreciative.
Thank you

Table: CompletesAndNonCompletes
ID
Firstname
LastName
Username
CourseName
CompletionStatus
CompletionDate

Table: CoachingSession
CoachingRecordID
FirstName
LastName
ID4LIFE
NameOfCoach
DateOfCoachingSession
NotesFromCoachingSession
DateofNextCoachingSession
CommentsFromParticipant
CommentsFromCoach
FormPic
 
I have two tables that you see below. The first table called
CompletesAndNonCompletes holds information on who completed a course
in the company. If the field called CompletionDate is not null then
that means that the person completed the course. (in other words if
there is a date in this field then that means that the person
completed the course.)

The second table entitled CoachingSession is a table on everyone that
took a follow up coaching session after they completed the course.
Username from the first table and ID4LIFE from the second table are
the related fields. A person that completed the course may have more
than one coaching session. I am trying [unsuccessfully] to create a
query that simply gives me a list of everyone that has completed the
course but has not yet had a coaching session. I am using Access 2007.
If anyone could help me with this I would be extremely appreciative.
Thank you

Table: CompletesAndNonCompletes
ID
Firstname
LastName
Username
CourseName
CompletionStatus
CompletionDate

Table: CoachingSession
CoachingRecordID
FirstName
LastName
ID4LIFE
NameOfCoach
DateOfCoachingSession
NotesFromCoachingSession
DateofNextCoachingSession
CommentsFromParticipant
CommentsFromCoach
FormPic
Click to expand...

I don’t understand your table structure.

The Username in table CompletesAndNonCompletes may not be unique (a
person can have completed more than one course). So connecting
CompletesAndNonCompletes and CoachingSession on username won’t work.
You can’t tell which course the CoachingSes-sion belongs to.

In the CompletesAndNonCompletes you have 3 person related fields
(first name, last name and username). That means that for each course
a person takes you have to store these three fields. One should store
a person’s name only once.

If the names in CoachingSession are not the name of the choach you are
in even bigger trouble.

I suggest you first reorganize your tables.


Groeten,

Peter
http://access.xps350.com
 
Hi John.

I think you can use the unmatched query wizard. Join the 2 tables on the
columns that you indicated. Once the query is built, open it in design view,
and add the not null criteria to your date column.
 
XPS350,
Sorry, I should've mentioned that there is only *one* course in this
situation.
Thanks.
 
XPS350,
Sorry, I should've mentioned that there is only *one* course in this
situation.
Thanks.
Click to expand...

As Chris said, the unmatched query wizard should do the job.
It results in something like:

SELECT CompletesAndNonCompletes.*
FROM CompletesAndNonCompletes LEFT JOIN CoachingSession ON
CompletesAndNonCompletes.Username = CoachingSession.ID4LIFE
WHERE CoachingSession.ID4LIFE Is Null;

Groeten,

Peter
http://access.xps350.com
 
Back
Top