Update query size error

[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 hard drive.

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.

 

[BigRed1212]

I guess I'm confused because I'm looking the classic update syntax (which I think is only going to work in one table at a time):

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

more like

UPDATE [Copy Of Care_Trust_Sites]
set easting= (select stuff),

 

[Darrylles]

Ak,

Do this 3 times - separately - that will provide a 'tidier' and more accurate solution that your 3 LEFT joins.

For the minutes it will take you - do it.

If this is going to be a recurring task then create 3 queries, create a simple form with one button and have it run all 3 for you.

ATB

Darrylles



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.