Update with Max date

L

Liliane

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?

Please help me.

thanks a lot!!!
 
M

MGFoster

Liliane said:
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-----
 
L

Liliane

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!!!!


MGFoster said:
Liliane said:
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-----
 
M

MGFoster

-----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-----

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!!!!


MGFoster said:
Liliane said:
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-----
 
L

Liliane

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!!!!




MGFoster said:
-----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-----

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!!!!


MGFoster said:
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-----
 
M

MGFoster

-----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-----

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!!!!




MGFoster said:
-----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-----

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-----
 
L

Liliane

Hi MGFoster,

Thank you for all your helps. But the query is still not working, the type
convension error is still there. My query is now like:

UPDATE tbl_Audits
SET tbl_Audits.AAL_Completed =
DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & tbl_Audits.Audit_No)
WHERE (((tbl_Audits.Completed)<>0));

In the tbl_Audits, basically No_of_Items and Completed are not same, but
when all audit items is completed (that means No_Of_Items = Completed) the
whole audit can be marked as Close Out (an audit has many audit items). So I
need to verify if No_of_Items = Completed here.

Thanks a million!!!!!


MGFoster said:
-----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-----

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!!!!




MGFoster said:
-----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-----
 
M

MGFoster

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


Hmmm... the type conversion error happens when Access is comparing two
data items and tries to convert one of the items to the same data type
as the other item. Or, when trying to assign a String to a Number data
type (or vice-versa) - like converting a Long number into an Integer.
This means the error is happening on one of the assignments or
comparisons in your query.

Things you might want to try:

1. Check that Completed is a Numeric data type.
2. Check that Audit_No in both tables is the same data type (Text =
Text, Number = Number). If it is a number data type, that it is the
same numeric data type (i.e., Long = Long, Integer = Integer, etc.).

--
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/AwUBSamuAIechKqOuFEgEQKPpACeJkFhb58So8VmkJdLvJe8d0bdip4AoInH
p5yEq6vVDutdxcoSAkb0KgCg
=byX/
-----END PGP SIGNATURE-----
Hi MGFoster,

Thank you for all your helps. But the query is still not working, the type
convension error is still there. My query is now like:

UPDATE tbl_Audits
SET tbl_Audits.AAL_Completed =
DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & tbl_Audits.Audit_No)
WHERE (((tbl_Audits.Completed)<>0));

In the tbl_Audits, basically No_of_Items and Completed are not same, but
when all audit items is completed (that means No_Of_Items = Completed) the
whole audit can be marked as Close Out (an audit has many audit items). So I
need to verify if No_of_Items = Completed here.

Thanks a million!!!!!


MGFoster said:
-----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-----

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-----
 
L

Liliane

Hi,

I don't think it's a real type conversion error, since if I don't use the
DMax() but just update AAL_Complete with Date_Actioned, everything is fine.

So.....do you think there's something wrong with the DMax() function?

thanks!!!

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


Hmmm... the type conversion error happens when Access is comparing two
data items and tries to convert one of the items to the same data type
as the other item. Or, when trying to assign a String to a Number data
type (or vice-versa) - like converting a Long number into an Integer.
This means the error is happening on one of the assignments or
comparisons in your query.

Things you might want to try:

1. Check that Completed is a Numeric data type.
2. Check that Audit_No in both tables is the same data type (Text =
Text, Number = Number). If it is a number data type, that it is the
same numeric data type (i.e., Long = Long, Integer = Integer, etc.).

--
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/AwUBSamuAIechKqOuFEgEQKPpACeJkFhb58So8VmkJdLvJe8d0bdip4AoInH
p5yEq6vVDutdxcoSAkb0KgCg
=byX/
-----END PGP SIGNATURE-----
Hi MGFoster,

Thank you for all your helps. But the query is still not working, the type
convension error is still there. My query is now like:

UPDATE tbl_Audits
SET tbl_Audits.AAL_Completed =
DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & tbl_Audits.Audit_No)
WHERE (((tbl_Audits.Completed)<>0));

In the tbl_Audits, basically No_of_Items and Completed are not same, but
when all audit items is completed (that means No_Of_Items = Completed) the
whole audit can be marked as Close Out (an audit has many audit items). So I
need to verify if No_of_Items = Completed here.

Thanks a million!!!!!


MGFoster said:
-----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-----
 
M

MGFoster

Liliane said:
Hi,

I don't think it's a real type conversion error, since if I don't use the
DMax() but just update AAL_Complete with Date_Actioned, everything is fine.

So.....do you think there's something wrong with the DMax() function?

thanks!!!

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

Take the DMax() expression and do some substituting. Like this:

The function as it is used in query:

DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & tbl_Audits.Audit_No)

Open the Debug window (ctrl-G) and substitute the values of Audit_No
like this (use a valid Audit_No):

? DMax("Date_Actioned","tbl_AAL_Items","Audit_No=2533") <hit Enter key>

Dose this work? Check that Audit_No is NOT a Text data type (look in
the table definition). If it is a Text data type you'll have to put
quotes around the value - like this:

? DMax("Date_Actioned","tbl_AAL_Items","Audit_No='2533'") <hit Enter>

Does that work? If it does work, change the query DMax() to this (watch
out for line wrap):

DMax("Date_Actioned","tbl_AAL_Items","Audit_No=" & tbl_Audits.Audit_No
& "'")

--
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/AwUBSasb8IechKqOuFEgEQJhRwCfblqAoqtxSoXkGYwZgW8J13cZk3cAn0P6
mkEgvTi4WZ+1OXbRjk0NAYJH
=t6OY
-----END PGP SIGNATURE-----
 

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

Top