Grouping Records

[Question Boy]

Hello,

I am trying to manipulate a contact table (Id,FirstName,LastName,
Address,City,Prov,PostalCode,Phone1,Phone2,Email,...)

In the table I can have, and do have, multiple entry for a given postal
code. Basically, I have an entry for a Husband and another for the Wife.
Not the way I would have created the structure but I am stuck with it (cannot
be altered).

I am trying, and having trouble, creating a query that would 'group' the
records so that I get a unique listing by postal code. I used a Concatenate
function to combine the firstname for a given postal code but am now having a
problem telling the query to return the 1st phone1. if rec1 has phone1 use
it, otherwise use the phone1 from rec2...

For instance (- added to separate fields)
472-Devon-Julian-345 Farley-Marville,Quebec,J01AR3,5145555555,,,
876-Jane-Hunry-345
Farley-Marville,Quebec,J01AR3,5146666666,6135555555,[email protected],

I need to build a query to return
Devon & Jane,Julian--345
Farley-Marville,Quebec,J01AR3,5145555555,6135555555,[email protected],

Thank you for your help,

QB

 

[Dale Fye]

I can understand why you might want to concatenate names from the same
address, but what if you have more than 2 names at that address?

Jane's last name is Hunry, not Julian. Are you really sure you want to
create a postal address that calls her Jane Julian? I could understand doing
that when they both have the same last name, but if they have different last
names do you really want to concatenate in that manner? Which last name do
you use? If the man's, do you have a field that identifies the record as
belonging to a man? What if the house contains two men or two women?

Depending on what you want to do with this query, my recommendation would be
to create a function that accepts an address and returns a PostalName, which
might be:

Devon & Jane Julian
Devon Julian & Jane Hunry
Dr. Devon Julian and Jane Hunry
.... whatever

You would then need to have a separate function for PostalPhone or any other
field you want to field you want to concatenate or only select one of
multiples. This function would open the recordset to all those records
containing that postal code, and would use some logic to determine which
value to return.

Then create a query that groups by the Postal Code and passes the postal
code to the PostalName( ) and PostalPhone( ) functions.


HTH
Dale