Running queries on specific dates (1 Viewer)

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
That look much better. I'm sure others can add comments.

The letter types one is a bit of sticky wicket, ideally the honest answer is you should create a junction table with the LandlordID and the LetterDepts they can receive. This way if that third/fourth/seventh LetterDept arrives you don't need to rebuild all your reports and forms.

It sounds messy but handles the expansion possibilities - Whilst your idea of the check box is visually appealing and sounds easy to code, as soon as LetterDept 3 arrives you need to re-code.

I have been told under no circumstances would there be another Dept as it has to be one of the two listed. However I do see your point.

With regards to a couple of fields I had a question.
Canvas_Block is either a drop down box either "Block" or "Canvas"
Letter_Dept is the same a drop down box either "Block" or "Canvas"

would it be best to have a department table created and have a relationship to them. (ie below)

 

Minty

AWF VIP
Local time
Today, 10:32
Joined
Jul 26, 2013
Messages
10,353
I have been told under no circumstances would there be another Dept as it has to be one of the two listed. However I do see your point.

I absolutely positively guarantee whoever said this, will at some point be the very same person that says "Could we add another type of x y z to the system?"

You shouldn't really ever have circular relationships. It normally indicates that the setup isn't right as the data should be pulled in from another already linked table.

Also I would rename your ID fields to LandlordID and PropertyID, and your third table to DeptID. That way you won't get confused about what ID is doing what.
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
I absolutely positively guarantee whoever said this, will at some point be the very same person that says "Could we add another type of x y z to the system?"
Noted, it's the same with my day job so I belief your right, your methods will be correct for a reason and just because I don't know how to do it doesn't mean I shouldn't learn


You shouldn't really ever have circular relationships. It normally indicates that the setup isn't right as the data should be pulled in from another already linked table.
Brilliant, Makes sense, What's the best way to set up the Department. The only other way is to not have a table for Deptartment and have the two fields it links to "Letter_Dept" and "Canvas_Block" as lists which are filled when the forms are filled out. Let me know your thoughts on this.

Also I would rename your ID fields to LandlordID and PropertyID, and your third table to DeptID. That way you won't get confused about what ID is doing what.

Already done, just not displayed. They follow the same format as Letter_ID.
 

Grumm

Registered User.
Local time
Today, 11:32
Joined
Oct 9, 2015
Messages
395
Or you can make an enum for field Letter_Dept and Canvas_Block. That way you can expand the enum in the futur if needed.
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
Looks a lot better from when I left. As to the circular relationship, from what you have set up, it seems that Letters would be better attached to Property than Landlord. Do that and you eliminate your circular reference.

From what I am seeing, Letter_Type and Department are unnecessary tables. The Canvas_Block/Department data would exist only in the Property table (that eliminates your Department table). The Letter_Ref value then goes into Letters (eliminating Letter_Type table).

Attached is what I think the new relationship should look like:
 

Attachments

  • test.png
    test.png
    12.1 KB · Views: 66

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
Looks a lot better from when I left. As to the circular relationship, from what you have set up, it seems that Letters would be better attached to Property than Landlord. Do that and you eliminate your circular reference.

From what I am seeing, Letter_Type and Department are unnecessary tables. The Canvas_Block/Department data would exist only in the Property table (that eliminates your Department table). The Letter_Ref value then goes into Letters (eliminating Letter_Type table).

Attached is what I think the new relationship should look like:

Thanks Plog, appreciate it. The only thing is letters are sent/created based on the Landlord, Not on the property as if a Landlord has 3x properties it would create 3x letters (potentially at different times).

If this is easily worked around I can do as suggested.
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
The only thing is letters are sent/created based on the Landlord, Not on the property as if a Landlord has 3x properties it would create 3x letters (potentially at different times).

But Canvas_Block plays a part in the letters and that is at the Property level. Suppose a Landlord has a Canvas and a Block property. How does that effect their letter(s)?

If Letters are related to a landlord then you are storing Canvas_Block in the wrong table--it should be in Landlord.
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
But Canvas_Block plays a part in the letters and that is at the Property level. Suppose a Landlord has a Canvas and a Block property. How does that effect their letter(s)?

If Letters are related to a landlord then you are storing Canvas_Block in the wrong table--it should be in Landlord.

Yeah it's a bit of a mismatch, I'll explain the situation but looks like you're right.

Every property has a Department (Canvas or Block) the Landlord may have 3x Canvas and 4 Block properties. However the system should only send out letters once, to avoid duplicates. I assume there would be a way to control this?

You're right though it should sit against the property. that makes more sense.
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
Going by your suggestion I followed your advice,



*Renamed "Block_Canvas" to "Department" to make it clearer. this field will tell the letters what letters to send
*Removed unneeded tables

I think we're there on the layout part and I am happy with how I believe it will work.


