Update Table Field from select Query

[ECEK]

I've never been able to get my head around this so here's hoping somone can explain it to me...very......very slowly !!

My Table1 has a unique ID
It has a NumberField

My SelectQuery (based on Table1 and additional tables) calculates a new Number.

What I want to do is update the whole of Table1 with my Newcalc.

I can do it as a select query (basically replacing the [Numberfield] with [NewCalc] but I want to run this update when I open the database.

I have tried creating an Update Query and selecting [NumberField] Update to..[NewCalc].

Im clearly missing the principle here.

any help is muchly appreciated.

 

[Minty]

What was the SQL of your Update query that failed ? The syntax should be something like

UPDATE MyTable
SET FieldtoUpdate = NewValue, SecondField = NewValue2
WHERE Criteria goes here

 

[ECEK]

I wasn't doing it as SQL. Is SQL the only way to do this?

 

[ECEK]

Seeing as it's you Minty I can reference what you have just helped me with.
I want to update all of my dates in my Table with a new date that I have formulated in a select query. (NewCalc)

 

[Minty]

In your query design right click and select SQL view. That will show you what the query designer is actually doing in a SQL query.

If you post up the select query that gives you the result you want we can probably easily adapt it.

 

[ECEK]

This is my select query (there are additional fields that I have not included for brevity)
I want to replace MOEB with NewCalc

SELECT MyTable.ID, MyTable.MOEB, UPD01.NewCalc, MyTable.consultant, MyTable.administrator, MyTable.source_campaign
FROM MyTable INNER JOIN UPD01 ON MyTable.ID = UPD01.ID;

 

[ECEK]

Done it Hurrah for me !!!!!

UPDATE MyTable INNER JOIN UPD01 ON MyTable.ID = UPD01.ID SET MyTable.MOEB = [UPD01].[NewCalc];

Thanks for pointing the way Minty.

 

[Minty]

Glad you sussed it out - I always think a pointer is way more valuable than a copy and paste of code that you might not "get"