I have construced a string of people's surname who live in the same house
Field name is XFirstSN
E.G.
Blackburn, Blackburn, Blackburn, Blackburn, Blackburn,
Brooke, Brooke, Brooke, Edwards, Brooke, Brooke,
Chalmers, Goodwin,
Douglas, Allott, Douglas, Douglas,

Rossi, Rossi, Rossi, Rossi, Rossi, Rossi, Rossi, Rossi, Rossi,

I want to reduce it to the basic names E.G
Blackburn
Brooke & Edwards
Chalmers & Goodwin
Douglas & Allott
Rossi

FirstSN: [Member]![MemSurName] & IIf([Member_1]![MemSurName]>"" And
[Member_1]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_1]![MemSurName]) & IIf([Member_2]![MemSurName]>"" And
[Member_2]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_2]![MemSurName]) & IIf([Member_3]![MemSurName]>"" And
[Member_3]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_3]![MemSurName]) & IIf([Member_4]![MemSurName]>"" And
[Member_4]![MemSurName]<> [Member]![MemSurName]," & " &
[Member_4]![MemSurName]) & IIf([Member_5]![MemSurName]>"" And
[Member_5]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_5]![MemSurName]) & IIf([Member_6]![MemSurName]>"" And
[Member_6]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_6]![MemSurName]) & IIf ([Member_7]![MemSurName]>"" And
[Member_7]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_7]![MemSurName]) & IIf([Member_8]![MemSurName]>"" And
[Member_8]![MemSurName]<>[Member]![MemSurName]," & " &
[Member_8]![MemSurName])

This horrendoud IIf clause produces the right result, but I suspect it only
works for 2 names The number of Member tables (Member to Member_8) is
determined by the maximum number of people living at 1 address so this is
variable. If instead of the current maximum of 9 people, it drops to say 8,
the IIf clause will fail because thee won't be a Member_8

Incidenly, this is the query that is the source of the query that has the IIf
clause. I suspext that as this is based on a crosstab query, "XAllNames", I
am going to have to create it on the fly. SELECT Member.MemSurName,
Member.MemFirstName, Member.MemTitle, Member.MemTitleOption,
Member_1.MemSurName, Member_1.MemFirstName, Member_1.MemTitle,
Member_1.MemTitleOption, Member_2.MemSurName, Member_2.MemFirstName,
Member_2.MemTitle, Member_2.MemTitleOption, Member_3.MemSurName,
Member_3.MemFirstName, Member_3.MemTitle, Member_3.MemTitleOption,
Member_4.MemSurName, Member_4.MemFirstName, Member_4.MemTitle,
Member_4.MemTitleOption, Member_5.MemSurName, Member_5.MemFirstName,
Member_5.MemTitle, Member_5.MemTitleOption, Member_6.MemSurName,
Member_6.MemFirstName, Member_6.MemTitle, Member_6.MemTitleOption,
Member_7.MemSurName, Member_7.MemFirstName, Member_7.MemTitle,
Member_7.MemTitleOption, Member_8.MemSurName, Member_8.MemFirstName,
Member_8.MemTitle, Member_8.MemTitleOption FROM Member AS Member_8 RIGHT JOIN
((((((((XAllNames LEFT JOIN Member ON XAllNames.[1] = Member.MemberID) LEFT
JOIN Member AS Member_1 ON XAllNames.[2] = Member_1.MemberID) LEFT JOIN
Member AS Member_2 ON XAllNames.[3] = Member_2.MemberID) LEFT JOIN Member AS
Member_3 ON XAllNames.[4] = Member_3.MemberID) LEFT JOIN Member AS Member_4
ON XAllNames.[5] = Member_4.MemberID) LEFT JOIN Member AS Member_5 ON
XAllNames.[6] = Member_5.MemberID) LEFT JOIN Member AS Member_6 ON
XAllNames.[7] = Member_6.MemberID) LEFT JOIN Member AS Member_7 ON
XAllNames.[8] = Member_7.MemberID) ON Member_8.MemberID = XAllNames.[9];