Edit* So I assume the best way for this to work for sending letters is as follows

letter1 is merged/sent, an entry is made in the letters table stating letter1 has been sent, ie as follows

LETTER_ID == 1
PROPERTY_ID ==1
LETTER_DATE ==04/01/2016
LETTER_TYPE ==Block
LETTER_REF == B001

Then when letter 2 is being run it will use a query that checks the previous properties for letters sent? What's the best way to do this?

Search for the PROPERTY_ID count how many letters have been sent? if 1 letter has been sent it needs letter2, to see if letter2 is due it uses the letter_date to find out?

Or would it be easier to go by the letter_ref? i.e if the recent letter_ref = B001 then send B002 XX days after?
 
Last edited:

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
First, I really question that Letter_Ref should be a field in your table. Whats its purpose? Even if it is necessary, I don't think the 'B' portion is. That designates that it is a Block letter correct? If so, you don't need to store that because you know its a Block letter by Letter_Type. Does the numeric portion of that correspond to other data you are already storing as well?

Second, explain to me what you think 'Letters' contain. Is it a schedule for sending out letters, or is it a tracker of letters sent? Do you hope to do both of those things or just one of those things (plan and track)? As it is, you're data is just allowing one of those things and your explanations have confused me to what that is. Is this database for scheduling or tracking letters?
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
First, I really question that Letter_Ref should be a field in your table. Whats its purpose? Even if it is necessary, I don't think the 'B' portion is. That designates that it is a Block letter correct? If so, you don't need to store that because you know its a Block letter by Letter_Type. Does the numeric portion of that correspond to other data you are already storing as well?

Second, explain to me what you think 'Letters' contain. Is it a schedule for sending out letters, or is it a tracker of letters sent? Do you hope to do both of those things or just one of those things (plan and track)? As it is, you're data is just allowing one of those things and your explanations have confused me to what that is. Is this database for scheduling or tracking letters?

The Ref B0001 gets printed on the labels for the letter for our internal billing (our department pick up the codes to allocate the billing of postage)

I apologies if I didn't explain correctly. I'll explain from start to finish in clear terms the purpose of the DB. (some may be irrelevant but maybe not)


The outlying objective of the database is to contain a record of all of the landlords we're canvassing and their respective properties. We enter the landlord onto the DB with their property details. (can be multiple properties for same LL)

The user will run a report daily on the database which will tell us which letters need to be sent out and to which landlords.

The database produces a report/list of LL's (Landlords) who are "due" LetterX (Xbeing a number in a series of letters) The database will then allow a word document to merge the contents stored into the word template creating a letter (or label in most cases) the report will be for each letter individually, ie Letter1 Report, Letter2 Report. This is because different users are responsible for different letters. This also means the letters printed 1 at a time, so the template for Letter1 is merged, printed, updated then letter2 is done separately. (not in one go)

Once the letter merge is sent it needs to document that the letter has been sent for auditing purposes, documenting the letter has been sent will also be used to confirm the next letter is due (based on when the previous is sent).

An example letter schedule is as follows

Letter1 is due day 0
Letter2 is due 14 days after letter1
Letter3 is due 14 days after letter2
Letter 4 is due 21 days after letter3

The cycle continues for either 30 letters (Canvas) or 15 letters (Block)

Both Canvas and block use different frequencies for the period between letter sends.


Does this clear up the purpose of the DB? apologies if not clear originally?
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
The Ref B0001 gets printed on the labels for the letter for our internal billing

That didn't answer my question, in fact it raised more. If this is for billing, then why does each letter get its own reference? The field instead should instead hold a charge code/GL/cost center/etc to designate where it should be billed.

Then you never addressed specifically my second set of questions about what you think Letters holds. Sounds like you want to use it for both scheduling and tracking letters. It is currently not enabled for that, so you will need to accomodate that.

2 new questions:

1. How do you expect users to mark letters as sent? Do you expect them to go into each landlord, drill down to their letters, find the one they sent and mark a check box/enter a date? Then repeat this process for every letter sent? Or, do you want to Batch your letters together, then have the user mark the Batch as sent?

2. How specific do you need get when sending letters? Is it simply Yes/No or do you need to record a date/time the letter was sent? Now, if further letters will be sent out based on when prior letters were sent, the answer is you need to record the date/time.
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
ETD - Seen your reply, more to follow
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
That didn't answer my question, in fact it raised more. If this is for billing, then why does each letter get its own reference? The field instead should instead hold a charge code/GL/cost center/etc to designate where it should be billed.

I'm not sure how I can explain, the B001 - B015 gets printed on each label, When it's run through our machines at work to frank the label it picks this up and gives an itemized count of how many & cost. It also means I can create a report to go "how many entries on the DB between XX & XX had REF B001"

