Help!! (1 Viewer)

[vvasudev]

I have 2 tables and i want to run an Update statement in ACCESS:


........CODE...............................

UPDATE FINAL_TEST
SET FINAL_TEST.Sales_Jan =

(
SELECT Temp.Sales
FROM Temp
WHERE Temp.RowID = FINAL_TEST.RowID
),

FINAL_TEST.Units_Jan =
(
SELECT Temp.Units
FROM Temp
WHERE Temp.RowID = FINAL_TEST.RowID)


WHERE FINAL_TEST.RowID in

(
SELECT Temp.RowID
FROM Temp
)


............CODE..............................

Error Message: Operation Must Use an Updatable Query..Please Advice

thank you

 

[MarkK]

In your sub-query FINAL_TEST.RowID has no meaning because the parent query does not return rows.

 

[vvasudev]

So please can u modify..My problem is as follows

I have 2 tables FINAL_TEST and TEMP
FINAL_TEST has 12 fields
TEMP has 2 Fields

FINAL _TEST has 32 Records
TEMP has also 32 Records

I want to update the 2 fields of TEMP to the Column 2 and 3 of FINAL_TEST

 

[Galaxiom]

UPDATE FINAL_TEST
SET FINAL_TEST.Sales_Jan = (Select Temp.Sales from Temp where Temp.RowID = FINAL_TEST.RowID),

FINAL_TEST.Units_Jan = (Select Temp.Units from Temp where Temp.RowID = FINAL_TEST.RowID)

Please post code formatted and in a code box.
More like this:

UPDATE FinalTest
SET FinalTest.Sales_Jan = 
      (SELECT Temp.Sales
       FROM Temp
       WHERE Temp.RowID = FinalTest.RowID),
 
       FinalTest.Units_Jan =
      (SELECT Temp.Units
       FROM Temp 
       WHERE Temp.RowID = FinalTest.RowID)

I haven't attempted to understand the question or even the right syntax. It is just the same text but it is much easier to read especially when you are hiding small letter "where" inside capitalised field and table names.
Someone will correct it no doubt.

IMHO The best strategy for naming is the capitialisation of names
like you did with RowID, so FinalTest. The underscore is a pain to type anyway.

 

[vvasudev]

Thank u for ur suggestions, i have done a bit of editing


BR,
Vineeth

 

[MarkK]

A date is what a thing HAS not what a thing IS. Put another way--a date is data not structure. If you have a field named Sales_Jan then you very likely have a data design problem that will keep causeing you headaches until you get it sorted out.
In respect to the specific problem you've posted, it appears that you simply copy data A from table B to table C. I would try to get the process that produces data A to write it directly to table C and eliminate the step you are having trouble with.
Cheers,