Access 97 Correlated Updates

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
 
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same data
type as item2 in the WHERE clause.

Good luck.
 
Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


Roger said:
Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
Click to expand...
Click to expand...
 
Probably not a direct query. Chaining two queries will work.
1. Make table query
SELECT Dates.Name, Max(Dates.SubDate) AS MaxOfSubDate INTO [Dates-Max]
FROM Dates
GROUP BY Dates.Name;
2. Update query
UPDATE [Names] INNER JOIN [Dates-Max] ON Names.Name = [Dates-Max].Name SET
[Names].MaxDate = [Dates-Max].[MaxOfSubDate];

Not totally satisfactory, but will function.
--
Don Berman
Computer Sciences Corp.


Roger said:
Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


Roger said:
Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Yea, that looks like the route I am going to have to take. I am
shocked that Access does not support it.

Although its microsoft.

Thanks
Roger
Probably not a direct query. Chaining two queries will work.
1. Make table query
SELECT Dates.Name, Max(Dates.SubDate) AS MaxOfSubDate INTO [Dates-Max]
FROM Dates
GROUP BY Dates.Name;
2. Update query
UPDATE [Names] INNER JOIN [Dates-Max] ON Names.Name = [Dates-Max].Name SET
[Names].MaxDate = [Dates-Max].[MaxOfSubDate];

Not totally satisfactory, but will function.
--
Don Berman
Computer Sciences Corp.


Roger said:
Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


:

Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
One method is to use the DMAX VBA function - can be slow.

UPDATE TableB
SET Date_Ferment_Completed = DMAX("SampleDt","TableB","FieldID =" &
TableB.FieldID)

IF FieldID is a text field then you need
UPDATE TableB
SET Date_Ferment_Completed = DMAX("SampleDt","TableB","FieldID ='" &
TableB.FieldID & "'")
 
UPDATE tableB ...... from tableB GROUP BY .....

you have a set of data called tableB in one state and you are trying to
change it into another set of data based on an operation upon a subset of
that data and leave the updated set of data in a known state, when changing
that data might affect the criteria. Writing the algorithms to ensure that
those conditions will be met is a major task, and it is simpler to not allow
such operations. Nothing to do with it being Microsoft.



Roger said:
Yea, that looks like the route I am going to have to take. I am
shocked that Access does not support it.

Although its microsoft.

Thanks
Roger
Probably not a direct query. Chaining two queries will work.
1. Make table query
SELECT Dates.Name, Max(Dates.SubDate) AS MaxOfSubDate INTO [Dates-Max]
FROM Dates
GROUP BY Dates.Name;
2. Update query
UPDATE [Names] INNER JOIN [Dates-Max] ON Names.Name = [Dates-Max].Name
SET
[Names].MaxDate = [Dates-Max].[MaxOfSubDate];

Not totally satisfactory, but will function.
--
Don Berman
Computer Sciences Corp.


Roger said:
Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.

dberman wrote:
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same
data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


:

Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
This is a one time shot. I added a new field to the database which is
supposed to show when work is completed. For the sake of the old data,
I can take a best guess by looking at that max(date) of tableB. There
are about 10,000 records to update and I don't want to manually
transfer it. I will write a 2 step script to use the temporary table
but trying to simplify it to one sql query.

Thanks
Roger
you have a set of data called tableB in one state and you are trying to
change it into another set of data based on an operation upon a subset of
that data and leave the updated set of data in a known state, when changing
that data might affect the criteria. Writing the algorithms to ensure that
those conditions will be met is a major task, and it is simpler to not allow
such operations. Nothing to do with it being Microsoft.



Roger said:
Yea, that looks like the route I am going to have to take. I am
shocked that Access does not support it.

Although its microsoft.

Thanks
Roger
Probably not a direct query. Chaining two queries will work.
1. Make table query
SELECT Dates.Name, Max(Dates.SubDate) AS MaxOfSubDate INTO [Dates-Max]
FROM Dates
GROUP BY Dates.Name;
2. Update query
UPDATE [Names] INNER JOIN [Dates-Max] ON Names.Name = [Dates-Max].Name
SET
[Names].MaxDate = [Dates-Max].[MaxOfSubDate];

Not totally satisfactory, but will function.
--
Don Berman
Computer Sciences Corp.


:

Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.

dberman wrote:
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same
data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


:

Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
John,

This worked, Thanks alot

Roger
This is a one time shot. I added a new field to the database which is
supposed to show when work is completed. For the sake of the old data,
I can take a best guess by looking at that max(date) of tableB. There
are about 10,000 records to update and I don't want to manually
transfer it. I will write a 2 step script to use the temporary table
but trying to simplify it to one sql query.

Thanks
Roger
UPDATE tableB ...... from tableB GROUP BY .....
Click to expand...

you have a set of data called tableB in one state and you are trying to
change it into another set of data based on an operation upon a subset of
that data and leave the updated set of data in a known state, when changing
that data might affect the criteria. Writing the algorithms to ensure that
those conditions will be met is a major task, and it is simpler to not allow
such operations. Nothing to do with it being Microsoft.



Roger said:
Yea, that looks like the route I am going to have to take. I am
shocked that Access does not support it.

Although its microsoft.

Thanks
Roger

dberman wrote:
Probably not a direct query. Chaining two queries will work.
1. Make table query
SELECT Dates.Name, Max(Dates.SubDate) AS MaxOfSubDate INTO [Dates-Max]
FROM Dates
GROUP BY Dates.Name;
2. Update query
UPDATE [Names] INNER JOIN [Dates-Max] ON Names.Name = [Dates-Max].Name
SET
[Names].MaxDate = [Dates-Max].[MaxOfSubDate];

Not totally satisfactory, but will function.
--
Don Berman
Computer Sciences Corp.


:

Thnaks for the reply.

I am not looking for the subquery to be in the where clause.
I need it to be in the SET datecompleted = (select MAX(sampleDT) from
tableB ....

tableA
ID
datecompleted

tableB
ID
sampleDT

(TableA) (1 -> Many) (TableB) relationship

so I need Max(sampleDT) in the subquery to put into date completed.

dberman wrote:
You want something like:

UPDATE [table1] SET [item1] = whatever
WHERE [item2] in (select [item2] from table2 where itemx like "xx*");

table1 and 2 can be the same table. The select must return the same
data
type as item2 in the WHERE clause.

Good luck.
--
Don Berman
Computer Sciences Corp.


:

Does anyone know how to do something like this in Access 97?

UPDATE tableB SET
date_ferment_completed =
EXISTS (
SELECT fieldID, MAX(sampleDT)
from
tableB
GROUP BY fieldID
)


I get an "Operation must use an updatable query" error.

I tried this as well


UPDATE tableB SET
date_ferment_completed =
(
SELECT MAX(sampleDT)
from
tableB
)

The inner query gets only one value for the return.

Thanks
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

Updateable Query?????? 8
One-to-many query 4
How easy is this? 7
Selecting from a database where the joins have history 1
subqueries 3
Problem with Dual subselect 7
sql problem with updating data from a gridview 5
Append query - current record only (follow on from Ken Stafford su 2

Back
Top