SQL for MS Access--updating one table with information from another

[theweatherman]

I am working on a project and could use some SQL assistance. The project is an export from a database that allows user-defined fields. Information from user-defined fields is stored in a single table with the data in one column and the field name in another. I am working to export data from this and other tables as a single table. The export table will have already been created and have some partial data in it including a key that can be used to link to the user-defined field table.

My plan for getting a single user-defined field into a distinct column of the export table is as follows:

Update ExportTable.Description rows with data from rows in UDFields.UDData where UDFields.UDName='Description' (the name of the user-defined field) and
ExportTable.ItemKey=UDFields.ItemKey

I plan to run this separately for each user-defined field name (there are only about 10 that are needed). Here is one of the things I have tried:

UPDATE ExportTable
SET ExportTable.Description = UDFields.UDData
FROM ExportTable, UDFields
WHERE ExportTable.ItemKey=UDFields.Item_Key
AND UDFields.UDName='Description';

This yields an error message: "Syntax error (missing operator) in query expression 'UDFields.UDData FROM ExportTable"

Can any of you help me to determine either where the syntax is incorrect, or if the logic behind my query will work? I am fairly new to Update statements and do not know exactly how they work (how they handle arguments), beyond basic syntax rules.

Many thanks in advance!

WM

[KnooKie]

Here's a typical UPDATE statement from Access. Notice there is no FROM clause and the tables use the INNER JOIN statement to link the tables together.....

UPDATE FMG_Dealer_Qaire INNER JOIN FMG_in_UID ON (FMG_Dealer_Qaire.fi_fmgCode = FMG_in_UID.fl_fmgCode) AND (FMG_Dealer_Qaire.fs_UID = FMG_in_UID.fs_UID) SET FMG_Dealer_Qaire.fl_contactCode = 1234
WHERE (((FMG_Dealer_Qaire.fs_UID)="lat2003"));

[theweatherman]

Thank you very much for your reply. Since posting, I moved to PowerBuilder and completed the project there.

Originally posted by KnooKie
Here's a typical UPDATE statement from Access. Notice there is no FROM clause and the tables use the INNER JOIN statement to link the tables together.....

UPDATE FMG_Dealer_Qaire INNER JOIN FMG_in_UID ON (FMG_Dealer_Qaire.fi_fmgCode = FMG_in_UID.fl_fmgCode) AND (FMG_Dealer_Qaire.fs_UID = FMG_in_UID.fs_UID) SET FMG_Dealer_Qaire.fl_contactCode = 1234
WHERE (((FMG_Dealer_Qaire.fs_UID)="lat2003"));