mailing list.

Hello,
I'm still a learner. I need help on my ms access.

I have a mailing list, in my query, for newsletter distribution. But some recipients on that list have the same address, i.e., they are families.
How to generate a list that could display all similar address into one label, but with all the respective names on it?

If this can be done, I could save alot on postages.

Thanks.

Would it be enough to say something like Bob Smith and Family?

If so, is there a way of determining who the head of the household is (age field, the one with the smallest identifier, etc.?)

Bob smith and family, would be good. but how to do it?
The only thing that is common among them is their addresses and not last names.
thanks.

Couple of questions:
1) How are the names arranged in the table? (one field or two)
2) Do you have any preference as to which of the people at the given address it is addressed to?
2a) Is there a designation in the database that we can find to match that preference?

SELECT (first_name || ' ' || last_name || ' and Family') AS Household

This is Oracle SQL. You may need to adjust the single quotes to double quotes or something similiar. This doesn't break it down to a single person at each address, but it's a start

The fields are as follows, NAME, ADDRESS, POSTAL CODE, MEMBERS NO.(primary key). Its not important to display the name of head of the house on the label. it does'nt matter if there is no name at all.

presently, I make a copy of the file. Sort according to address. Hence, those with the same address are grouped together. Then I would delete all entry from the same house, but sparing one.

Anyway, to answer yr question:
1. One field
2. & 2a. No.

Thanks.

SELECT DISTINCT Address,
postal_code
FROM MailingListTableName

will give you only one occurance of each address... but if you put in that select alias from the previous post, you'll get each person rather than individual addresses.. sounds like you might have to write a function to sort them and pick one.

You can probably use VB to write a function to perform a single name match on the query above to give you a name and street address.

The problem is that you're storing the same Address data multiple times. If having multiple members be associated with one common address is a requirement of your database, then you shouldn't store it all in a single table.

Make a separate "Address" table, and add a foreign-key onto the Member table which points to the Address table:



To get all members and their addresses, do

SELECT member.name, address.address from member, address where address.addr_ref = member.addr_ref;


To get a list of unique addresses, do these two queries inside a report:

SELECT distinct member.addr_ref as curr_AddrRef, Address.address from Member, Address where address.addr_ref = member.addr_ref;

For each record that you get, run a subreport, linked on addr_ref, using this query:

SELECT Member.name from member where member.addr_ref = currAddrRef;


Your report would look something like: