Updateable Query??????

P

Paul W Smith

I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which to
update the table - plenty of examples on this!

However I want to understand how to use the result of one query to update a
table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B) for
each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
 
J

John Spencer

It's a fact that you cannot use any aggregate query in an update query (with
the rare exception of using it in the WHERE clause). You have to use the
VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")
 
P

Paul W Smith

Understood.

Now if I add an additional column C onto each Table. How Do I amend your
query below to update C with the correct corresponding field value from
Table B i.e. the one with the Max(B).

PWS


John Spencer said:
It's a fact that you cannot use any aggregate query in an update query
(with the rare exception of using it in the WHERE clause). You have to
use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



Paul W Smith said:
I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which to
update the table - plenty of examples on this!

However I want to understand how to use the result of one query to update
a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B) for
each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
 
J

John Spencer

Not quite sure what you want here. What I understand from your post is that
you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third Argument>

So in the case above you end up with the equivalent of a join clause where
you are joining a table a to table b on the values in field A (only you are
doing it one at a time).


Paul W Smith said:
Understood.

Now if I add an additional column C onto each Table. How Do I amend your
query below to update C with the correct corresponding field value from
Table B i.e. the one with the Max(B).

PWS


John Spencer said:
It's a fact that you cannot use any aggregate query in an update query
(with the rare exception of using it in the WHERE clause). You have to
use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



Paul W Smith said:
I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which to
update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B)
for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 
P

Paul W Smith

No what I want to do is update TableA.C to be whatever TableB.C is where
Max(TableB.B)



John Spencer said:
Not quite sure what you want here. What I understand from your post is
that you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third Argument>

So in the case above you end up with the equivalent of a join clause where
you are joining a table a to table b on the values in field A (only you
are doing it one at a time).


Paul W Smith said:
Understood.

Now if I add an additional column C onto each Table. How Do I amend your
query below to update C with the correct corresponding field value from
Table B i.e. the one with the Max(B).

PWS


John Spencer said:
It's a fact that you cannot use any aggregate query in an update query
(with the rare exception of using it in the WHERE clause). You have to
use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which to
update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B)
for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 
P

Paul W Smith

