It's possible to design your interface so that the user cannot create the child record without also creating the parent record. One method being to make the foreign key field that links it to the parent a required value and then also add a validation calculaiton that checks for the existance of the related parent record or a validation rule that requires that the foreign key field's value be a member of a defined value list--a value list of exiting Parent record primary keys.
Another method is to require the user to create the child record in a portal to the child table from a layout based on the parent record.
Wouldn't that be better than checking once a month for missing parent records? (That can be done, it just seems better interface design to catch the error at the time the omission occurs rather than days/weeks after the fact.)
thanks Phil basically the user has to enter a parent record along with the child records once in every month, so if the user has not entered the record I want the database to auto generate an email.
So the presence of a child record without a parent is not the issue, you just need to confirm whether or not a new parent record was added since the last deadline.
Presumably you have some kind of data field that identifies the month and year for which such a record applies and these records are then related to a table of Personnel? You could have a date field with the date of the first day of the month, year in question or separate fields for month and year and this still works though a date field can be easier to work with in some cases as you only need one field instead of two.
It should be fairly simple to find all personnel records that have a related record dated for the month, year in question. Then Show Omitted Only will bring up all of those that do not and you can then have your script send out that list of personnel to the appropriate recipient. Individual reminder emails to the personnel on the list can also be sent.