combining households

  • Thread starter Thread starter bfreeman
  • Start date Start date
B

bfreeman

greetings,

Let me give you an overview of what's going on in the query and the
end/goal that I'm trying to meet. I have a volunteer database built
with access 2000. My goal in this query is to take the volunteers that
have the same address (i.e. husbands and wives and brothers sisters,
etc.) and send one letter to that household, instead of multiple.
Hoping that this will cut some of my office supplies expense.

The code below gets me the volunteers i need and their addresses,
however I am unable to produce the correct code to look at the
addresses that are the same and combine the names and mailing address,
for example:

John Doe
123 Helpme Lane
Cleveland, Oh 44124

Jane Doe
123 Helpme Lane
Cleveland, Oh 44124

So.... it would be

John Doe
Jane Doe
123 Helpme Lane
Cleveland, Oh 44124


Obviously, I am lost when it comes to Visual Basic.... But for some
reason I think that the code in VB would be use something like Case 1,
Case 2..... I'm probably incorrect. I understand that the code in VB
should run a query and sort by address, then check and see is line 2 =
line 1, etc. But, I am unsure in how to structure this. Also, not
sure if the code would be a module or what.... I would really
appreciate some feedback.

Thanks.


Code:
--------------------

SELECT Volunteers.VolunteerID
, Volunteers.FirstName
, Volunteers.VolunteerTypeID
, Volunteers.StatusNo
, Volunteers.DeliveryTypeID
, [Address] & ", " & [Address2] AS FullAddress
, [City] & ", " & [State] & " " & [PostalCode] AS CityState
FROM VolunteerType
INNER JOIN Volunteers
ON VolunteerType.VolunteerTypeID = Volunteers.VolunteerTypeID
WHERE (((Volunteers.VolunteerTypeID)=1
Or (Volunteers.VolunteerTypeID)=2)
AND ((Volunteers.StatusNo)=3
Or (Volunteers.StatusNo)=5)
AND ((Volunteers.DeliveryTypeID)=3))
ORDER BY Volunteers.FirstName;
 
Back
Top