Update Query-Passing Max Value

  • Thread starter Thread starter Gregg
  • Start date Start date
G

Gregg

I am trying to pass the maximum order number from a table
of orders into a sigle field in another table. I created
an update query and created the following:

Field: next_fpo_no
Table: OrdersTable
Update To: Max([ord_no])

It tells me, "You tried to execute a query that does not
include the specified experssion 'next_fpo_no" as part of
the aggregate function." I understand what is telling me
yet I don't know how to get around the problem.

Any help would be appreciated.

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

Try something like this (JET db):

UPDATE OrdersTable
SET next_fpo_no = DMax("ord_no", "OrderNumbers")

You have to tell the update query where to find the Max(ord_no), so
change the OrderNumbers table name to whatever the name of your table
that keeps the order numbers.

AND, you must update the OrderNumbers table to increment the ord_no
column so the next hit will pick up the correct NEXT ord_no.

You may wish to consider using an AutoNumber field for the ord_no
column in the OrdersTable instead of keeping the order numbers in a
separate table that has to be maintained by separate procedures


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQCQm44echKqOuFEgEQK8cACg9BYbVS1cK+VbhBriPn5VcGxXxy4An3O3
Zh0MNtbGN/rpk2qz2G+SlGzO
=E6kz
-----END PGP SIGNATURE-----
 
That is finding the maximum order number but the
recipient field is a single field and the query is trying
to populate it with 669 records, which of course it will
not allow the query to do. Can you give me just a little
more information that will help me find only ONE record
and not 669?

Thank you for all your help.
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try something like this (JET db):

UPDATE OrdersTable
SET next_fpo_no = DMax("ord_no", "OrderNumbers")

You have to tell the update query where to find the Max (ord_no), so
change the OrderNumbers table name to whatever the name of your table
that keeps the order numbers.

AND, you must update the OrderNumbers table to increment the ord_no
column so the next hit will pick up the correct NEXT ord_no.

You may wish to consider using an AutoNumber field for the ord_no
column in the OrdersTable instead of keeping the order numbers in a
separate table that has to be maintained by separate procedures


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQCQm44echKqOuFEgEQK8cACg9BYbVS1cK+VbhBriPn5VcGxXx y4An3O3
Zh0MNtbGN/rpk2qz2G+SlGzO
=E6kz
-----END PGP SIGNATURE-----

I am trying to pass the maximum order number from a table
of orders into a sigle field in another table. I created
an update query and created the following:

Field: next_fpo_no
Table: OrdersTable
Update To: Max([ord_no])

It tells me, "You tried to execute a query that does not
include the specified experssion 'next_fpo_no" as part of
the aggregate function." I understand what is telling me
yet I don't know how to get around the problem.

Any help would be appreciated.

Gregg Horwitz
Click to expand...

.
Click to expand...
 
Back
Top