-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Well, your doing too much in the query. You don't need to INNER JOIN to
tbl_AAL_Items since you are referring to it in the DMax() function. Try
this:
UPDATE tbl_Audits
SET AAL_Completed = DMax("date_Actioned", "tbl_AAL_Items", "Audit_No=" &
tbl_Audits.Audit_No)
WHERE Completed <> 0
You're saying that there are 2 columns named Completed. Indicate which
tables they are in - right now it is not clear. Now your query is
saying this:
WHERE tbl_Audits.No_of_Items= tbl_Audits.Completed
AND tbl_Audits.Completed <> 0
The No_of_Items = Completed in the same table is weird 'cuz having 2
columns that hold the same data is in violation of the rules of table
Normalization.
--
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/AwUBSaZ8a4echKqOuFEgEQIvHACgkTBcpZ228YfEMS/COAKmkLeeX9QAn2AZ
Hs4snrpxcrLxv/VV4zNRm9Ac
=p4hZ
-----END PGP SIGNATURE-----
Liliane wrote:
Hi,
The [Completed] is in tbl_Audits. So how can I handle it?
My query so far is:
UPDATE tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No SET tbl_Audits.AAL_Completed =
DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & [tbl_Audits].[Audit_No])
WHERE (((tbl_Audits.Completed)<>0) AND
((tbl_Audits.No_of_Items)=[Completed]));
I still got the type conversion error, but I can't see any problem with
that. AAL_Completed and Date_Actioned are Date/Time, No_of_Items and
Completed are Numbers.
I'm hopeless...
Many thanks!!!!
:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Why don't you post your version of my second suggestion. The type
conversion error means the query was trying to put the wrong data type
in the table, or, one, or more, of the criteria (WHERE clause) has an
expression with the wrong data type (comparing apples to oranges). This
means it needs the correct delimiters; or no delimiters where there now
are delimiters; or there is a comparison of 2 different data types.
--
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/AwUBSaUs6YechKqOuFEgEQLqBgCdHgST7ZBRThjZPcoeVCQFaY5pkyYAoPkj
Nnh5PapNWiftXsifRVfjyard
=mFTY
-----END PGP SIGNATURE-----
Liliane wrote:
Hi MGFoster,
I tried both of ways.
After running your first suggestion, I got an error message saying
"Operation needs at least one updatable field".
And the second suggestion brought another error message saying "Can't update
all records due to a type conversion failure".
What do these mean? @_@
Many thanks!!!!
:
Liliane wrote:
Hi,
I have two tables: tbl_Audits and tbl_AAL_Items. Now I need to update
AAL_Completed field in tbl_Audit with latest date select from tbl_AAL_Items.
I wrote a query but it's not working.
UPDATE tbl_Audits INNER JOIN tbl_AAL_Items ON tbl_Audits.Audit_No =
tbl_AAL_Items.Audit_No
SET tbl_Audits.AAL_Completed = Max([date_Actioned])
WHERE (((tbl_Audits.No_of_Items)=[completed]) AND
((tbl_Audits.Completed)<>0));
How can I get the latest action date and update AAL_complete?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You can't use MAX() like that. Try the following (I use a derived
table: the SELECT command in parentheses) [probably won't work, but if
it does it should be the faster query]:
UPDATE tbl_Audits INNER JOIN (SELECT Audit_No, MAX(date_Actioned) As
LatestDate FROM tbl_AAL_Items GROUP BY Audit_No) As AC ON
tbl_Audits.audit_no = AC.audit_no
SET tbl_Audits.AAL_Completed = AC.LatestDate
WHERE tbl_Audits.No_of_Items = [completed] AND tbl_Audits.Completed <> 0
If that doesn't work try the Microsoft recommended solution (use a
domain aggregaate function), which may take a while to run, depending on
the size of the tables.
UPDATE tbl_Audits
SET AAL_Completed = DMax("date_Actioned", "tbl_AAL_Items", "Audit_No=" &
tbl_Audits.Audit_No)
WHERE No_of_Items = [completed] AND Completed <> 0
This WHERE clause is screwy 'cuz [completed] needs to be referenced - is
it a column in tbl_AAL_Items or a user-defined parameter or are you
referring to the column in tbl_Audits? If it is a user-defined
parameter you need to name it something different and use the PARAMETERS
clause. Like this:
PARAMETERS completed_items INTEGER;
UPDATE...
SET ... etc. ...
WHERE No_of_Items = completed_items AND Completed <> 0
If it is in tbl_AAL_Items then the expression needs to go in the DMax()
function's criteria parameter. Like this:
"Audit_No=" & tbl_Audits.Audit_No & " AND [completed] = " &
tbl_Audits.No_of_Items)
and removed from the main query's WHERE clause.
WHERE Completed <> 0
--
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/AwUBSaTBdoechKqOuFEgEQLe8QCfRuunoN9of97j6X8eyVl5txWP0kMAn2hq
lDwisEzkATSM0x3nIU97imRC
=7nNa
-----END PGP SIGNATURE-----
