append query or other to perform a task?

[Jack]

Hi,

I'm wondering if someone can help with the following problem. I have
2 tables in an access database from which I would like to create a
third table.

They look like this:

table 1 table 2
======== ========
FNAME LNAME FNAME LNAME COLOR
Jack Smith Jack Smith Green
Jack Smith Jack Smith Blue
Jack Smith Jack Smith Red
Jack Smith Jack Smith Orange
John Orr John Orr Black
John Orr John Orr Brown
John Orr John Orr Blue

I'd like Access to perform a comparison and find the same FNAME and
LNAME
in tables above, remove any duplicates, and pull out the data from
the correct 'color' fields into one 'colorsummary' field as
illustrated below:

RESULT:

FNAME LNAME COLORSUMMARY
===== ===== =============
Jack Smith Green, Blue, Red, Orange
John Orr Black, Brown, Blue

Any suggestions on how to achieve this is much appreciated. I have
worked before with Access queries (and after much experimenting dont
seem to be getting far!) but obviously am missing the needed expertise
to make the above happen. Alternately, if you have other suggestions
of other software/languages to use which would be simpler (eg., excel
or some DB language) that would be appreciated.

Thanks
Jack

[Lyle Fairfield]

ja******@lycos. co.uk (Jack) wrote in
news:69******** *************** ***@posting.goo gle.com:

Hi,

I'm wondering if someone can help with the following problem. I have
2 tables in an access database from which I would like to create a
third table.

They look like this:

table 1 table 2
======== ========
FNAME LNAME FNAME LNAME COLOR
Jack Smith Jack Smith Green
Jack Smith Jack Smith Blue
Jack Smith Jack Smith Red
Jack Smith Jack Smith Orange
John Orr John Orr Black
John Orr John Orr Brown
John Orr John Orr Blue

I'd like Access to perform a comparison and find the same FNAME and
LNAME
in tables above, remove any duplicates, and pull out the data from
the correct 'color' fields into one 'colorsummary' field as
illustrated below:

RESULT:

FNAME LNAME COLORSUMMARY
===== ===== =============
Jack Smith Green, Blue, Red, Orange
John Orr Black, Brown, Blue

Any suggestions on how to achieve this is much appreciated. I have
worked before with Access queries (and after much experimenting dont
seem to be getting far!) but obviously am missing the needed expertise
to make the above happen. Alternately, if you have other suggestions
of other software/languages to use which would be simpler (eg., excel
or some DB language) that would be appreciated.

Thanks
Jack

Don't! Your plan violates the principle of atomicity; a row-column
intersection should hold just one value.

Read a little about
normalization,
relationships,
and
subforms/subreports.

You could end up with
something like:

People

UniqueID FName LName
1 Jack Smith
2 John Orr

Colours

UniqueID Color
1 Red
2 Orange
3 Yellow
4 Green
5 Blue
6 Purple
7 Brown
8 Black

PeopleWithColor s

UniqueID PeopleID ColorID
1 1 1
2 1 2
3 1 4
4 1 5
5 2 5
6 2 7
7 2 8

While this may seem strange, as you work with it, you will find that it
<<<may>>> be the most efficient design that you can create.

Access Report and Form frameworks were created with this type of design in
mind, ... or they should have been.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Jack]

Thanks Lyle for your quick reply

FNAME and LNAME below are just examples as the application I'm working
on is to have my database remove duplicate addresses for the purpose
of a mailing

At each address I'd like a summary field (like a memo field, freeform
text field) which shows the names of people at that same address:

for example, source data looks like this

name address
joe 2 main st
sue 6 oak blvd
tom 2 main st
jim 2 main st
art 6 oak blvd

and I want the resulting database to look like this

address names
2 main st joe, tom, jim
6 oak blvd sue, art

the reason for this is so I can treat each address as one record and
have the names summarized in one field so these can be presented in
one place on the merge letter I'm creating

Any help on how to do this appreciated

Thanks,Jack

Don't! Your plan violates the principle of atomicity; a row-column
intersection should hold just one value.

Read a little about
normalization,
relationships,
and
subforms/subreports.

[Pieter Linden]

This should do what you want:

http://www.mvps.org/access/modules/mdl0004.htm

Say John, Jack & Jill share the same address....

Your query result would look something like:

John, Jack, Jill
Address
City, State, Zip

[Jack]

Thanks Pieter - I will give that a try!

Jack

pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ***@posting.goo gle.com>...

This should do what you want:

http://www.mvps.org/access/modules/mdl0004.htm

Say John, Jack & Jill share the same address....

Your query result would look something like:

John, Jack, Jill
Address
City, State, Zip