I have some SQL here. It is generated by a VBa script but that is bye-the-bye what I need to do is get rid of at least one of the DLookup calls in the SQL because even with a small data sample this takes forever to run.
UPDATE type INNER JOIN (select * from Mat_data in 'C:\sb-Data\sb\In Tray\New_18_1_2005.mdb') T1 ON type.SBKEY = T1.type_SBKEY SET Type.Name = [T1].[Type_name], Type.[Measure ID] = DLookup('[Measure ID]', '[Measure]', "'[Measure].[SBKey] = " & [T1].[Measure_SBKey] & "'"), Type.Wm_Ratio = dlookup("[id]", "[converter]","'[converter].[sbkey] = " & [t1].converter_sbkey & "'");
Once I've tidied up the SQL I can re-write the script to generate SQL that is like our new sample but first I need a new sample...
I hope that makes sense. It boils down to needing to make this SQL faster (it will run on Access 2000 for now and maybe MySQL later but it must first work in Access).