update with another query or with a join

N

nadilieHLHL

I have this query thatès working fine:
SELECT Max(GI_ACTENREG_TB.DHREACT) AS MaxDeDHREACT, GI_ACTENREG_TB.DOSSIER
FROM [T-Gestion des usagers] INNER JOIN (GI_ACTENREG_TB INNER JOIN
GI_ACTIVITE_TB ON (GI_ACTENREG_TB.ACTCODE_OFFICIEL = GI_ACTIVITE_TB.ACTCODE)
AND (GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE)) ON
[T-Gestion des usagers].DOSIIERPATIENT = GI_ACTENREG_TB.DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND ((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"));

And I need to have the Max(GI_ACTENREG_TB.DHREACT) it's a date field to be
update in an another database.
UPDATE [T-TestChargementnomPrenom] INNER JOIN [R-Date dernier r-v
psychiatre] ON [T-TestChargementnomPrenom].DOSSIER = [R-Date dernier r-v
psychiatre].DOSSIER SET [T-TestChargementnomPrenom].dateHDernierRvMed =
"MaxDeDHREACT";

But I tried to save my first query and do an update query with but it says:
Operation must use an updateable query.

And then I tried this in SQL:
UPDATE S1 SET S1.dateHDernierRvMed = S2.DATEDERNIERRV
FROM [T-TestChargementnomPrenom] S1
INNER JOIN (
SELECT Max(GI_ACTENREG_TB.DHREACT) DATEDERNIERRV , GI_ACTENREG_TB.DOSSIER
FROM (
GI_ACTENREG_TB INNER JOIN GI_ACTIVITE_TB ON
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE) AND
(GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.ACTCODE_OFFICIEL = GI_ACTIVITE_TB.ACTCODE)) INNER JOIN
[T-TestChargementnomPrenom] ON GI_ACTENREG_TB.DOSSIER =
[T-TestChargementnomPrenom].DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND ((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"))) as S2
ON S1.DOSSIER = S2.DOSSIER

But it says syntax error (missing operator) in query expression...
and it highlight the very first FROM...

Any idea before I get crazy?
 
M

Michel Walsh

Just to illustrate a possible way to do it, I will use a simpler queries:

given query1:

SELECT MAX(f1) as mf1, f2 FROM table1 GROUP BY f2


You want something equivalent to:

UPDATE table2 INNER JOIN query1 ON table2.f3=query1.f2
SET table2.f4=query1.mf1


It does not work, as it is, because the query query1 is not updateable. But
then try:


UPDATE table2 INNER JOIN table1 ON table2.f3=table1.f2
SET table2.f4=table1.f1
WHERE table1.f1= (SELECT MAX(f1) FROM table1 AS a WHERE a.f2=table1.f2)



or, if you prefer a simpler look (may be faster, or slower, it is up to you
to test it):

UPDATE table2
SET table2.f4=DMax("f1", "table1", "f2=" & f2 )


(assuming f2 is an integer; if it is a string, use:

... = DMax("f1", "table1", "f2='" & f2 & "'" )





Hoping it may help,
Vanderghast, Access MVP






nadilieHLHL said:
I have this query thatès working fine:
SELECT Max(GI_ACTENREG_TB.DHREACT) AS MaxDeDHREACT, GI_ACTENREG_TB.DOSSIER
FROM [T-Gestion des usagers] INNER JOIN (GI_ACTENREG_TB INNER JOIN
GI_ACTIVITE_TB ON (GI_ACTENREG_TB.ACTCODE_OFFICIEL =
GI_ACTIVITE_TB.ACTCODE)
AND (GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE)) ON
[T-Gestion des usagers].DOSIIERPATIENT = GI_ACTENREG_TB.DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND
((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"));

And I need to have the Max(GI_ACTENREG_TB.DHREACT) it's a date field to be
update in an another database.
UPDATE [T-TestChargementnomPrenom] INNER JOIN [R-Date dernier r-v
psychiatre] ON [T-TestChargementnomPrenom].DOSSIER = [R-Date dernier r-v
psychiatre].DOSSIER SET [T-TestChargementnomPrenom].dateHDernierRvMed =
"MaxDeDHREACT";

But I tried to save my first query and do an update query with but it
says:
Operation must use an updateable query.

And then I tried this in SQL:
UPDATE S1 SET S1.dateHDernierRvMed = S2.DATEDERNIERRV
FROM [T-TestChargementnomPrenom] S1
INNER JOIN (
SELECT Max(GI_ACTENREG_TB.DHREACT) DATEDERNIERRV , GI_ACTENREG_TB.DOSSIER
FROM (
GI_ACTENREG_TB INNER JOIN GI_ACTIVITE_TB ON
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE) AND
(GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.ACTCODE_OFFICIEL = GI_ACTIVITE_TB.ACTCODE)) INNER JOIN
[T-TestChargementnomPrenom] ON GI_ACTENREG_TB.DOSSIER =
[T-TestChargementnomPrenom].DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND
((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"))) as S2
ON S1.DOSSIER = S2.DOSSIER

But it says syntax error (missing operator) in query expression...
and it highlight the very first FROM...

Any idea before I get crazy?
Click to expand...
 
N

nadilieHLHL

thanks for your help I found my answer there:
view this Kb
-----------------
ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc

It finally works with that!
Thanks God

Michel Walsh said:
Just to illustrate a possible way to do it, I will use a simpler queries:

given query1:

SELECT MAX(f1) as mf1, f2 FROM table1 GROUP BY f2


You want something equivalent to:

UPDATE table2 INNER JOIN query1 ON table2.f3=query1.f2
SET table2.f4=query1.mf1


It does not work, as it is, because the query query1 is not updateable. But
then try:


UPDATE table2 INNER JOIN table1 ON table2.f3=table1.f2
SET table2.f4=table1.f1
WHERE table1.f1= (SELECT MAX(f1) FROM table1 AS a WHERE a.f2=table1.f2)



or, if you prefer a simpler look (may be faster, or slower, it is up to you
to test it):

UPDATE table2
SET table2.f4=DMax("f1", "table1", "f2=" & f2 )


(assuming f2 is an integer; if it is a string, use:

... = DMax("f1", "table1", "f2='" & f2 & "'" )





Hoping it may help,
Vanderghast, Access MVP






nadilieHLHL said:
I have this query thatès working fine:
SELECT Max(GI_ACTENREG_TB.DHREACT) AS MaxDeDHREACT, GI_ACTENREG_TB.DOSSIER
FROM [T-Gestion des usagers] INNER JOIN (GI_ACTENREG_TB INNER JOIN
GI_ACTIVITE_TB ON (GI_ACTENREG_TB.ACTCODE_OFFICIEL =
GI_ACTIVITE_TB.ACTCODE)
AND (GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE)) ON
[T-Gestion des usagers].DOSIIERPATIENT = GI_ACTENREG_TB.DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND
((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"));

And I need to have the Max(GI_ACTENREG_TB.DHREACT) it's a date field to be
update in an another database.
UPDATE [T-TestChargementnomPrenom] INNER JOIN [R-Date dernier r-v
psychiatre] ON [T-TestChargementnomPrenom].DOSSIER = [R-Date dernier r-v
psychiatre].DOSSIER SET [T-TestChargementnomPrenom].dateHDernierRvMed =
"MaxDeDHREACT";

But I tried to save my first query and do an update query with but it
says:
Operation must use an updateable query.

And then I tried this in SQL:
UPDATE S1 SET S1.dateHDernierRvMed = S2.DATEDERNIERRV
FROM [T-TestChargementnomPrenom] S1
INNER JOIN (
SELECT Max(GI_ACTENREG_TB.DHREACT) DATEDERNIERRV , GI_ACTENREG_TB.DOSSIER
FROM (
GI_ACTENREG_TB INNER JOIN GI_ACTIVITE_TB ON
(GI_ACTENREG_TB.CENACTIVCODE_FAIT = GI_ACTIVITE_TB.CENACTIVCODE) AND
(GI_ACTENREG_TB.UNITEADMCODE_FAIT = GI_ACTIVITE_TB.UNITEADMCODE) AND
(GI_ACTENREG_TB.ACTCODE_OFFICIEL = GI_ACTIVITE_TB.ACTCODE)) INNER JOIN
[T-TestChargementnomPrenom] ON GI_ACTENREG_TB.DOSSIER =
[T-TestChargementnomPrenom].DOSSIER
WHERE (((GI_ACTIVITE_TB.TYPEACT_MAISON_CODE)="D") AND
((GI_ACTENREG_TB.DHREACT) Between #4/1/2006# And #3/31/2007#) AND
((GI_ACTENREG_TB.INDICFAIT)<>0) AND
((GI_ACTENREG_TB.CENACTIVCODE_FAIT)=6332))
GROUP BY GI_ACTENREG_TB.DOSSIER
HAVING (((GI_ACTENREG_TB.DOSSIER)<"4000000"))) as S2
ON S1.DOSSIER = S2.DOSSIER

But it says syntax error (missing operator) in query expression...
and it highlight the very first FROM...

Any idea before I get crazy?
Click to expand...
Click to expand...
 
Top