Combining multiple rows

[Sola]

Hi, everyone. I have a database (example included) where I can't get a query to work like I want. I have looked online, but the examples don't seem to match my problem (or I am missing something). Here is the setup:

tblMain is set up with one record for each acct #.

tblClient contains the first and last names of each client. There can be up to 4 clients on each account, so the table is set up in a many-to-one relationship with tblMain. The # in the Position field indicates the order in which the clients are listed on the account.

I am trying to set up a query (for later use in a report) that displays like this:
Acct, Client1, Client2, Client3, Client4, OtherInfo.

I have used both Iif and Switch to get the first and last names to drop into the correct ClientX slot (based on position), but it isn't combining them into one row.

Qry:
Acct, Client1: Iif([Position]=1,[LastName] & ", " & [FirstName], Null), Client2: Iif([Position]=1,[LastName] & ", " & [FirstName], Null), etc.

Result:
Acct / Client1 / Client2 / Client3 / Client4 / OtherInfo
123 / Abigail / (null) / (null) / (null) / Zzz
123 / (null) / Adam / (null) / (null) / Zzz
123 / (null) / (null) / Alice / (null) / Zzz
123 / (null) / (null) / (null) / Ansel / Zzz

Qry:
Acct, Client1: Switch([Position]=1,[LastName] & ", " & [FirstName],[Position]=2,[LastName] & ", " & [FirstName],[Position]=3,[LastName] & ", " & [FirstName],[Position]=4,[LastName] & ", " & [FirstName]), Client2: Switch([Position]=2,[LastName] & ", " & [FirstName],[Position]=3,[LastName] & ", " & [FirstName],[Position]=4,[LastName] & ", " & [FirstName]), etc.

Result:
Acct / Client1 / Client2 / Client3 / Client4 / OtherInfo
123 / Abigail / (null) / (null) / (null) / Zzz
123 / Adam / Adam / (null) / (null) / Zzz
123 / Alice / Alice / Alice / (null) / Zzz
123 / Ansel / Ansel / Ansel / Ansel / Zzz

 

[ajetrumpet]

this might be a place to start:

SELECT tblclient.Acct, 
  DLookUp("[LastName] & ', ' & [FirstName]","tblclient", "[position] = " & 
    [position] & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client1, 

DLookUp("[LastName] & ', ' & [FirstName]","tblclient", "[position] = " & 
  [position]+1 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client2,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient", "[position] = " & 
  [position]+2 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client3,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient", "[position] = " & 
  [position]+3 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client4, 

tblMain.Item1, tblMain.Item2, tblMain.Item3

FROM tblMain INNER JOIN tblclient ON tblMain.Acct = tblclient.Acct;

That only works though if you have a set number of maximum clients for an account. Like, in this example, 4. If that always changes, you are going to have to use a called procedure I presume. Also, it is not what you want. But...it could serve as a base for a procedure to do the rest of the work.

 

[Sola]

You seriously rock. I set the 2nd [position] in the criteria part to the # 1-4, then put grouping on:

SELECT tblclient.Acct,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient","[position] = " & 1 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client1,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient","[position] = " & 2 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client2,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient","[position] = " & 3 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client3,

DLookUp("[LastName] & ', ' & [FirstName]","tblclient","[position] = " & 4 & " AND tblclient.[acct] = " & tblclient.[acct]) AS Client4,

tblMain.Item1, tblMain.Item2, tblMain.Item3

FROM tblMain INNER JOIN tblclient ON tblMain.Acct = tblclient.Acct

GROUP BY tblclient.Acct, etc.;

And I got this:

Acct / Client1 / Client2 / Client3 / Client4 / OtherInfo
123 / Adams, Abigail / Adams, Adam / Adams, Alice / Adams, Ansel / Zzz
456 / Brown, Bob / Brown, Betty / (null) / (null) / Zzz
789 / Carter, Carla / (null) / (null) / (null) / Zzz

Thank you SO much!

 

[ajetrumpet]

no problem. good luck!

 

[khawar]

You should not be using dlookup in query when it colud simply be done without this

try this Sql

TRANSFORM First([LastName] & ", " & [FirstName]) AS Name
SELECT tblClient.Acct, 
tblMain.Item1, 
tblMain.Item2, 
tblMain.Item3
FROM tblMain INNER JOIN 
tblClient ON tblMain.Acct = tblClient.Acct
GROUP BY 
tblClient.Acct, 
tblMain.Item1, 
tblMain.Item2, 
tblMain.Item3
PIVOT "Client " & [Position];

an additional benefit is that you dont have to collect 4 clients individually and in case some account has more than 4 clients it will automatically pick that whereas the previous solution will skip any client whose position is more than 4

 

[Sola]

Hi, Khawar. Thanks for the alternate method. It works great, too.