Still a little confused, what series? That particular property's series? Or the batch of letter's series? Could multiple letters get 'B001' printed on them?
In either case I think we are missing a field in Letters---[Letter_Num] which will designate which version of the letter is being sent.
^ That's perfect, basically to simply as possible every letter1 has ref 001 (with B/C in front if it's Block or Canvas) Every letter2 has ref 002 (with B/C in front if it's Block or Canvas)
Thus if we have a Letter_Dept (which is either Block/Canvas) and Letter_Num the reference can be deduced from this by using firstletter of Dept+Letter_Num
Also, since you have formal logic as to when letters should be due, you do not need to store that data. I think Letters should only hold data about letters that have been sent. We will be able to use a query to determine when next letters are due to be sent and from there can generate a list of data to feed your letters.
Yup, that's what I did previously with other databases, (I think I posted my SQL Query or an extract of it on page 1, so to work out what letter needs sent was picked by running a query
Do you have a database file for this? Something with the tables we have talked about and some sample data in them? If so, can you post it?
I have a DB file, I'll post a final DB Schema to make sure you're happy, I have no forms just tables currently. I can post a file with Sample data,
How much sample data would you like? I could provide 4/5 landlords and 12-20 properties, or a larger sample. ie few hundred?