Update Query 2

[Smack]

I need to run an update query which will update:
GSS0041 to GSS0041A
This will be for all records. I also need to update the price field X 1000. So .4982 would change to 498.20
I tried it the night and wiped out the whole price field - thank goodness for tape back-up!
Thanks y'all,
-Smack X-)
Access=Humility

 

[tlbroadbent]

These UPDATE queries should work.

Update table_name Set column_name = column_name & "A"
Where column_name="GSS0041"

Update table_name Set price=price*1000 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[nickjar2]

I haven't got access open and i am not very good with sql but isn't something like:

update TABLE set field = [field] & "A" and FIELD2 = [FIELD2]*1000;

Nick

 

[nickjar2]

Disregard what i put. we must have posted at the same time and my sql is wrong.

Sorry.

 

[Smack]

To make sure I'm on the right track, this would be done in SQL or in the criteria / update fields? Also, GSS0041 is not the only record to update, I have about twelve different three letter prefixes, gss, gbg, gtb etc. followed by four digits, all will need an A added to the end. Sorry for the confusion.
-Smack

 

[tlbroadbent]

Smack,

You could open the SQL view and create the query like thos posted here. Or you can open the grid view to create the query. The where clause is the criteria. The Set clause is the update to field.

If all records are going to be updated then NickJar2 is pretty close to correct.

Update TableA
Set field1=field1 & "A", Price=Price*1000;

You only need the criteria or Where clause to restrict which records are updated. If you have no restrictions then then the query above is what you need. It will update all records.

In the query design grid this query would look sonmething like the following.

Field: field1 field2
Table: TableA TableA
Update to: field1 & "A" field2 * 1000
Criteria: Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Smack]

Thanks Terry, that worked great. But now of course that this was worked out "they" have added a new twist. My form is made from two tables, one is the subtable for the subform. The subform records may or may not have multiple entries and they want the update to only affect the most recent entry (by date). Only thing about this is that none of the records will have common dates so I can't simply say update all records after X date. Suggestions?
-Smack X-)

 

[tlbroadbent]

I'm unclear which table has the date and which table is being updated. Perhaps you could clarify because I don't know wwhether to recommend a JOIN or a correlated subquery or some combination. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Smack]

The subtable has the date of entry. Both tables have info requiring updates. Help I had received on <> will allow me to filter certain records which will not be updated. Hope this helps.
-Smack

 

[Smack]

Any suggestions?