Find / Join question 1

[wonford]

I have a small application with 3 tables STAFF, DATES and WEEKLY ABSENCES.

The DATES table contains the 52 week commencing dates for the year and is joined to the week commencing field on the WEEKLY ABSENCE table. The STAFF table contains all members of staff and is joined to the STAFF ID field on the WEEKLY ABSENCE table.

Each week all members of STAFF should create a WEEKLY ABSENCE record (to show if they have been absent i.e. Holiday, Sick, Course etc or not)

However, I want to ensure that everyone has filled in their weekly record (by creating a WEEKLY ABSENCE record), but how can I get a list of STAFF who have NOT filled in a record for a given week.

It’s probably dead easy, but I just cannot see how to do it. Any ideas ??

Cheers !

Jerry

 

[paulbent]

Create a variable field (type date) and put it on the Staff form to enter the w/c date to search on. Say you name it vDate.

Create a calculated field, substituting your own table and field names:

SSum(If(WeeklyAbsence.Date = vDate, 1, 0))

and set the summary option to "All records in WeeklyAbsence"

If you put this field on a form that has Staff as its main table, you can go into find mode and enter 0 in it as the find condition.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[wonford]

Cheers Paul, that worked fine. See I knew it would be simple for someone !!
--- Jerry ---