Running code on query results

[rjmccafferty]

For mailings, we use tag fields (yes/no field) in our Contacts table to select recipients for the mailings. It is then easy to query the db and have only the records you have selected. After the mailing, it needs to be documented in the db by COMPANY, not by contact person.

In Excel I do the following

I run a macro on data like the following to combine names (I will explain further after the sample data.

ABC Co. Smith
ABC Co. Adams
ABC Co. Samson
Acme Jones
Ebay, Inc. Johnson

I want to end up with only one line for each company name and all individuals listed together for that company. Thus, the end result to the above would be:

ABC Co. Smith and Adams and Samson
Acme Jones
Ebay, Inc. Johnson

Please see the code below in VBA for Excel:

cust1 = ActiveCell.Offset(0, 0)
contact1 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Range("A1&quot.Select

While ActiveCell.Formula <> &quot;&quot;
cust2 = ActiveCell.Offset(0, 0)
contact2 = ActiveCell.Offset(0, 3)
If cust2 = cust1 Then
contact2 = contact1 & &quot; and &quot; & contact2
ActiveCell.Offset(0, 3) = contact2
ActiveCell.Offset(-1, 0).Range(&quot;A1&quot.Select
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Range(&quot;A1&quot.Select
cust1 = ActiveCell.Offset(0, 0)
contact1 = ActiveCell.Offset(0, 3)
End If
Wend

It seems to work with one problem - If the first two records are the same company, I lose my comparisons with cust1 and cust2. I believe I need a statement that basically says,

If ActiveCell.Offset(0, 0) = BOF then ...

It seems this would more easily done in Access over the long run, rather than exporting the data to Excel to manipulate it and then reimport it into Access for the last step of processing.

When I have the completed answer set, I use an update query to make the entries needed in each company record so we know later who received the mailings.

I do NOT want to get away from queries to get the original answer set or disperse the finished answer set. I need to keep this as simple and flexible as possible for highly unsophisticated users to learn. They understand how to get the initial record set, I have taught them how to properly use an update query. I want just enough code to transform the data.

I have several problems.

1. The largest problem is that I do not know how to run code on the answer set to a query. I assume I would need to use a temporary table into which I could dump the query results. Is that correct? Or can I reference the query in the code to get the answer set directly?

2. I am a little confused by the EOF/BOF syntax.

Can anyone help get me through this?