Warning!!! this is a lonnnnnnng message.
I have an app that sends out letters on a timed schedule to property owners-
letter1 - today
letter2 - 30 days later
letter3 - 60 days later etc.

I will also send other correspondence that needs to be tracked such as certified letters, and special info letters.

the table that tracks these events is structured with a field for every possible type of letter labeled accordingly i.e letter1 letter2 letter3 etc.(date fields) I have run into a slight problem when I either need to send out letter4(which I currently don't have a field for, or if I need to start the process over again starting at letter 1. this table is related to a master table via a tax parcel code. I currently have a one to one relation to the master table. I would like to change this table to a "many" and instead of having a field for each event, have one field that will hold the event type, and a corresponding date field to log the date that event happened. (1st normal form?)

field1 (holds parcel code for relating to master table)
field2 (holds type of event i.e letter1, certified etc)
field3 (holds date this event occurred)

Using sql to query out all records that need to be sent a follow up letter is fairly straight foreward when I have all possible fields in the same table. I do something like this now.

SELECT master.*;
FROM master INNER JOIN events ;
ON master.parcel = Events.parcel ;
where !EMPTY(events.ltr1) ;
and events.ltr1 < date() - 30 ;
and EMPTY(ltr2)

This result will give me all records where ltr1 has been sent more than 30 days ago and ltr2 hasn't been sent yet. I can then run the results into my report to print them out a letter 2.

Since I can check the ltr1, and ltr2 values I can determine if ltr2 needs to be sent very easily. If my events table is a many type table (one record per event), when I join the tables I can't retrieve just the records in the events table that have been sent ltr1 and are ready for ltr2, atleast not with one select statement because I can't evaluate two rows of the events table with one select. How can I join the master table to my events table(many) and just retreive those records that have been sent ltr1 more than 30 days ago and haven't been sent ltr2?

If anyone can make sense out of my garble, and has suggestions for me, I would greatly appreciate.

Thanks.

john.