Running queries on specific dates (2 Viewers)

[kacey8]

Hi guys, I hope I explain this correctly as it's a bit of an odd one

We have data in a table, addresses which need letters sent every at set points during the year, for example

Point 1 01/01/20XX
Point 2 01/04/20XX
Point 3 01/08/20XX

This repeats for 5 years (15 letters in total) then it stops completely marking the file as completed. Every time a letter is sent it marks a field ie Letter 1 as Yes so you know the letter has sent, also letter 2 will not send if letter 1 has not been sent.

My problem is new data is entered at new times. so for example the following might happen:

Address 1 entered on DB on 12/12 means letter 1 = 01/01
Address 2 entered on DB on 05/01 means letter 1 = 01/04

And the cycle would go on. but I have no idea to get the query to work on this basis. Can anyone help with this or maybe suggest a better way to do it?

 

[kacey8]

so as an example the following is based on 3 pieces of data added on different dates into the database. The sequence for when letter 1 is sent changes dependent on when the data is added to the DB.

 

[plog]

Can anyone help with this or maybe suggest a better way to do it?

You didn't really explain how you propose to do it. You explained the broadstrokes of what you wanted to accomplish, but how you intend to do that is lacking.

With that said, I believe you need table that will hold when every letter for every address needs to be sent:

LetterSchedule
letter_ID, autonumber, primary key
ID_Address, number, foreign key to Address table you mentioned
letter_Date, date/time, date that letter will be sent
letter_Sent, Yes/No, determines if letter was sent

That table should hold all the information you need to send and track letters. To populate it, you would run some sort of process whenever a new Address is added--it would add 15 records to the LetterSchedule table.

Is that what you have?

 

[kacey8]

Plog,

Thanks for your response, Currently I have nothing as I am unsure how best to go around this.

The method you'd describe I assume the dates for the letter would need to be added manually, this is the problem and what I am trying to avoid, but as the date each letter is sent is a variable I see no way the dates the letters need sent can be calculated automatically.

 

[plog]

as the date each letter is sent is a variable

Variable doesn't mean incalculable. Variable can mean dependent on other data. What is that other data? I am sure there's some logic in determining when letters get sent. You can then codify that logic and use it to populate the LetterSchedule table.

 

[kacey8]

Thanks Plog,

The fields in the tables which are related to the letters are

Address_1 to Address_5 (5 fields) doubt they'd be useful
Date_entered would be useful for calculating the date of the first letter
Letter_1_Sent Yes/No same for letters 2-15
Letter_1_Date Date field calculated/filled once letter 1 is marked as sent.

If required I can provide other fields in the DB but nothing that I see would be useful to the calculation for when to send letters.

This worked fine in these examples as all I had to do was use the following Where statement but that's not applicable here, not that I can see.

WHERE (((CANVAS_DATA.REMOVED)=False) AND ((CANVAS_DATA.LETTER_1_SENT)=True) AND ((CANVAS_DATA.LETTER_1_DATE)<=DateAdd("d",-14,Date())) AND ((CANVAS_DATA.LETTER_2_SENT)=False));

I've used this set up before with another database which has letters going out XX days after the data was entered on the database/the previous letter being sent.

This works in this instance but the issue is they want to send in one batch on specific dates rather than send XX days after they were added to the DB.


Hope this makes sense.

 

[plog]

Your table isn't structured properly. I see 2 major issues with what you posted:

1. When you start numerating field names (Letter1, Letter2, Letter3, etc), its time for a new table. You will need a seperate table for Addresses and for Letters.

2. Redundant fields. You don't need a Letter_1_Sent field if you have a Letter_1_Date that you can use to tell if the letter has been sent. If Letter_1_Date has a value then its been sent, if not, it hasn't. You don't need a seperate field for that.

At the end you also mention batching. This will require yet another table. Of course, that's a good thing. You will then be able to assign letters to batches and not have to individually track what letters were sent--you just check to see if their batch was.

I suggest you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), structure your tables properly, set up the relationmships tool in access, then post a screenshot of it so we can make sure you are on the right track with your tables.

 

[kacey8]

Cheers,

I'll crack on with that. I am working on other things as (as you can tell) writing DB's is not my primary job.

I'll respond once the database is split up, (it's an old DB) and I am aware the sections that need splitting since I've learnt more in recent years, just never changed this particular DB.

 

[kacey8]

Hi Plog,

I appreciate this topic has digressed massively as evidently my entire view of the DB was wrong before. Following from your advice we scrapped our previous DB and are going to begin from the start, merging two Databases into one to better control the data.

A much cleaner DB has been designed, basic but using a Landlord as a central file as we noticed previously multiple entries could have the same LL thus duplicating the same letter going out to the same person.

