L
LeeTV
Hi,
I'm using the Event Management database (Microsoft product).
In the database I have had to add one or two extra fields and customize it
to my needs.
The field I’ve added in the Registration table is a "CourseExprationDate"
field. Most people that take a course have to come back before the expiration
date and take either a renewal/full course over again. This is for CPR /
First Aid classes.
In order to track who is expiring and mail merge to an email I'm trying to
build a query that will show me the people that are expiring.
The query that I have is as follows: (SQL below)
SELECT Attendees.AttendeeID, Attendees.AttendeeTitle,
Attendees.AttendeeFirstName, Attendees.AttendeeMiddleName,
Attendees.AttendeeLastName, Attendees.AttendeeCredentials, Attendees.Address,
Attendees.Address2, Attendees.City, Attendees.PostalCode,
Attendees.StateOrProvince, Attendees.PhoneNumber, Attendees.CellPhoneNumber,
Attendees.FaxNumber, Attendees.EmailName, [Event Types].EventType,
Registration.Role, Registration.CourseExpirationDate,
DLookUp("RegistrationID","Registration","attendeeid = [attendeeid] and
courseexpirationdate < now()") AS Renewed
FROM ((Attendees LEFT JOIN Registration ON Attendees.AttendeeID =
Registration.AttendeeID) LEFT JOIN Events ON Registration.EventID =
Events.EventID) LEFT JOIN [Event Types] ON Events.EventTypeID = [Event
Types].EventTypeID
WHERE (((Registration.Role)="student") AND
((Registration.CourseExpirationDate) Like "*2009*"));
What I really want to occur is to have this post the regisrationID if the
person has renewed their course. (The EventTypeID is the field that will help
identify if they have taken a new course, then the expiration date of that
record will be the selected record). I've tried using a Dlookup fxn in this
query, I’ve been able to modify the above DLookup fxn (the one above didn’t
give me any results and gave errors as to wanting to find fields/tables that
already existed). when I took out most to of the dlookup criteria I was able
to get it to look up the reg id, but every row in the recordset was set to
the same value.
I would appreciate any help that anyone maybe able to offer for this solution.
Thanks
Lee
I'm using the Event Management database (Microsoft product).
In the database I have had to add one or two extra fields and customize it
to my needs.
The field I’ve added in the Registration table is a "CourseExprationDate"
field. Most people that take a course have to come back before the expiration
date and take either a renewal/full course over again. This is for CPR /
First Aid classes.
In order to track who is expiring and mail merge to an email I'm trying to
build a query that will show me the people that are expiring.
The query that I have is as follows: (SQL below)
SELECT Attendees.AttendeeID, Attendees.AttendeeTitle,
Attendees.AttendeeFirstName, Attendees.AttendeeMiddleName,
Attendees.AttendeeLastName, Attendees.AttendeeCredentials, Attendees.Address,
Attendees.Address2, Attendees.City, Attendees.PostalCode,
Attendees.StateOrProvince, Attendees.PhoneNumber, Attendees.CellPhoneNumber,
Attendees.FaxNumber, Attendees.EmailName, [Event Types].EventType,
Registration.Role, Registration.CourseExpirationDate,
DLookUp("RegistrationID","Registration","attendeeid = [attendeeid] and
courseexpirationdate < now()") AS Renewed
FROM ((Attendees LEFT JOIN Registration ON Attendees.AttendeeID =
Registration.AttendeeID) LEFT JOIN Events ON Registration.EventID =
Events.EventID) LEFT JOIN [Event Types] ON Events.EventTypeID = [Event
Types].EventTypeID
WHERE (((Registration.Role)="student") AND
((Registration.CourseExpirationDate) Like "*2009*"));
What I really want to occur is to have this post the regisrationID if the
person has renewed their course. (The EventTypeID is the field that will help
identify if they have taken a new course, then the expiration date of that
record will be the selected record). I've tried using a Dlookup fxn in this
query, I’ve been able to modify the above DLookup fxn (the one above didn’t
give me any results and gave errors as to wanting to find fields/tables that
already existed). when I took out most to of the dlookup criteria I was able
to get it to look up the reg id, but every row in the recordset was set to
the same value.
I would appreciate any help that anyone maybe able to offer for this solution.
Thanks
Lee