Access 97 Correlated Updates

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.
--
Don Berman
Computer Sciences Corp.


"Roger" wrote:

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

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.


"Roger" wrote:

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

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" wrote:

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.


"Roger" wrote:

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

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.


"Roger" wrote:

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.


"Roger" wrote:

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

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 & "'")

"Roger" wrote in message
oups.com...
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

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" wrote in message
ups.com...
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.


"Roger" wrote:

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.


"Roger" wrote:

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

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

David F Cox wrote:
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" wrote in message
ups.com...
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.


"Roger" wrote:

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.


"Roger" wrote:

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

John,

This worked, Thanks alot

Roger

Roger wrote:
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

David F Cox wrote:
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" wrote in message
ups.com...
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.


"Roger" wrote:

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.


"Roger" wrote:

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