In case my last update is not clear I want to update the Record (all fields)
in Table A with the record (all fields) where DMax("B","TableB","A=""" &
TableA.A & """").

I have treid using DlookUP but cannot seem to get the syntax right!

PWS



Paul W Smith said:
No what I want to do is update TableA.C to be whatever TableB.C is where
Max(TableB.B)



John Spencer said:
Not quite sure what you want here. What I understand from your post is
that you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third
Argument>

So in the case above you end up with the equivalent of a join clause
where you are joining a table a to table b on the values in field A (only
you are doing it one at a time).


Paul W Smith said:
Understood.

Now if I add an additional column C onto each Table. How Do I amend
your query below to update C with the correct corresponding field value
from Table B i.e. the one with the Max(B).

PWS


It's a fact that you cannot use any aggregate query in an update query
(with the rare exception of using it in the WHERE clause). You have to
use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which
to update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B)
for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY
TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

PERHAPS

DLookup("C","TableB","A=""" & TableA.A &""" AND B=""" &
DMax("B","TableB","A=""" & TableA.A & """")
& """")

Watch out for line wrapping, that should all be one line

Paul W Smith said:
In case my last update is not clear I want to update the Record (all
fields) in Table A with the record (all fields) where
DMax("B","TableB","A=""" & TableA.A & """").

I have treid using DlookUP but cannot seem to get the syntax right!

PWS



Paul W Smith said:
No what I want to do is update TableA.C to be whatever TableB.C is where
Max(TableB.B)



John Spencer said:
Not quite sure what you want here. What I understand from your post is
that you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third
Argument>

So in the case above you end up with the equivalent of a join clause
where you are joining a table a to table b on the values in field A
(only you are doing it one at a time).


Understood.

Now if I add an additional column C onto each Table. How Do I amend
your query below to update C with the correct corresponding field value
from Table B i.e. the one with the Max(B).

PWS


It's a fact that you cannot use any aggregate query in an update query
(with the rare exception of using it in the WHERE clause). You have
to use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which
to update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A =
TableB.A;

This works fine and updates the value in TableB over the old value in
TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the max(B)
for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY
TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B =
[Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 
P

Paul W Smith

What do the triple and quadruple double quotes mean?

PWS


John Spencer said:
PERHAPS

DLookup("C","TableB","A=""" & TableA.A &""" AND B=""" &
DMax("B","TableB","A=""" & TableA.A & """")
& """")

Watch out for line wrapping, that should all be one line

Paul W Smith said:
In case my last update is not clear I want to update the Record (all
fields) in Table A with the record (all fields) where
DMax("B","TableB","A=""" & TableA.A & """").

I have treid using DlookUP but cannot seem to get the syntax right!

PWS



Paul W Smith said:
No what I want to do is update TableA.C to be whatever TableB.C is where
Max(TableB.B)



Not quite sure what you want here. What I understand from your post is
that you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third
Argument>

So in the case above you end up with the equivalent of a join clause
where you are joining a table a to table b on the values in field A
(only you are doing it one at a time).


Understood.

Now if I add an additional column C onto each Table. How Do I amend
your query below to update C with the correct corresponding field
value from Table B i.e. the one with the Max(B).

PWS


It's a fact that you cannot use any aggregate query in an update
query (with the rare exception of using it in the WHERE clause). You
have to use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with which
to update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A
= TableB.A;

This works fine and updates the value in TableB over the old value
in TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the
max(B) for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY
TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B
= [Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

To get a quote mark into a string, you have to double it up. So """" ends
up as " in the final string

You can do this more clearly using Chr(34) - which is a double quote.

For instance,

DMax("B","TableB","A=" & Chr(34) & TableA.A & Chr(34))

IF Field A is Number field then you drop the Chr(34)
If Field A is a Date field then you add "#" in place of the Chr(34)


Paul W Smith said:
What do the triple and quadruple double quotes mean?

PWS


John Spencer said:
PERHAPS

DLookup("C","TableB","A=""" & TableA.A &""" AND B=""" &
DMax("B","TableB","A=""" & TableA.A & """")
& """")

Watch out for line wrapping, that should all be one line

Paul W Smith said:
In case my last update is not clear I want to update the Record (all
fields) in Table A with the record (all fields) where
DMax("B","TableB","A=""" & TableA.A & """").

I have treid using DlookUP but cannot seem to get the syntax right!

PWS



No what I want to do is update TableA.C to be whatever TableB.C is
where Max(TableB.B)



Not quite sure what you want here. What I understand from your post
is that you also want to set Column C equal to the Max of column B.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """"),
TableA.C = DMax("B","TableB","A=""" & TableA.A & """")

What the DMax statement does is basically build a query
SELECT Max(<first argument>) FROM <Second argument> WHERE <Third
Argument>

So in the case above you end up with the equivalent of a join clause
where you are joining a table a to table b on the values in field A
(only you are doing it one at a time).


Understood.

Now if I add an additional column C onto each Table. How Do I amend
your query below to update C with the correct corresponding field
value from Table B i.e. the one with the Max(B).

PWS


It's a fact that you cannot use any aggregate query in an update
query (with the rare exception of using it in the WHERE clause).
You have to use the VBA functions DMax etc.

UPDATE TableA
SET TableA.B = DMax("B","TableB","A=""" & TableA.A & """")



I am trying to understand how update queries work.

It is so simple when you are just typing in the new value with
which to update the table - plenty of examples on this!

However I want to understand how to use the result of one query to
update a table.

I have created two simple tables:

TableA
A B
Z 1
Y 2
X 3

TableB
A B
Z 11
Y 22
X 33

SELECT TableA.B, TableB.B FROM TableA INNER JOIN TableB ON TableA.A
= TableB.A;

This works fine and updates the value in TableB over the old value
in TableA.

Now I add some additional values to TableB

TableB
A B
Z 11
Y 22
X 33
Z 44
Y 55
X 66

I then produce a query (saved as Query2) that returns just the
max(B) for each A

SELECT TableB.A, Max(TableB.B) AS MaxOfB FROM TableB GROUP BY
TableB.A;

Now I try to Update TableA using this:

UPDATE TableA INNER JOIN Query2 ON TableA.A = Query2.A SET TableA.B
= [Query2]![MaxOfB];

To me this should work, but I get a dialog box which says:

"Operation must use an updateable query."

What is the problem?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update TableA with data from TableB using Update Query 3
Subselect Query Problem 4
The complement of a select query 5
Delete Query with Joins problem 2
How easy is this? 7
Query not returning all records from a linked Excel table 1
Need help in MS Access Query 1
Update SQL with Joinning a Read only view 2

Top