Running queries on specific dates (2 Viewers)

[kacey8]

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?

 

[plog]

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

Exactly. You can even format [Letter_Num] to have preceding 0s.


And yes, 4/5 landlords and a dozen properties would be great. No need for forms yet, just tables.

 

[kacey8]

Brilliant, Final Schema if you're happy I'll throw some Data in and upload onto here.

These have all the fields I can think of but if I have missed one you've mentioned apologies. I've renamed some to make them a little clearer to what they mean also on the DB I've filled in the "Description box" of every field to show what it will store.


Quick question, I store the Department in property (ie it's Block or Canvas) but also have Letter_Dept (which tells me if the letter is Block or Canvas)

Am i not doubling up, as the property file the letter is linked to would tell me if it's block or canvas? would it not. Thus is a redundant field if I understand your teachings correctly.

I also got rid of the underscores in the field titles, I remembered that it's a pig to type on queries and gets in the way.

 

[plog]

Looks good and you are correct. LetterDept in Letters is redundant and not needed.

 

[kacey8]

Okay, not complete data, I'll throw up just incase you want to look, (I only got 6 properties in) if not I'll throw 6 more in and upload first thing in the morning.

Letters is empty, didn't know if you wanted test data here or not as the data should be auto generated normally.

Let me know if you do.

 

[plog]

That's just what I wanted. Attached you will find your database with a few changes and additions.

1. Made LetterNum a number. Work with the correct datatypes--this field is numeric, store it that way.

2. Added LetterSchedule table. This will be the brains behind when each subsequent letter gets sent out.

3. Created LettersDue query. Used subquery to determine last letter sent, then used that and LetterSchedule to create the query you will need to send the next letter to every property. In it, I created the LetterRef field you said you put on every letter.

4. Created mark_LettersSent query. This will update Letters with the data from LettersDue essentially marking them as sent.

Check out the data in the Letters table and veirfy its right--I created fake data based on your schedule for each subsequent letter and assumed they all were sent exactly on time.

Give it a look and let me know if you have any questions

 

[kacey8]

Hi Plog,

Many thanks and looks great Few things I noticed I'll note below plus respond to your points

That's just what I wanted. Attached you will find your database with a few changes and additions.

1. Made LetterNum a number. Work with the correct datatypes--this field is numeric, store it that way.

Thanks, oversight on my part.

2. Added LetterSchedule table. This will be the brains behind when each subsequent letter gets sent out.

Yup, looks good and I understand the structure, seems very simple to me.

3. Created LettersDue query. Used subquery to determine last letter sent, then used that and LetterSchedule to create the query you will need to send the next letter to every property. In it, I created the LetterRef field you said you put on every letter.

Firstly, I noticed I made a massive faux pas with naming my Landlord Valuation field (using a space), I'll correct it and adjust what you've made.

LettersDue query - Looks great, I noticed it returns all the letters due (ie the next letter in the series regardless of due date, This query will be used on a daily basis and I would need it to only return results which are due on the day the query is being run (thus stopping letters being printed too early. Any suggestions on how best to work this out? I assume the query can be added simply to only return results Less than/Equal to today's date?

LetterRef It looks great, I want to run reports on this in the future for historical letters sent, ie look back to how many of each type was sent. I assume I can use a similar calculation for this. Looks interesting.

LettersDue_sub1 - Looks amazing, nothing would need change on here and gives me a great starting point.

4. Created mark_LettersSent query. This will update Letters with the data from LettersDue essentially marking them as sent.

Check out the data in the Letters table and veirfy its right--I created fake data based on your schedule for each subsequent letter and assumed they all were sent exactly on time.

Give it a look and let me know if you have any questions

Mark_LettersSent This is amazing and similar to what I used before, (however better)


I really appreciate your help, I've dissected the code you've created as I need to learn and understand it but so far it all looks straight forward to me.

 

[kacey8]

Okay so I answered one part,

The letter due I added a Where clause to the query

WHERE (((DateAdd("d",[LastSentDate],[NextLetterDays]))<=Date()));

Tested it only shows letters on/before todays date, thus not allowing letters to be run for the future.

 

[plog]

Tested it only shows letters on/before todays date, thus not allowing letters to be run for the future.

Most likely you will want something like this:

<=(Date()+7)

If ran today that would show all letters due before January 12th. Change the number to whatever fits your needs.

As to running a report of how many of each type of letter was run, that would be a simple aggregate query on the Letters table:

SELECT LetterNum, COUNT(LetterNum) AS Total FROM Letters GROUP BY LetterNum;

 

[kacey8]

Most likely you will want something like this:

<=(Date()+7)

If ran today that would show all letters due before January 12th. Change the number to whatever fits your needs.

Thanks, I don't think I need +7 as I only want to run for letters due on the day, the Due date represents when they're due to be processed/printed not due to arrive. So using <=Date() is perfect as I only want results with todays results or less. But thanks.

As to running a report of how many of each type of letter was run, that would be a simple aggregate query on the Letters table:

SELECT LetterNum, COUNT(LetterNum) AS Total FROM Letters GROUP BY LetterNum;

This is fine, I have comething similar (works well) but I need it split by Department, so I need a count for LetterNum split by Department.

I have an idea I'll try.

 

[plog]

Just noticed my mark_LettersSent query is incorrect a bit. It should be this:

INSERT INTO Letters ( PropertyID, LetterNum, LetterSentDate )
SELECT LettersDue.PropertyID, LettersDue.NextLetterNum, Date() AS Sent
FROM LettersDue
WHERE (((LettersDue.NextLetterDate)<=Date()));

In the prior version I was marking the letters as sent on the day they were due. The above query will mark them as sent on todays date (whatever that may be when run).

 

[kacey8]

Just noticed my mark_LettersSent query is incorrect a bit. It should be this:

INSERT INTO Letters ( PropertyID, LetterNum, LetterSentDate )
SELECT LettersDue.PropertyID, LettersDue.NextLetterNum, Date() AS Sent
FROM LettersDue
WHERE (((LettersDue.NextLetterDate)<=Date()));

In the prior version I was marking the letters as sent on the day they were due. The above query will mark them as sent on todays date (whatever that may be when run).

Thanks, I've done that.

I've noticed that the query LetterDue requires a Letter to have been sent for it to show a result. However when a property is first added it will not have had a letter sent,

Dealing with the "initial" letter, would it be better to use a different query i.e "InitialLetter" to run first, then use LetterDue for all subsequent letters or can the first letter for a property be incorporated into the LetterDue query?


I also have two questions going off on a tangent even more from my original questions here.

1 - We have multiple users using this Database and we'd like to log activity by the user, How much activity would depend on the complexity of adding it. I've never done anything like this before. the only way I logged before was each person .accdr runtime file had individual code that added their own initials when they added entries to the DB.

2 - If I wanted to calculate a field to work out the difference between when the letter was due and when it was sent, What's the best way to work this out?

I assume I could append a field in

 

[plog]

Initial letter-- this is up to you. The datasource of the query will have to be based off the Property table. However, once you have it you could UNION that query to LettersDue and create 1 query that holds all that data.

1--The complexity of adding is dependent on how anal you want to be. Logging who opens the database at what time would be easy, logging every change would be painful. Also, I really question this. Management always wants to manage, but usually things like this never really serve any purpose. Do you really care that Tim added the wrong property to a landlord? Even if it was on purpose, Tim probably has other marks on his record that will be grounds for termination.

2--In a query using the Datediff function

 

[kacey8]

Initial letter-- this is up to you. The datasource of the query will have to be based off the Property table. However, once you have it you could UNION that query to LettersDue and create 1 query that holds all that data.

I figured it would be based of the property table as it'll be reliant on the "PropDateEntered" field. I think I will keep as two queries, 1st letters which will then add the initial entry into the Letters table and then continue to use LettersDue as it is.

I'll try a few things, will prob post the query to let me know if I am doing it in the best way.

1--The complexity of adding is dependent on how anal you want to be. Logging who opens the database at what time would be easy, logging every change would be painful. Also, I really question this. Management always wants to manage, but usually things like this never really serve any purpose. Do you really care that Tim added the wrong property to a landlord? Even if it was on purpose, Tim probably has other marks on his record that will be grounds for termination.

2--In a query using the Datediff function

1-- I personally do not care less about Tim, The main thing Management want to log is "who added the Landlord" and "Who added the Property". I think this would be the minimum I would be able to get away with.

2-- Once again I am stuck in my way of thinking of adding a field to store the "DateDiff" where as the data is there it's just a calculation. Perfect, I can create a query easily and use it to display on a report.

MORE QUESTIONS. Apologies, I realise we're going off on a tangent but your advice has been amazing so far.

Now because LettersDue is based off the property file. How would I go about making sure Landlords would only receive one letter, We don't want 5 letters for the same landlord printing or worse case going out days apart from each other. Now the they're not duplicates as of course as the data in other fields differ so I can't treat them as such and I don't want to just hide duplicates, I only want to return the first entry for the landlord.

For your reference I've adjusted LetterDue Query so it doesn't include records which are Duplicates/OTM (which means we shouldn't send to them) and also I forgot a field needed in the ref Property.branchID so I added that in.

SELECT LettersDue_sub1.PropertyID, [LastSentNumber]+1 AS NextLetterNum, DateAdd("d",[LastSentDate],[NextLetterDays]) AS NextLetterDate, [Property].[BranchID] & "-" & Mid([Property].[Department],1,1) & "-" & Format([LastSentNumber]+1,"0000") AS LetterRef, Landlord.LandlordSaluation, Landlord.LLAddress1, Landlord.LLAddress2, Landlord.LLAddress3, Landlord.LLAddress4, Landlord.LLAddress5, Landlord.LLDuplicate, Landlord.OTM, Property.PropDuplicate
FROM ((LettersDue_sub1 INNER JOIN LetterSchedule ON (LettersDue_sub1.LastSentNumber = LetterSchedule.LetterNum) AND (LettersDue_sub1.Department = LetterSchedule.Department)) INNER JOIN Property ON LettersDue_sub1.PropertyID = Property.PropertyID) INNER JOIN Landlord ON Property.LandlordID = Landlord.LandlordID
WHERE (((Landlord.LLDuplicate)=False) AND ((Landlord.OTM)=False) AND ((Property.PropDuplicate)=False));

 

[plog]

Now because LettersDue is based off the property file. How would I go about making sure Landlords would only receive one letter, We don't want 5 letters for the same landlord printing or worse case going out days apart from each other. Now the they're not duplicates as of course as the data in other fields differ so I can't treat them as such and I don't want to just hide duplicates, I only want to return the first entry for the landlord.

Define 'first entry for the landlord'. Also, would you still mark the letters you don't send as sent?

 

[kacey8]

Define 'first entry for the landlord'. Also, would you still mark the letters you don't send as sent?

I think first entry for the LL is the wrong term to use. sorry.

So from the example I have here, PropertyID 4 was the first property on the DB and had a letter run begin, Half way through PropertyID 5 got added (they have the same Landlord)

Property 5 NEVER gets a letter sent out as letters are already being sent out to the Landlord due to Property 4, BUT we need it in the database as a record to use later on.

However, once property 4's letters have ended we see in our example Property 10 is added (same Landlord)

Property 10 letters get sent out as no letters were being sent to the Landlord at the time he was added to the database.

Does this make sense?

Also, Blocks/Canvas (departments) would be treated separately and not affect each other.

I don't think the letters need to be marked as "sent" but maybe the property can be tagged with a status saying "do not send due to previous send" or something worded better.

 

[kacey8]

Just a thought, could it work on the logic that

When a Property is added to a Landlord, if Landlord is inbetween receiving Letter1 to Letter26 the file is marked "DoNotMail" so it doesn't appear in the mailings.

 

[plog]

I have an idea of what you are getting at with your example, but it doesn't make complete sense. Specifically how you say property 5 will never have a letter sent out. What happens after property4 complets all its letters? Wouldn't property5 Letter 16 go out? Then shouldn't it continue sending letters based on property5 until they complete? Then it should pick up and start sending property10 letters, starting at Letter10?

Many more questions, but lets start there.

 

[kacey8]

I have an idea of what you are getting at with your example, but it doesn't make complete sense. Specifically how you say property 5 will never have a letter sent out. What happens after property4 complets all its letters? Wouldn't property5 Letter 16 go out? Then shouldn't it continue sending letters based on property5 until they complete? Then it should pick up and start sending property10 letters, starting at Letter10?

Many more questions, but lets start there.

That is where my logic for it was originally going until I got overridden by the powers that be.

Powers that be said that:

If a Landlord has letters going out when a property is added, that property NEVER gets letters

If a Landlord has no letters going out (even if he had letters go out historically) when a property is added, that property gets letters.

 

[plog]

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?