concatenating records

  • Thread starter Thread starter macroapa
  • Start date Start date
M

macroapa

Hi, I have got the concatenate code form Duane Hookom.

My table is structured as follows:

Policy_No ID
123456 1000
234567 1000
234456 2000

In all I have about 30000 records.

I have got the code to work (on a small set of data), but on the 30000
the SQL takes forever to execute. Is there a more efficient way to
write it. I wold like the output (of the above example) to be:

ID Policies
1000 123456, 234567
2000 234456


My SQL is:

SELECT Roleextract.id, Concatenate("SELECT Policy_no FROM roleextract
WHERE id =" & [id]) AS Policies
FROM Roleextract
GROUP BY Roleextract.id, Concatenate("SELECT Policy_no FROM
roleextract WHERE id =" & [id]);

Thanks.
 
It may be quicker if you use a Distinct subquery to limit the processing.

SELECT X.id
, Concatenate("SELECT Policy_no FROM roleextract WHERE id =" & [id]) AS Policies
FROM (
SELECT DISTINCT RoleExtract.ID
FROM RoleExtract
) as X

That should decrease the number of times the Concatenate function is called by
a significant amount and therefore increase the speed.

Please post back with the change in speed - if any.

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