Update query on a query on the latest date

[dede]

1) I first do a query on one table to find the latest weighing
(ref_chien & poids) Which look fine
ref_chien poids Date_Poids ==> ref_chien poids
1 10 20.01.09 ==> 1 11
1 11 21.01.09 ==> 2 12
2 10 20.01.09
2 12 21.01.09
Below the SQL
SELECT Q.Ref_Chien, Q.poids
FROM Poids_Chien AS Q INNER JOIN Poids_Chien AS T ON Q.Ref_Chien =
T.Ref_Chien
GROUP BY Q.Ref_Chien, Q.poids, Q.Date_Poids
HAVING (((Q.Date_Poids)=(SELECT Max(T.[Date_Poids])
FROM Poids_Chien AS T
WHERE T.[Ref_Chien] = Q.[Ref_Chien])));

2) from this query I will update my Table and a error message appear !
"OPERATION must use an updateable query"
SQL:
UPDATE poids_actuel, mise_a_jour_Poids SET poids_actuel.poids =
mise_a_jour_Poids.poids;

BUT on a append query it's work !!
INSERT INTO poids_actuel ( poids, Ref_Chien )
SELECT mise_a_jour_Poids.poids, mise_a_jour_Poids.Ref_Chien
FROM mise_a_jour_Poids;
( in the table the field is a Decimal ( Precision 6, Scale 3, decimal Places
3))
Could you tell me what it's wrong ??

 

[dede]

Hi MGFoster
First let me thank you for your help,
I have tried your suggestions but I still have the same error. In the View
window it's look OK. The SQL looks like :

UPDATE poids_actuel INNER JOIN [Liste des chiens] ON poids_actuel.Ref_Chien
= [Liste des chiens].Ref_Chien SET poids_actuel.poids = (SELECT poids FROM
mise_a_jour_Poids)
WHERE ((([Liste des chiens].Ref_Chien)=[poids_actuel].[Ref_Chien]));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access (Jet) UPDATE command doesn't work the way most SQL UPDATEs
usually work. Try this:

UPDATE poids_actuel
SET poids = (SELECT poids FROM mise_a_jour_Poids)

I'm assuming that "mise_a_jour_Poids" has only one row, otherwise, you'd
need a WHERE clause, something like this:

WHERE unique_column_name = poids_actuel.unique_column_name

This would ensure the appropriate rows in each data source are linked
together.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXbfWIechKqOuFEgEQIAGwCg1nlpPa8TjaGFHRUX0bFPJ0DWgmkAnRMA
DdNhdzGRjRIjowjTzOmborVa
=639E
-----END PGP SIGNATURE-----

1) I first do a query on one table to find the latest weighing
(ref_chien & poids) Which look fine
ref_chien poids Date_Poids ==> ref_chien poids
1 10 20.01.09 ==> 1 11
1 11 21.01.09 ==> 2 12
2 10 20.01.09
2 12 21.01.09
Below the SQL
SELECT Q.Ref_Chien, Q.poids
FROM Poids_Chien AS Q INNER JOIN Poids_Chien AS T ON Q.Ref_Chien =
T.Ref_Chien
GROUP BY Q.Ref_Chien, Q.poids, Q.Date_Poids
HAVING (((Q.Date_Poids)=(SELECT Max(T.[Date_Poids])
FROM Poids_Chien AS T
WHERE T.[Ref_Chien] = Q.[Ref_Chien])));

2) from this query I will update my Table and a error message appear !
"OPERATION must use an updateable query"
SQL:
UPDATE poids_actuel, mise_a_jour_Poids SET poids_actuel.poids =
mise_a_jour_Poids.poids;

BUT on a append query it's work !!
INSERT INTO poids_actuel ( poids, Ref_Chien )
SELECT mise_a_jour_Poids.poids, mise_a_jour_Poids.Ref_Chien
FROM mise_a_jour_Poids;
( in the table the field is a Decimal ( Precision 6, Scale 3, decimal Places
3))
Could you tell me what it's wrong ??