Hi Seona, I like to use a LEFT OUTER JOIN for a query like this. It would look something like
SELECT u.emailaddress, u.firstname, u.surname, u.rec_id, bde.bde_entrydate FROM users u INNER JOIN body_diaries bd ON u.rec_id = bd.bdi_usr_id left outer join JOIN body_diary_entries bde ON bd.bdi_id = bde.bdi_id where bde.bdi_id is null which should return users that have body_diaries, but NO body_diary_entries. you can add in the left outer join clause the date parameter for that day. eg left outer join JOIN body_diary_entries bde ON bd.bdi_id = bde.bdi_id and entry_date > #CreateODBCDate( todays_date )# Scott Thornton, Programmer Billing Unit Hunter-New England Area Health Service ext: 51762 p: +61 02 491 51762 m: 0413 800 242 >>> "Seona Bellamy" <[EMAIL PROTECTED]> 22/08/2008 11:54 am >>> Hi all! Have a system that people are supposed to log into every day and make an entry. I've been asked to created a scheduled task that every evening will look for all of the users who haven't made an entry today and send them a reminder email, but I'm having trouble getting me head around how to get that information. I have the following query, called "qReminders", that gets all of the entries: SELECT u.emailaddress, u.firstname, u.surname, u.rec_id, bde.bde_entrydate FROM users u INNER JOIN body_diaries bd ON u.rec_id = bd.bdi_usr_id INNER JOIN body_diary_entries bde ON bd.bdi_id = bde.bde_bdi_id WHERE u.user_level = 1 ORDER BY u.rec_id, bde.bde_entrydate So then I thought I'd do a query-of-query on this one to get the details of all the people who haven't made an entry today, but I can't figure out what the filtering clauses would be. Just getting the records that aren't today's won't work. I need a way to say "where bde_entrydate does not include today's date in one of the records returned for this user". Is this even a good way to go about it? I'm concerned that doing an initial query that gets everything could become quite stupidly large over time. Cheers, Seona. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---