Working with databases, you would *not* just hope the the user happens to
spell the address idendically to determine how many people live at an
address. For example, if Jack lives at 433 Bubba Ct, and Joe lives at 443
Bubba Court, your current approach will not work.
The correct approach is to create a table to hold just the addresses. It
will have an AddressID primary key. Then create another table to hold the
people. The People table will not have address fields: it will have an
AddressID field that identifies which address the person belongs to. This is
a classic one-to-many situation: one address can be for many people.
Once you have done that, you can do stuff such as:
- Get the count of the number of people at an address with a Totals query.
- Use a subquery to get the count of people at the address, and list the
people (as in your example). Subquery basics:
http://support.microsoft.com/?id=209066
- Concatentate the names for an address onto a label:
http://www.mvps.org/access/modules/mdl0004.htm
That's the simple approach. In the real world, things can get more involved,
e.g:
- A child who spends half their time with Dad and half with Mum, so they are
part of 2 families and have 2 addresses;
- People and companies who have a postal address and a street address, and
keeping track of which address to use in different scenarios;
- Companies that have addresses in multiple locations;
- Maintaining mailing lists that consist of both persons and companies.
To track those kinds of things in a reliable way requires some creative
thinking beyond the basics we outlined above. If you need to do this, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
BTW, the "extra parenthesis" message is easy to solve: just remove the extra
bracket.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Phoenix_ver10" <ph***********@hotmail.com> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...
I have a mailing list with multiple names going to the same addresses.
I need one address with all the names for that address on it. I checked
out the example on microsoft's site, but A: It doesn't work (error that
there is an extra parenthise (sp?) ) and B: Will only let in two names
for each record. If there are three, the middle on is deleted.
Or to make things simpler, if nothing else, I'd like to add a field in
the table that shows how many times each street address shows up in the
table. For instance:
john, 333 joe ln, 3
steve, 333 joe ln, 3
fred, 333 joe ln, 3
joe, 433 bubba ct., 2
jack, 433 bubba ct., 2
etc.. That way I can manually dice the thing up in excell and do what I
need. Mind you I know jack about database programming. Thanks :-).
