I have a table named Quotation that contains QuoteNo, Heading, Note, Delivery
Sometimes when a new quote is generated, it is useful to clone some of the details from an existing quote. So, for example, I might start a new quote with a QuoteNo of 1000, but would like to copy over the Heading and Note from QuoteNo 800. So I need the ability to copy data from within the same table.
I have tried writing an update query with a nested subquery, but keep getting a syntax error. Here is what I have:
Code:
UPDATE Quotation
SET
Heading = CloneData.Heading,
Note = CloneData.Note
FROM
(
SELECT
Heading,
Note,
FROM Quotation
WHERE QuoteNo = variable_CloneQuoteNo
) CloneData
WHERE
QuoteNo = variable_NewQuoteNo;
The variables are set when I start a new quote and when I select a quote to clone. From what I can tell, this should be valid in SQL Server, but apparently not in Access. Any ideas of how to accomplish this in Access? I am using 2010.
Thanks!