Now tbh I can scrap this code off and hard code them on the Word Templates (seeing as their are 15 templates anyway for each letter. but how else would I report how many entries had been created with the B001 code

Then you never addressed specifically my second set of questions about what you think Letters holds. Sounds like you want to use it for both scheduling and tracking letters. It is currently not enabled for that, so you will need to accomodate that.

Yes, I thought I said that, I need to schedule when letters are sent. and also track when they are sent, date stamp them.

2 new questions:

1. How do you expect users to mark letters as sent? Do you expect them to go into each landlord, drill down to their letters, find the one they sent and mark a check box/enter a date? Then repeat this process for every letter sent? Or, do you want to Batch your letters together, then have the user mark the Batch as sent?

They should be done in batches of each letter, ie Letter1. Letter2 etc so on. not via each individual record one at a time.

On my old DB, it may have been wrong but I had 3 steps.

[*]Report ran and showed which Landlords were due LetterX
[*]Merge file was run, created the letters for the Landlords which showed in the report then the users printed the letters
[*]Update query would be run which would update the Landlords which showed in the report, that the letter had been sent.

2. How specific do you need get when sending letters? Is it simply Yes/No or do you need to record a date/time the letter was sent? Now, if further letters will be sent out based on when prior letters were sent, the answer is you need to record the date/time.

The latter, As Letter 2 is scheduled based on when Letter 1 is sent the date letter one is sent needs to be recorded.

This is why I neeeded/have a Letter_date field, (although should probably be called Letter_date_sent to make it clearer.
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
I believe what I will need is two tables.


One as a schedule stating which letter is due and when it is due
One as a track of the previously sent letters, what has been sent and when

Am I correct?


Lettersent.tbl
Letter_ID, primary key, id of the letter
Property_ID, foreign key, linking the letter that's been sent to the property/ll it was sent to
Letter_Date_Sent, date the letter was sent
Letter_Type, don't think this is needed as Letter_type is either canvas/block but the type is on the property table as "department"
Letter_Ref, The charge code/reference of the letter that's been sent


Schedule.tbl

This I am not sure how to best structure this, This is where I am now stumped.



Ps. Appreciate your patience.
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
Concerning the reference number--my point is I don't think it needs to be stored. You can still print it on wherever you need to, you just do not need a field for it because it is comprised of data you do store in other fields. If you stored someone's birthdate and wanted to display their age, you wouldn't need an age field, you could deduce their age via the birthdate value. Same with your reference number--it can be deduced from data already in your tables.

How will the due date for letter 1 be determined? Then after that, how logically do you determine when letter 2...X will be sent?
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
Concerning the reference number--my point is I don't think it needs to be stored. You can still print it on wherever you need to, you just do not need a field for it because it is comprised of data you do store in other fields. If you stored someone's birthdate and wanted to display their age, you wouldn't need an age field, you could deduce their age via the birthdate value. Same with your reference number--it can be deduced from data already in your tables.

I understand that, How would this be deduced from the current fields, in your opinion?

How will the due date for letter 1 be determined? Then after that, how logically do you determine when letter 2...X will be sent?

*Letter 1 is due on the day the property is entered to the database [prop_date_entered]

*Letter 2 is due X days after letter1 is sent
*Letter 3 is due X days after Letter 2 is sent

And so on, there are also filters, ie Letters do not get sent is field = true for either LL_Duplicate or Prop_Duplicate

Does this make sense?
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
I don't know how the reference number would be deduced, I was trying to get that out of you and then you veered off course. The 'B' portion doesn't because its in the Canvas_Block field, correct? How do you determine the numeric part?

Can you be more specific than X days after letter 1? There has to be some sort of logic right? If not, then does the user generating letter 1 decide that and manually input it?
 

kacey8

Registered User.
Local time
Today, 10:32
Joined
Jun 12, 2014
Messages
180
I don't know how the reference number would be deduced, I was trying to get that out of you and then you veered off course. The 'B' portion doesn't because its in the Canvas_Block field, correct? How do you determine the numeric part?v

The Numeric is based on the number of letter in the series, so the Letter1 for Block has B001 Letter1 for Canvas has C001, Letter10 for Block has B010 and for Canvas has C010

Can you be more specific than X days after letter 1? There has to be some sort of logic right? If not, then does the user generating letter 1 decide that and manually input it?

The gaps between letter sends can be seen below.

This is the structure for Letters for (Canvas)


The numbers is how many days after the previous letter was sent that the next letter is due. Does this clarify for you?

Block is sent on a different schedule which is much simply with Block the logic is easy, the first letter is sent on day 0 with each following letter due 90 days after the previous.
 

plog

Banishment Pending
Local time
Today, 05:32
Joined
May 11, 2011
Messages
11,611
The Numeric is based on the number of letter in the series

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.

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.

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?
 

Users who are viewing this thread

Top Bottom