I'm using Access 2003 on Win XP. I have a database with two tables (important fields are listed below the table name):

---------- MainData ----------
Complex - empty value to be filled from other table
Well - ID number to be matched with other table
TopValue

---------- LUdata ----------
Complex_Tops - values like 2016. 1432, etc.
Complex - lable to be moved to other table: TC-I, TC-II, TC-III, TC-IV
Well - ID number matched with other table

I need to update the label column (its called Complex) in the MainData table (right now its empty). This needs to be done selectively based on values stored in the LUdata table. Ive tried using a SELECT statement coupled with WHERE EXISTS, but I cannot get it working.

This SQL statement returns this recordset:

SELECT MainData.Complex, MainData.Well, LUdata.Well, MainData.TopValue, LUdata.Complex_Tops, LUdata.Complex
FROM LUdata INNER JOIN MainData ON LUdata.Well = MainData.Well
WHERE( MainData.Well = LUdata.Well ) AND ( MainData.TopValue <= LUdata.Complex_Tops )

<null> BV-103 BV-103 1980.9 2042 TC-IV
<null> BV-105 BV-105 1667 2046 TC-III
<null> BV-105 BV-105 2010 2046 TC-IV
<null> BV-107 BV-107 1717 1890 TC-I
<null> BV-107 BV-107 1825.5 1890 TC-II

=============================================

What I want to do is move the TC-IV value into the column with the null value, and repeat this on each row. This doesnt work at all, but it shows the direction I was thinking.

----- Code -----
UPDATE MainData
SET MainData.Complex = (SELECT MainData.Complex, MainData.Well, LUdata.Well, MainData.TopValue, LUdata.Complex_Tops, LUdata.Complex
FROM LUdata INNER JOIN MainData ON LUdata.Well = MainData.Well
WHERE( MainData.Well = LUdata.Well ) AND ( MainData.TopValue <= LUdata.Complex_Tops ) )
WHERE EXISTS (SELECT MainData.Complex, MainData.Well, LUdata.Well, MainData.TopValue, LUdata.Complex_Tops, LUdata.Complex
FROM LUdata INNER JOIN MainData ON LUdata.Well = MainData.Well
WHERE( MainData.Well = LUdata.Well ) AND ( MainData.TopValue <= LUdata.Complex_Tops ) )


This is the article that gave me the idea to try it this way: http://www.techonthenet.com/sql/update.php

Any ideas and help will be greatly appreciated!