Taking data from one record, adding to previous record? Help!

F

Forrest Gump Ky

Back in the ancient days of dbase, this was simple, but now I'm stumped.

I'm an amateur programmer. I'm using a table of mailing data. When there
are two people at the same address, I want to put both names in a single name
record (such as Bill Jones and Sally Smith, instead of two records).

In dbase I would have just modified command, taken the street address, stuck
it in a memory variable, skipped to the next record, compared it to THAT
street address. For matches, I would then stick the name in the second
record in a variable, delete that second record, skip back one record and
replace name with Name plus " " plus &Memoryvariablecontents.

I've done some work with queries in Access and reports, but none with
modules or macros, so I will need someone to spell out the basics on this.

I'm sure this is something very easy, but does anyone have the solution, or
would someone please direct me to a subject that deals with it?

Thanks
 
D

Dirk Goldgar

Forrest Gump Ky said:
Back in the ancient days of dbase, this was simple, but now I'm
stumped.

I'm an amateur programmer. I'm using a table of mailing data. When
there are two people at the same address, I want to put both names in
a single name record (such as Bill Jones and Sally Smith, instead of
two records).

In dbase I would have just modified command, taken the street
address, stuck it in a memory variable, skipped to the next record,
compared it to THAT street address. For matches, I would then stick
the name in the second record in a variable, delete that second
record, skip back one record and replace name with Name plus " " plus
&Memoryvariablecontents.

I've done some work with queries in Access and reports, but none with
modules or macros, so I will need someone to spell out the basics on
this.

I'm sure this is something very easy, but does anyone have the
solution, or would someone please direct me to a subject that deals
with it?

Thanks
Click to expand...

You *can* follow pretty much the same procedure with Access, if you
want, using a recordset that is opened on a query that sorts the table
on the address in such a way that you can compare each record with the
previous. I can probably help you come up with code for that, if you
give me more information and if you still want me to after reading the
rest of this message.

I'd suggest, though, that if you want to record the fact that multiple
people live at the same address, it would be better to have two related
tables -- one table of addresses (with an AddressID key field) and one
table of people -- one record per person -- with the AddressID of each
person's address stored in the person's record. (That's assuming that
you don't have to cope with one person having multiple addresses -- then
you'd need three tables: Addresses, People, and PeopleAddresses to link
them.)

If you have addresses and people stored in different tables, then you
can readily determine at any time just who-all lives at any given
address, and mailings can be based on the table of addresses so as to
ensure that only one mailing is sent to any given address. Or, if you
produce your mailings as an Access report, you can easily base the
report on a join of Addresses and People (on AddressID) and group the
report by address, printing out something like.

Bill Jones
Sally Smith
123 Main St.
Somewhere, IN 99999
USA

If you want to get a line like "Bill Jones and Sally Smith", though, you
need code to concatenate the values from multiple People records.

The forms and queries to handle people and addresses in related tables
this way are admittedly more complicated than the simple,
one-record-with-multiple-people-in-the-name structure you described, but
the scheme has the advantage of accurately representing the relationship
between people and addresses, and facilitates manipulate and querying by
SQL. For example, it enables you to say things like "Give me a list of
all the people who live in zip code 12345 and whose names start with
'J'".

It really all depends on the tradeoff you choose to make between
"simple" and "powerful".
 
Top