Any thoughts please?

Phil

Phil wrote:
Then use that view as the source for your concatenation instead of the
original table.
--
HTH,
Bob Barrows

My first thought is that you need to restructure the tables in your database.
You have repeating data Member_1, Member_2, etc. That implies that you need a
HouseHolds table and a Members table. The Members table would look
something like
HouseHoldID (a value from the primary key of the households table)
MemberSurname
MemberNumber (if you had to order the names in some specific order, right now
1 to 8 would be the possible values)
MemberFirstName
....

If you cannot do this then use a UNION query to return a unique list of member
surnames in each household and concatenate them together with one of the
user-defined VBA concatenate functions.

SELECT HouseholdID, Surname_1
FROM Households
UNION
SELECT HouseholdID, Surname_2
FROM Households
UNION
....
UNION
SELECT HouseholdID, Surname_8
FROM Households

With that query you will have a unique set of names for each household.

Here are links (url) to three examples on concatenating a field from multiple
records into one field in one record.

You might be able to use a query against the original table now that I've
looked a little bit further at your posted Query. There must be a field that
ties each individual to a specific household (many individuals, one
household). I've posted a generic sample using Duane Hookom's function.

SELECT DISTINCT SomeHouseholdField,
Concatenate("SELECT Distinct MemSurName FROM Member WHERE SomeHouseholdField =
" & Member.SomeHouseholdField," & ") as NameList
FROM Member

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Phil wrote:

John Spencer wrote:
That was my thought as well, until I looke more closely at his query and
realized that the Member_X identifiers were aliases for the Members
table which was joined multiple times

LEFT JOIN
--
HTH,
Bob Barrows

On 10/09/2010 18:28:56, "Bob Barrows" wrote:

Spot on Bob.

Member Table structure is
MemberID
MemSurName
MemFirstName
MemAddID ' This links all the members of a houshold together
MemPosInHome ' Unique number for sorting name order
....
....
At the moment, I use user defined functions XNames() to produce the results I
want such as FirstNames "Fred, Mary, Sonny & Jane" or SurNames "Douglas &
Allott"

Now I want to use a query using this user defined function XNames() for a
mail merge, but can't link to Access using OLE or ODBC because OLE doesn't
see queries with user defined functions, and althogh ODBC sees the query, it
won't run it. That leaves DDE as the only linking option, and that is
thoroughly unreliable. (See my posting "Query problem / DDE connection)

On the BeforeUpdate I give each Member living at that address a unique number
from 1 to currently 9 (Currently the highest no of people living at one
address, but this of course will change). This is MemPosInHome

So I took Salad's advice and created a CrossTab query.

The CrossTab is XAllNames
TRANSFORM Min(Member.MemberID) AS MinOfMemberID
SELECT Member.MemAddID
FROM Member
GROUP BY Member.MemAddID
ORDER BY Member.MemAddID
PIVOT Member.MemPosInHome;

This produces the numbers 1 to 9 as the column heads, The MemAddID as the row
heads and the MemberID as the value. So by linking this XAllNames Crosstab to
9 Member tables, I get among other things, 9 colums of MemFirstName and 9
columns of MemSurName, although for small families most of the colums are
blank, but frequently there is a duplication of MemSurName ( Husband, wife &
children all living together). Occassionally there are people with different
surnames living at the same address.

As mentioned in OP, I need all the individual MemFirstName which I
concatenate so that I can do a "Dear Fred, Mary, Sonny & Jane" but I don't
want duplicate MemSurName.

Hope this clarifies things

Phil

On 10/09/2010 18:25:29, John Spencer wrote:
Thanks John.

Trying to avoid yet another table, but certainly your idea of a household
table will work. I can use my existing functions to populate it everytime I
update a Member Record. But somehow I hate duplication of information in
different tables, even when it is generated automatically. I may be forced to
adopt this method in the end

Allen's Concatenate function is far more sophisticated than the one that I
wrote, so will use that in future

Thanks

Phil