473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Anding a field for record count. Same address multiple name.

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 :-).

Mar 4 '06 #1
1 2900
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 :-).

Mar 5 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Randell D. | last post by:
Folks, Perhaps someone can figure this out - this is 'the process of my script' I have a form whereby I can add multiple contacts to a single address. There is only one...
20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
5
by: Theresa Hancock via AccessMonster.com | last post by:
I have an Excel table I need to import into Access. The name is entered into one field "Name". I'd like to have two fields in Access, FirstName and LastName. How do I do this. -- Message posted...
8
by: Bri | last post by:
Greetings, I am using Eval() in a query with only limited success. If the text within the function contains a reference to a Field I get #ERROR#. I'll try and explain what I'm trying to do and...
4
by: Deborah V. Gardner | last post by:
I have a multi-user database in Access 2000. The back end is on the server and the front end is on each machine. The user will go to a field (defined as data type Memo) and make some changes. Then...
1
by: yldchld13 | last post by:
I'm new to both SQL and this message board so please be kind. I've got a large dataset (1.9 million records) with various information. Within this dataset, there is an address field that is...
1
by: chocciies | last post by:
Hi! I want to delete multiple record via checkboxes. However, with no success. Is there sthg wrong with the value of my checkbox? Your help will be greatly appreciated. Thanks! <form...
7
by: kirkgilbert | last post by:
I am trying to do an onchange event in a form using a text field. The form is tied to a record set that is part of a repeated region. One the first record when I edit the data it works perfectly. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.