I have a code snippet, which seems very inefficient. I am fairly new to FoxPro. As you will see in the snippet below data is read from a number of tables using joins and then a filter, selected from a list box is applied. This list box can contain thousands of brandtypes(Agbrands.siccode2), from which the user can select (this needs to be so).

When I first tried it using a straight Select SQL Statement into a table, I would get an errortelling me that the SQL statement was too complex. As you can see I now use an array and populate the temp table on record at a time.

Can anyone offer a more efficient method (by the way, it works well and some would say if its not broken, don't fix it, but I am tryingto increase my knowledge of FoxPro.

**CODE SNIPPET

FOR i = 1 TO THISForm.list3.ListCount
IF THISform.list3.Selected(i)
nNoSelected = nNoSelected + 1
ctl = chr(39) + alltrim((THISform.list3.List(i))) + " " + chr(39)
MySQL = "SELECT agbrands.brandname, agclients.clientname, alltrim(Agind.prefix) As Prefix, alltrim(agind.fname) As Firstname, alltrim(agind.initial) as Initial, alltrim(agind.sname) as Surname, "
MySql = MySql + " alltrim(agind.position) as position, agind.phone as Phone, agind.fax as Fax, agind.email as eMail, "
MySql = MySql + " agencies.agname as Agencyname, IIF(agencies.mediaco = .T.,'M','A'), "
MySql = MySql + " agencies.agadd1 as Address1, agencies.agadd2 as Address2, "
MySql = MySql + " agencies.agadd3 as Address3, agencies.agadd4 as Address4, alltrim(agencies.county) As County, alltrim(postcode) as Postcode "
MySQL = MySql + " FROM agencies "
MySQL = MySql + " INNER JOIN agind "
MySQL = MySql + " INNER JOIN indvcl "
MySQL = MySql + " INNER JOIN agbrands "
MySQL = MySql + " INNER JOIN agclients "
MySQL = MySql + " ON Agbrands.clientid = Agclients.clientid "
MySQL = MySql + " ON Agbrands.brandid = Indvcl.brandid "
MySQL = MySql + " ON Agind.agindid = Indvcl.agindid "
MySQL = MySql + " ON Agencies.agencyid = Agind.agencyid "
MyWhere = "WHERE alltrim(Agbrands.siccode2) = (" + ctl + ") GROUP BY Agind.agindid Order By agclients.clientname, agbrands.brandname into array aTemp"
lcCommand = MySql + MyWhere
&lcCommand
select temp
APPEND FROM ARRAY aTemp
ENDIF
ENDFOR


** END OF CODE SNIPPET

Thanks forany help