Landlord (only one entry per person)
Canvas_Property (multiple entries per Landlord, one per property he owns)
Block_Property (multiple entries per Landlord, one per property he owns)
Block_Letter (only one entry per Landlord*)
Canvas_Letter (only one entry per Landlord*)

*Need to investigate how to deal with end of cycle

 

[plog]

You broke both the rules I explicitly mentioned previously:

1. Numerated field names. When you feel the need to tack a number onto a field name (Letter1_, Letter2, etc.), you are doing it wrong. I actually gave you the structure of the table to accomodate this data. You didn't use it.

2. Redundant fields. While you did get rid of the '_Sent' fields you made this error in a new way. Both your Landord and _Propery tables have the same fields (e.g. Date_Entered, OTM, Duplicate, etc.). Why is that? You should only be storing values in one table if they are going to be the same values for both tables.

You also broke a 3rd rule

3. Tables with the same structure. Both your _Property tables have the exact same structure. There is no need for this. What you effectively have done is stored a value in the table name (Block or Canvas) that should instead be stored in a new field in the table. What that means is you should have one table for all this data called 'Property', it should have all the fields it has now, plus one new field which will hold the data to determine if that record contains block or Canvas data.

Give your structure another shot, include those 3 things and then post back.

 

[kacey8]

You broke both the rules I explicitly mentioned previously:

1. Numerated field names. When you feel the need to tack a number onto a field name (Letter1_, Letter2, etc.), you are doing it wrong. I actually gave you the structure of the table to accomodate this data. You didn't use it.

I must have missed this as all I see re letters/numerated field names in your reply is below.

1. When you start numerating field names (Letter1, Letter2, Letter3, etc), its time for a new table. You will need a seperate table for Addresses and for Letters.

Which is done, they're in a new table. separate tables for addresses/letters? Can you clarify? The only other way I can imagine making the table is to have two fields for the letters, ie
LetterNum
DateSent

Then rather having a separate field for each number recording the number in a field of it's own with the sent date in a separated field? or a seperate table for each individual letter?

2. Redundant fields. While you did get rid of the '_Sent' fields you made this error in a new way. Both your Landord and _Propery tables have the same fields (e.g. Date_Entered, OTM, Duplicate, etc.). Why is that? You should only be storing values in one table if they are going to be the same values for both tables.

