Attached is an updated database with a few changes to accomodate this:
1. Added [SendLetters] field to Property table. This will determine if a property is the one generating letters for a landlord. Upon entering a new property the system will have to see if the landlord has any current letters for that Type, if not it would mark this field as 'Yes'.
2. Added LetterDurations query. This is a subquery that determines how many days there are from PropertyEnteredDate until letter26 is due.
3. Added ActiveLandLordLetters query. This determines all the landlord/Propertytypes that currently have a letter campaign going. This will be the query you check to see if a property will have its SendLetters field flagged as Yes.
Give it a look and let me know.
Thank you. So looking at this and how it works it looks so simple and straight forward. It's great.
So just so I think I have it right I'll explain what's happening or I think is happening.
*[SendLetters] the new field in property will tell me which properties need letters, Ergo I'll need to update "LettersDue" to include [SendLetters]=Yes
*LetterDuration works out how long the letter cycle is in days. This is needed for the ActiveLandlordLetters Query later on.
*ActiveLandlordLetters query looks at all of the [PropDateEntered] and [lastletterduedays] and only includes results inbetween these values? thus returning only active landlords.
* So with [Sendletters] I will need to make a query when adding a property that checks if the Landlord the property is allocated to. if the Landlord has a property inside [ActiveLandlordLetters] then [Sendletters] = NO if it doesn't [Sendletters] = Yes
(obviously this means it will be set by department too.
Thank you very much I hope I understand this correctly.