Running queries on specific dates

[kacey8]

That makes sense. Now tell me about this situation:

Letter26 for Property1 of Landlord1 is due out on 1/5/2017
Property2 for Landlord1 gets added 1/6/2017
Letter26 for Property1 of Landlord1 gets sent on 1/7/2017

Does Property2 for Landlord1 get letters sent?

Interesting....

Yes it would. We would need to base it on the DUE date of the letter 26 not the SEND date of the letter.

The only time letters are not usually sent on the Due date is usually over weekend/public holidays.

What if I built a 14 day buffer in to defer/delay letter crossover?

 

[plog]

Yes, that was my question essentially. Just getting down to brass tacks. Also, do Canvas and Block letters work seperately? That is if a landlord adds a Canvas property on 1/1/2017 and a Block property on 1/11/2017 does the landlord get 2 letters or 1 letter on 1/11/2017?

 

[kacey8]

Yes, that was my question essentially. Just getting down to brass tacks. Also, do Canvas and Block letters work seperately? That is if a landlord adds a Canvas property on 1/1/2017 and a Block property on 1/11/2017 does the landlord get 2 letters or 1 letter on 1/11/2017?

Apologies I thought I answered that already, Yes

Canvas & Block are two independent operations and should not interfere with each other.

 

[plog]

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.

 

[plog]

The database in my previous post has no changes you've made. So if you want to incorporate my changes (or yours) into a good up to date database, do that and then post it back here so we can reference the same version.

 

[kacey8]

The database in my previous post has no changes you've made. So if you want to incorporate my changes (or yours) into a good up to date database, do that and then post it back here so we can reference the same version.

Yeah I figured that, I'll crack on this morning and import them into the DB I have sitting here, (my working project)

 

[kacey8]

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.

 

[plog]

You are mostly correct, just one clarification:

You do not need to modify LettersDue, because LettersDue_sub1 already uses the SendLetters=Yes criteria.


Other than that, what you wrote was correct.

 

[kacey8]

Hi Plog,

Thanks and sorry for the delay in the response. Been away for a few days.

Overall this looks amazing and the structure or set up seems to do everything I need it to.

If I have more questions I may post back,