Yes, see Date_Entered for the property and for the landlord will be two different things (easily re named if you believe the field names should be changed. A Landlord could be added to the DB on one date but could have properties added in the future. OTM shouldn't be in Property table, that's a Landlord specific field and my bad (removed), Duplicate needs to be in both as either a property can be a duplicate or the landlord be a duplicate (ie incorrect entry, duplicated entry), they're separate values so I'll rename accordingly.

You also broke a 3rd rule

3. Tables with the same structure. Both your _Property tables have the exact same structure. There is no need for this. What you effectively have done is stored a value in the table name (Block or Canvas) that should instead be stored in a new field in the table. What that means is you should have one table for all this data called 'Property', it should have all the fields it has now, plus one new field which will hold the data to determine if that record contains block or Canvas data.

Give your structure another shot, include those 3 things and then post back.

^ Agreed, I've forgotten a couple of fields as well so I've added them to the DB.

 

[kacey8]

Okay, So bar your suggestions re letters (as I am unsure entirely what you mean) I've taken your other points into suggestion.

*I've not touched either Block_Letter or Canvas_Letter
*Renamed duplicate fields in tables to give a clear understanding they're separate fields for separate values
*New table called Property, removed separate Block/Canvas
*New field in property to split block/canvas
*Removed Duplicate OTM Field
*Added forgotten field to Property (Branch_ID)


Now one thing I need to do which I'd like your opinion on, a Landlord should only receive Block letters if he has a property linked to him where the Canvas_Block field is set to "Block" and the same for Canvas.

Would the best way to work this out have a field in the Landlord table for example

"Has_Block" which if Canvas_Block = "Block" & Prop_Duplicate = "No" "Has_Block" automatically calculates to "Yes" and then letters are sent, if "Has_Block" = No, no letters are sent?

 

[kacey8]

Just noticed you were referring back to your original post.

LetterSchedule
letter_ID, autonumber, primary key
ID_Address, number, foreign key to Address table you mentioned
letter_Date, date/time, date that letter will be sent
letter_Sent, Yes/No, determines if letter was sent

The database has somewhat changed in structure since my original post as now letters will be based on Landlords rather than the property address to avoid duplicates.

Going by your structure I can do the following.

Letter_ID, autonumber, primary key (I have that, it's just labeled ID, easily changed)
ID_Address, I have but changed to Landlord_ID due to change above
Letter_Date I can have one field for letter date yes, but without an additional field in the table this doesn't tell me which letter in the series it was. ie letter 1 or 5 or 15.
Letter_Sent Removed per your note re unnecessary fields.

The only structure I can imagine which I mentioned above would be the following


ID, autonumber, primary Key,
Landlord_ID, number, foreign key,
Letter_Date, Date/time, stores date of letter sent
Letter_Number, Number field, stores which letter in the series was sent

 

[Minty]

Just to jump in on Plog's advice - The letter number is not something I would store, you can easily count how many letters you have sent and they would be ordered by the date sent - so it's not achieving anything of any value.

Also if for some reason a letter was assigned to the wrong Landlord, then changed over to the right one your letter numbering sequence would be completely messed up. This is why you should only ever calculate this type of "Display" field.

Edit - and you only need to have one letter table with a letter type field e.g. Canvas or Block, or whatever the third letter you haven't thought of yet is called

 

[kacey8]

Just to jump in on Plog's advice - The letter number is not something I would store, you can easily count how many letters you have sent and they would be ordered by the date sent - so it's not achieving anything of any value.

Also if for some reason a letter was assigned to the wrong Landlord, then changed over to the right one your letter numbering sequence would be completely messed up. This is why you should only ever calculate this type of "Display" field.

The reason for this being done in this way was to keep a record of the following

*How many letter 1's, 2's 3's etc have been sent during a given date period.
*Manually adjust (for example on occasion we need to make a file go straight to letter 5 which was achievable by marking letter 1-4 as sent.
*Letters are sent a previous stages, so for example letter 2 would only show up to be sent XX days after letter 1 had been sent. I achieved this with a query using code like

((CANVAS_DATA.LETTER_1_DATE)<=DateAdd("d",-14,Date()))

*looking at a record and knowing which letters have been sent
*I also created the merge files for the letters on the DB which opened in word merging the data from the DB through. This has to be done for each letter, ie Letter1 and Letter2 have different templates in word.

Now if this can be done easier without the need to number the fields I am all ears, maybe I am not giving access enough credit.

Edit - and you only need to have one letter table with a letter type field e.g. Canvas or Block, or whatever the third letter you haven't thought of yet is called

That fine, happy to centralize the table for Block letter & Canvas letter but as you said I'd need a type field to specify which is which as they're tracked differently.

 

[Minty]

If the type of letter you are sending out varies I would store that as a letter function type. And then store the dept. as another field. You know your business model.

So you would have something like

LetterID, autonumber, primary Key,
Landlord_ID, number, foreign key,
Letter_Date, Date/time, stores date of letter sent
LetterType, Probably an number ID foreign Key to a LetterTypeTable? If you went this route you could possibly store the LetterDept (Below) in the same table and remove it from this table.
LetterDept, "Canvas", "Block", "Other"

As you can see there are often more than one way to achieve the data layout you need.
And Access can certainly display each record of the letters sent out per landlord. This would probably be a sub form in the landlord records form.

Get your data layout right first. Think about what you need in the way of reporting and end user interaction. Once you are know the data you need for the reports / views, then you decide how to store it, and finally build the forms to manipulate and enter that data.

If you can't design it on paper then it can't be done in access.

 

[kacey8]

If the type of letter you are sending out varies I would store that as a letter function type. And then store the dept. as another field. You know your business model.

So you would have something like

LetterID, autonumber, primary Key,
Landlord_ID, number, foreign key,
Letter_Date, Date/time, stores date of letter sent
LetterType, Probably an number ID foreign Key to a LetterTypeTable? If you went this route you could possibly store the LetterDept (Below) in the same table and remove it from this table.
LetterDept, "Canvas", "Block", "Other"

As you can see there are often more than one way to achieve the data layout you need.
And Access can certainly display each record of the letters sent out per landlord. This would probably be a sub form in the landlord records form.

Get your data layout right first. Think about what you need in the way of reporting and end user interaction. Once you are know the data you need for the reports / views, then you decide how to store it, and finally build the forms to manipulate and enter that data.

If you can't design it on paper then it can't be done in access.

That looks like it would word fine, I'd need to store a letter ID/Type the referencing for the letter type runs as follows

B1 - B15
C1 - C30

Clearly obvious how the letter type layout goes. I think the layout you've suggested above will work fine so I'll implement this and show a new design layout. Also adding the letter type as a field will then allow me to add new letters as I need to. ie C31 on wards without adding new fields.

Thanks.

 

[Minty]

If you added a field to your letter type table that was the word template name you could automate the choosing of that based on the letter type.

 

[kacey8]

Think we are getting there?

Going back to another question, I need to be able to control whether a Landlord receives a letter for canvas, or block, or both.

Would the best plan for this have two fields "Has_Block" and "Has_Canvas" The fields are then set to "yes" based on whether the properties linked to him have the required "canvas_block" field chosen

 

[Minty]

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.