concatenating multiple records from a query into one field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to access. I have a multi select list box on a customer form. I was
able to find code to insert behind a command button that will loop through
the selected list items and open a query with the results of the list box
selections. What I really need now, is it to be able to take the results of
the query and combine them into one field on the form and also be able to use
this field for a report. This is my data:
CustId Name PropType
3 Smith Land
3 Smith Retail
3 Smith Motel
3 Smith Warehouse

The current query will give me the output of Cust 3 as:
Land
Retail
Motel
Warehouse

With the code, I do not see the selections/query results on the form or know
how to combine them to one field that I can put in the form. Can anyone help
me with the code to do this?
 
This maybe a long way around but it will get you there. The first query gives
every property a ranking number and makes a table named mmais-2 from table
mmanis.

SELECT Q.CustId AS Customer, Q.Name AS Client, Q.PropType AS Property,
(SELECT COUNT(*) FROM mmanis Q1
WHERE Q1.[CustId] = Q.[CustId]
AND Q1.PropType < Q.PropType)+1 AS Rank INTO [mmanis-2]
FROM mmanis AS Q
ORDER BY Q.CustId, Q.PropType;

This second query pulls the data from mmanis-2 and creates a field for each
rank (property item). Set the IN(xxx function for the maximum number of
property items any may have (I set for 6 here).
mmanis-2_Crosstab --
TRANSFORM First([mmanis-2].Property) AS FirstOfProperty
SELECT [mmanis-2].Customer, First([mmanis-2].Client) AS Name
FROM [mmanis-2]
GROUP BY [mmanis-2].Customer
PIVOT [mmanis-2].Rank IN(1,2,3,4,5,6);

This query is built for the max of 6 property. For more add to the query.
SELECT [mmanis-2_Crosstab].Customer, [mmanis-2_Crosstab].Name, [1] & IIf([2]
Is Null,Null,", " & [2]) & IIf([3] Is Null,Null,", " & [3]) & IIf([4] Is
Null,Null,", " & [4]) & IIf([5] Is Null,Null,", " & [5]) & IIf([6] Is
Null,Null,", " & [6]) AS Property
FROM [mmanis-2_Crosstab];
 
Back
Top