Am I doing this in the most efficient way

[kacey8]

Hi Guys, Me again....

So I have a DB I am working on which holds canvassing data.

Now ever so often we sent a letter out to the people on the DB and it has a time period for example

Letter 1 - Same Day
Letter 2 - 7 Days after entry
Letter 3 - 14 Days after entry

This goes on for 30 letters.

The process for each letter is as follows and has three different buttons, I know I can merge them into 1 button but they're split for a reason (I have used Letter 3 as an example)

STEP 1 - Letter 3 Report
This report is produced from a query (QRY_Letter3_Search), the query searches for all entries into the database which were entered 14 days from todays date or older and has Letter 1 & Letter 2 Marked as true(sent) with Letter 3 marked as false (not sent)

STEP 2 - Letter 3 Labels
Step two runs and loads the same query (QRY_Letter3_Search) used in the report. This opens in a Word Mail Merge and makes the address labels for the letters which need to be sent out. This is cross checked with Letter 3 Report to confirm the correct number of labels are printed

STEP 3 - Letter 3 Update
This step is the final step and runs an update query (QRY_Letter3_Update) This query updates Letter 3 and marks it as being sent. This is kept seperate as it has various different warnings, warning the user to make sure their labels are correct.

All in all this is a simple process, but for the DB I would have to recreate it over 30 times.

I am wondering if this is the best way to do it, without overcomplecating things?

 

[CJ_London]

assuming your letters sent field is normalised - i.e is stored in a table with something like

recipientID
lettertype (1,2,3 etc)
datesent

for fields this is fairly straight forward.

You'll need to put on your form a control to capture which letter type to send, then your criteria can be based on

'where last sent lettertype= form lettertype-1'.

 

[kacey8]

Each letter have their own Yes/No box. rather than one field to control ALL letters.

 

[kacey8]

I'm assuming you don't want the programme to run and print out 30 letters & labels in one go as you will need the operator to check paper availability, the labels are ok, and other stuff.

What you are saying is you don't want to write the code out 30 times for each letter?

If thats the case then I would suggest you start with a table containing a list of the letters.

Now have a button that starts the letter printing operation. The code would select the first letter from the table, feed it in to your "Generic" code, and print the letter. Other fields in the table would hold parameters for the queries, ie "14" (for 14 days ago) a flag (or probably several) so that your code would move through the process logically.

It's not so much of writing out the code, tbh I can copy the previous Queries and just change it to the next letter.

Each letter being sent has its own Yes/No box, so for example I have this

Date Entered
[loads of random address fields which aren't relivent]
Letter 1 = Yes/No
Letter 2 = Yes/No

And so on, unfortunately this is the only way to do it (seperate letter boxes) as the data is being imported from Excel, not fresh data.

The program only prints labels, this loads them in word and shows the labels, the user then goes to file print in word (I am happy with this)

No letters are current printed from the program only labels, the letters are standard non personalised marketing pieces

 

[CJ_London]

Each letter have their own Yes/No box. rather than one field to control ALL letters.

To do what you want to do you need to change your db design as both I and Uncle Gizmo are suggesting.

Your letters table would look something like

RecipientID LetterNo DateSent
1 1 1/1/2014
2 1 1/1/2014
3 1 1/1/2014
1 2 8/1/2014
2 2 8/1/2014

 

[kacey8]

I appreciate that, Unfortunately without then going through and changing the 12-13k of records being imported into the DB from Excel I would be unable to do that.

For that reason I will continue on the method I am doing (which does work) but will take a little longer.

 

[vbaInet]

It's easy enough to use queries to update the ~13k records to suit what CJ and Uncle Gizmo have advised. If you want you could add an extra field which counts how many letters were included in that report (just in case legacy records are deleted), however, I don't know how useful this extra field would be to you.

Also remember that with the suggestion, you'll be saving memory space.

 

[kacey8]

Thanks guys...

Will investigate and try to get an update working on the legacy records. Once I am confident the update works I will work on the method suggest above.

 

[vbaInet]

Hopefully you remember to do all of this on a backup copy

 

[kacey8]

Hopefully you remember to do all of this on a backup copy

Always I have my "live" system which has all of the features I have added which I cam confident in and then I have my test system.

And of course each of those have tables which are backed up and front ends backed up.

Then our Server backs everything up nightly as well. lol.