Search results

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.

Hi Plog, Many thanks and looks great :) Few things I noticed I'll note below plus respond to your points Thanks, oversight on my part. Yup, looks good and I understand the structure, seems very simple to me. Firstly, I noticed I made a massive faux pas with naming my Landlord Valuation...

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...

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...

^ 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...

I understand that, How would this be deduced from the current fields, in your opinion? *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...

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...

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...

ETD - Seen your reply, more to follow

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...

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...

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...

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.

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...

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...

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...

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...

Just noticed you were referring back to your original post. 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...