Update query size problem?

[Akshay22]

Hi I've been tasked to run update queries in Access to populate fields for grid references. I have 3 tables of records that contain postodes and 2 empty fields called "easting" and "northing" (grid references) which are going to be populated using the update query which links to a separate database table that contains all UK postodes and their corresponding grid references. I have used the design query grid to do this but get the following error come up.

"The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB), or there is not enough temporary storage space on the disk to store the query result"

I have 8GB of RAM and 200GB free on my harddrive.

The SQL behind the query is below.



UPDATE [Copy Of Care_Trust_Sites] LEFT JOIN NSPDF_AUG_2010_UK_1M_FP ON [Copy Of Care_Trust_Sites].Postcode = NSPDF_AUG_2010_UK_1M_FP.PCDS, [Copy Of NHS_Trust_Sites] LEFT JOIN NSPDF_AUG_2010_UK_1M_FP AS NSPDF_AUG_2010_UK_1M_FP_1 ON [Copy Of NHS_Trust_Sites].Postcode = NSPDF_AUG_2010_UK_1M_FP_1.PCDS, [Copy Of PCT_Trust_Sites] LEFT JOIN NSPDF_AUG_2010_UK_1M_FP AS NSPDF_AUG_2010_UK_1M_FP_2 ON [Copy Of PCT_Trust_Sites].Postcode = NSPDF_AUG_2010_UK_1M_FP_2.PCDS SET [Copy Of Care_Trust_Sites].Easting = [NSPDF_AUG_2010_UK_1M_FP].[OSEAST1M], [Copy Of Care_Trust_Sites].Northing = [NSPDF_AUG_2010_UK_1M_FP].[OSNRTH1M], [Copy Of NHS_Trust_Sites].Easting = [NSPDF_AUG_2010_UK_1M_FP_1].[OSEAST1M], [Copy Of NHS_Trust_Sites].Northing = [NSPDF_AUG_2010_UK_1M_FP_1].[OSNRTH1M], [Copy Of PCT_Trust_Sites].Easting = [NSPDF_AUG_2010_UK_1M_FP_2].[OSEAST1M], [Copy Of PCT_Trust_Sites].Northing = [NSPDF_AUG_2010_UK_1M_FP_2].[OSNRTH1M];


Can I modify this to work without falling over? I could just do it in 3 separate update queries but wanted a simply tidy solution. I'm not an advanced Access user so I'm not very good at SQL scripting.

[June7]

Cross post http://forums.aspfree.com/microsoft-...em-476137.html