Concatenating fields together with common information

[Daniel Pineault]

Hello,

I have a table setup like

[Hist ID],[First Name],[Last
Name],[Address1],[City],[Prov],[Country],[Postal Code],...

Thus I end up with data similar to

123,Henry,Oband,14 grey,Dorval,Qc,CDA,H5P 3C2,...
225,Mary,Sinclair,14 grey,Dorval,Qc,CDA,H5P 3C2,...
758,Marvin,Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4,...

How can I build a quey to group common address information together. In the
above example, Henry and Mary reside at the same address and thus I would
like the query to return somthing in the nature of

[Name],[Address1],[City],[Prov],[Country],[Postal Code]

ie:
Mary Sinclair & Henry Oband ,14 grey,Dorval,Qc,CDA,H5P 3C2
Marvin Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4

Where I group by address and concatenate the First & Last name fields
together into one and eliminate all other fields (only interested in address
listing).

Thank you for the help!

Daniel Pineault

 

[Rod Plastow]

Daniel,

I don't think you can do this with simple SQL. Anyone?

However it does seem to me to be a case for Data Shaping. If you're up to
it read up on the SHAPE command. Even then I think you are in for some VBA
coding to poke the shaped data into a new table.

Rod

 

[Jerry Whittle]

The simple, easy, but not very personal way:

SELECT DISTINCT "Resident" AS [To],
TblAddresses.Address1,
TblAddresses.City,
TblAddresses.Prov,
TblAddresses.Country,
TblAddresses.[Postal Code]
FROM TblAddresses;

It just says "Resident" instead of listing names. Hower above SQL does have
another advantage. What happens when there are more than 2 people living at
the same address? If you have just two people, it might be possible to do it
with somewhat complex SQL; however, if there can be more, you'll need some
fancy code to pull it out.

 

[Dale Fye]

Besides Jerry's recommendation about using resident, which is a little
impersonal, you might want to consider the political correctness of whatever
ordering scheme you develop. Etiquette wise, there are specific style rules
(search +address +etiquette in google for examples) about how you
concatenate names for this type of thing. The degree to which you desire to
use etiquette rules will be determined by how thorough your database is and
the degree of formality of your letter.

Mary Sinclair and Henry Oband or
Henry Oband and Mary Sinclair or
Dr. Sinclair and Henry Oband or
Doctors Mary Sinclair and Henry Oband

Personally, I don't care, but if your recipients are professionals (Dr's,
lawyers, military) or politicians (I don't call these professionals,
although some believe they belong to the worlds oldest profession), they may
feel slighted if the proper sequencing and titles are not used. My thought
is it is probably better to keep it safe with resident (if there are
multiple records for an address). If there are multiple names, but the same
last name, you might go with "The Oband household" or something like that.
You might even want to create a TitleOrder table where you rank order the
various titles you expect to use, and use that ordering to determine the
precedence of your salutation.

Hope this doesn't just confuse the issue too much ;-)

Dale

 

[Daniel Pineault]

Dale,

I have been thinking about this already! It can be quite a can of worms...
Still haven't figured out how to handle all the permutations properly.
Sadly, residents is just too impersonnal for the current needs so I have to
come up with a routine that can make the proper concatenation (also based on
the boss' opinion - more important than etiquette).

BTW I liked you line: "politicians (I don't call these professionals,
although some believe they belong to the worlds oldest profession)".
However, I think that it's very insulting for the employees of our worlds
oldest profession!

Daniel P

Besides Jerry's recommendation about using resident, which is a little
impersonal, you might want to consider the political correctness of whatever
ordering scheme you develop. Etiquette wise, there are specific style rules
(search +address +etiquette in google for examples) about how you
concatenate names for this type of thing. The degree to which you desire to
use etiquette rules will be determined by how thorough your database is and
the degree of formality of your letter.

Mary Sinclair and Henry Oband or
Henry Oband and Mary Sinclair or
Dr. Sinclair and Henry Oband or
Doctors Mary Sinclair and Henry Oband

Personally, I don't care, but if your recipients are professionals (Dr's,
lawyers, military) or politicians (I don't call these professionals,
although some believe they belong to the worlds oldest profession), they may
feel slighted if the proper sequencing and titles are not used. My thought
is it is probably better to keep it safe with resident (if there are
multiple records for an address). If there are multiple names, but the same
last name, you might go with "The Oband household" or something like that.
You might even want to create a TitleOrder table where you rank order the
various titles you expect to use, and use that ordering to determine the
precedence of your salutation.

Hope this doesn't just confuse the issue too much ;-)

Dale

Hello,

I have a table setup like

[Hist ID],[First Name],[Last
Name],[Address1],[City],[Prov],[Country],[Postal Code],...

Thus I end up with data similar to

123,Henry,Oband,14 grey,Dorval,Qc,CDA,H5P 3C2,...
225,Mary,Sinclair,14 grey,Dorval,Qc,CDA,H5P 3C2,...
758,Marvin,Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4,...

How can I build a quey to group common address information together. In
the
above example, Henry and Mary reside at the same address and thus I would
like the query to return somthing in the nature of

[Name],[Address1],[City],[Prov],[Country],[Postal Code]

ie:
Mary Sinclair & Henry Oband ,14 grey,Dorval,Qc,CDA,H5P 3C2
Marvin Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4

Where I group by address and concatenate the First & Last name fields
together into one and eliminate all other fields (only interested in
address
listing).

Thank you for the help!

Daniel Pineault

 

[AccessVandal via AccessMonster.com]

Hi Daniel,

Have you tried Duane Hookom's concatenantion sample?

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records
'

Hello,
I have a table setup like
[Hist ID],[First Name],[Last
Name],[Address1],[City],[Prov],[Country],[Postal Code],...
Thus I end up with data similar to
123,Henry,Oband,14 grey,Dorval,Qc,CDA,H5P 3C2,...
225,Mary,Sinclair,14 grey,Dorval,Qc,CDA,H5P 3C2,...
758,Marvin,Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4,...
Daniel Pineault

 

[Daniel Pineault]

Actually that is exactly where I'm at right now! I have to perform some data
manipulation first though.

Daniel P

Hi Daniel,

Have you tried Duane Hookom's concatenantion sample?

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records
'

Hello,
I have a table setup like
[Hist ID],[First Name],[Last
Name],[Address1],[City],[Prov],[Country],[Postal Code],...
Thus I end up with data similar to
123,Henry,Oband,14 grey,Dorval,Qc,CDA,H5P 3C2,...
225,Mary,Sinclair,14 grey,Dorval,Qc,CDA,H5P 3C2,...
758,Marvin,Hemlock,2767 Milot,Laval,Qc,CDA,J7T 8Z4,...
Daniel Pineault