By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Update Column with Content of other table

P: n/a
Hi,
got:
[T1]![Nr]
[T2]![TT]

How to overwrite (update) all elements of [T2]![TT] with Max (or
latest) of [T1]![Nr] ?
I tried if with a Update-Querry - without success.

Thankx

Mar 13 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Mar 13, 5:43 am, "tone" <anton.sutterlu...@pantec.comwrote:
Hi,
got:
[T1]![Nr]
[T2]![TT]

How to overwrite (update) all elements of [T2]![TT] with Max (or
latest) of [T1]![Nr] ?
I tried if with a Update-Querry - without success.

Thankx

Any query using a calculation is automatically not updateable. You
need to use two queries - one to get the desired info, the second to
update based on the first.

Mar 13 '07 #2

P: n/a
Yeap, now I'm using to queries:
GetMax and UpdateT2 (which uses GetMax):
UPDATE T2, GetMax SET T2.TT = [GetMax]!MaxvonNr;

As soon I open UpdateT2 it says that the operation has to us an
updateable query!
Help!


Mar 13 '07 #3

P: n/a
I think you will need to change your summation query into an append or make
table query, then use that temp table to update your original table.

"tone" <an***************@pantec.comwrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...
Yeap, now I'm using to queries:
GetMax and UpdateT2 (which uses GetMax):
UPDATE T2, GetMax SET T2.TT = [GetMax]!MaxvonNr;

As soon I open UpdateT2 it says that the operation has to us an
updateable query!
Help!


Mar 13 '07 #4

P: n/a
He, kind a simple work-around.
Thanx so long, Ron

Mar 13 '07 #5

P: n/a

I apologize, I thought it would work.
An alternative to using a maketable would be to use a function in an
update (I have tried it and it works for my limited example)...

Query:
UPDATE [TABLENAME] SET [TABLENAME].[FIELDNAME]= MaxOfValue();

Add something like this to a module. Modify to use appropriate table
and field names:

Function MaxOfValue() As Variant
MaxOfValue = DMax("[FIELDNAME]", "[TABLENAME]")
End Function

Sorry again.

Mar 13 '07 #6

P: n/a
Thanks storrboy, you guys's great
This also works
Mar 13 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.