Nested query - Using max value in another table to update current table (1 Viewer)

Hi,

I am trying to get the max value of MCSLabelPrintingListID from a table called MCSLabelPrintingList. This max value will then be used to update the LabelPrintingListID field in a table called MCSLabelPrintingListItem, which I am running a query on.

If I try to run this query using the following SQL statement, I get an error "You tried to execute a query that does not include the specified expression 'LabelPrintingListID' as part of an aggregate function":

UPDATE MCSLabelPrintingListItem SET MCSLabelPrintingListItem.LabelPrintingListID = Max([mcslabelprintinglist].[mcslabelprintinglistid]);

Hence, I think I need to use a nested query. Anyone got any help as to how I would write the SQL for the query? Thanks in advance.

Got it working through another forum.

For those of you who are interested, I just used the following:

UPDATE MCSLabelPrintingListItem
SET MCSLabelPrintingListItem.LabelPrintingListID =
DMax("mcslabelprintinglistid", "mcslabelprintinglist")