rjmccafferty
IS-IT--Management
The scenario in my problem is that I use flag fields (Yes/No fields in the tables) for queries to determine recipients for various mailings that we do. That is, anyone who has a specific flag field checked as Yes would have their mailing information used in the mail merge in Word.
I have tables for Customer and Contacts. Typically, I would choose CustName from the Customer table and all the aapropriate address fields from Contacts. And usually I use a flag field in contacts to specify the recipients of the letters. I then need to document, in a third table, who received the mailing. I use CustID field to relate the record in the third table to Customer table. For a simple example, I might send a mailing to:
Customer Contact
A Joe Smith
B John Jones
B Jane Doe (There could be any number of contacts receiving the mailing for each company)
The follow up entries I want are:
Sent Whatever mailing to Joe Smith (with this related to Customer A via the custID field)
Sent Whatever mailing to John Jones and Jane Doe (with this related to Customer B)
The easy way would be to have two entries made to the third table related to Customer B, one for John Jones receiving the mailing, a second for Jane Doe having received it. I could just take the results of my initial query used for the mailmerge and run an append query to the third table. But this takes up too many lines in the table and on reports and makes them less useful.
In the past, I have exported the data to Excel and run a loop with VBA code something like the pseudo-code below:
(On the first line, in CustName field) CN1 = CustName
Contact1 = ContactName
Begin Loop
Go to next line,
CN2=CustName
Contact2 = ContactName
If CN2=CN1
ContactName = Contact1 & " and " & Contact2
Contact1=ContactName
Delete Previous Row
EndIf
Continue until EOF
I would then import the resulting table back into access and use it as the source data to use an append query to update my third table. The only entries necessary in the third table are [ThirdTable].[CustID] (which is part of the results of the initial query from which the data is obtained) and [ThirdTable].[Regarding], which is where the custom wording is entered. In the append query, it would be similar to:
[ThirdTable].[Regarding]: "On 12/1/01 Sent Whatever mailing to " & [ContactNames] & " ."
I have two problems. The first is that this is surely the difficult way to do this. Second, and more important, I need to automate this in a way that other people in the office can perform the task in my absence, preferably using only Access.
I am trying to think of a way to do this without getting into arrays, a subject that would completely befuddle anyone else here. I am thinking that a temporary table might be a good way of doing it. Further, and probably more important, I need the user to be able to specificy a different set of wording for each use of the system.
My preference, because it would be more understandable to others would be the use of a series of queries rather than completely dropping into code.
Anyone have thoughts on how to best do this?
I have tables for Customer and Contacts. Typically, I would choose CustName from the Customer table and all the aapropriate address fields from Contacts. And usually I use a flag field in contacts to specify the recipients of the letters. I then need to document, in a third table, who received the mailing. I use CustID field to relate the record in the third table to Customer table. For a simple example, I might send a mailing to:
Customer Contact
A Joe Smith
B John Jones
B Jane Doe (There could be any number of contacts receiving the mailing for each company)
The follow up entries I want are:
Sent Whatever mailing to Joe Smith (with this related to Customer A via the custID field)
Sent Whatever mailing to John Jones and Jane Doe (with this related to Customer B)
The easy way would be to have two entries made to the third table related to Customer B, one for John Jones receiving the mailing, a second for Jane Doe having received it. I could just take the results of my initial query used for the mailmerge and run an append query to the third table. But this takes up too many lines in the table and on reports and makes them less useful.
In the past, I have exported the data to Excel and run a loop with VBA code something like the pseudo-code below:
(On the first line, in CustName field) CN1 = CustName
Contact1 = ContactName
Begin Loop
Go to next line,
CN2=CustName
Contact2 = ContactName
If CN2=CN1
ContactName = Contact1 & " and " & Contact2
Contact1=ContactName
Delete Previous Row
EndIf
Continue until EOF
I would then import the resulting table back into access and use it as the source data to use an append query to update my third table. The only entries necessary in the third table are [ThirdTable].[CustID] (which is part of the results of the initial query from which the data is obtained) and [ThirdTable].[Regarding], which is where the custom wording is entered. In the append query, it would be similar to:
[ThirdTable].[Regarding]: "On 12/1/01 Sent Whatever mailing to " & [ContactNames] & " ."
I have two problems. The first is that this is surely the difficult way to do this. Second, and more important, I need to automate this in a way that other people in the office can perform the task in my absence, preferably using only Access.
I am trying to think of a way to do this without getting into arrays, a subject that would completely befuddle anyone else here. I am thinking that a temporary table might be a good way of doing it. Further, and probably more important, I need the user to be able to specificy a different set of wording for each use of the system.
My preference, because it would be more understandable to others would be the use of a series of queries rather than completely dropping into code.
Anyone have thoughts on how to best do this?
