update only using last record.

[Guest]

I have set a query to update one table based on data in another. The only
problem is that every time the query runs it uses every record and not just
the last one. This is an inventory sign out form that updates a 'hardcopy'
inventory list in another data table. I should probably just use a
calculation query, but this is how I've been told it has to be done.

What I have:
UPDATE ToolData AS t, ToolCribSignOut AS s SET t.Inventory =
(t.Inventory-s.SignOutQuantity)
WHERE t.ToolCribDesignation=s.ToolCribDesignation;]

Any suggestions are welcome!

 

[MGFoster]

I have set a query to update one table based on data in another. The only
problem is that every time the query runs it uses every record and not just
the last one. This is an inventory sign out form that updates a 'hardcopy'
inventory list in another data table. I should probably just use a
calculation query, but this is how I've been told it has to be done.

What I have:
UPDATE ToolData AS t, ToolCribSignOut AS s SET t.Inventory =
(t.Inventory-s.SignOutQuantity)
WHERE t.ToolCribDesignation=s.ToolCribDesignation;]

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

What do you consider the "last" record? There is no order in a table,
you have to designate what you consider the last row.

I'm guessing that by last you mean the last entered row (record). This
is designated by the date/time. Therefore, all you have to do is
include the date/time in the UPDATE's criteria:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut As S ON
T.ToolCribDesignation = S.ToolCribDesignation
SET T.Inventory = T.Inventory - S.SignOutQuantity)
WHERE S.[some date column] = (SELECT MAX([some date column]) FROM
ToolCribSignOut)

The above assumes there is only ONE row of ToolData per
ToolCribDesignation. IOW, there is a one to many relationship between
ToolData (the one) and ToolCribSignOut (the many).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRJmacIechKqOuFEgEQJekwCgw4QPszpu4UcPSsMf9WjtA1EWBIUAoPO9
6MDLlBv7QmgOAJI7umozEWF4
=rmM2
-----END PGP SIGNATURE-----

 

[Guest]

Thanks for the tip! I do have a date field and also a time field because
multiple records are made each day for each piece. Could you help me
incorporate the MAX time field into this query too?? Also, why the INNER
JOIN function? (does it replace the SET variable function?)

Thanks again for the great help...much appreciated.

UPDATE ToolData AS T INNER JOIN ToolCribSignOut As S ON
T.ToolCribDesignation = S.ToolCribDesignation
SET T.Inventory = T.Inventory - S.SignOutQuantity)
WHERE S.[some date column] = (SELECT MAX([some date column]) FROM
ToolCribSignOut)

I have set a query to update one table based on data in another. The only
problem is that every time the query runs it uses every record and not just
the last one. This is an inventory sign out form that updates a 'hardcopy'
inventory list in another data table. I should probably just use a
calculation query, but this is how I've been told it has to be done.

What I have:
UPDATE ToolData AS t, ToolCribSignOut AS s SET t.Inventory =
(t.Inventory-s.SignOutQuantity)
WHERE t.ToolCribDesignation=s.ToolCribDesignation;]

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

What do you consider the "last" record? There is no order in a table,
you have to designate what you consider the last row.

I'm guessing that by last you mean the last entered row (record). This
is designated by the date/time. Therefore, all you have to do is
include the date/time in the UPDATE's criteria:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut As S ON
T.ToolCribDesignation = S.ToolCribDesignation
SET T.Inventory = T.Inventory - S.SignOutQuantity)
WHERE S.[some date column] = (SELECT MAX([some date column]) FROM
ToolCribSignOut)

The above assumes there is only ONE row of ToolData per
ToolCribDesignation. IOW, there is a one to many relationship between
ToolData (the one) and ToolCribSignOut (the many).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRJmacIechKqOuFEgEQJekwCgw4QPszpu4UcPSsMf9WjtA1EWBIUAoPO9
6MDLlBv7QmgOAJI7umozEWF4
=rmM2
-----END PGP SIGNATURE-----

 

[Guest]

I think I got it,
I wrote 'and' after that where statement and copied the date function but
instead replaced it with time.

Again thanks for the help.

I have set a query to update one table based on data in another. The only
problem is that every time the query runs it uses every record and not just
the last one. This is an inventory sign out form that updates a 'hardcopy'
inventory list in another data table. I should probably just use a
calculation query, but this is how I've been told it has to be done.

What I have:
UPDATE ToolData AS t, ToolCribSignOut AS s SET t.Inventory =
(t.Inventory-s.SignOutQuantity)
WHERE t.ToolCribDesignation=s.ToolCribDesignation;]

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

What do you consider the "last" record? There is no order in a table,
you have to designate what you consider the last row.

I'm guessing that by last you mean the last entered row (record). This
is designated by the date/time. Therefore, all you have to do is
include the date/time in the UPDATE's criteria:

UPDATE ToolData AS T INNER JOIN ToolCribSignOut As S ON
T.ToolCribDesignation = S.ToolCribDesignation
SET T.Inventory = T.Inventory - S.SignOutQuantity)
WHERE S.[some date column] = (SELECT MAX([some date column]) FROM
ToolCribSignOut)

The above assumes there is only ONE row of ToolData per
ToolCribDesignation. IOW, there is a one to many relationship between
ToolData (the one) and ToolCribSignOut (the many).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRJmacIechKqOuFEgEQJekwCgw4QPszpu4UcPSsMf9WjtA1EWBIUAoPO9
6MDLlBv7QmgOAJI7umozEWF4
=rmM2
-----END PGP SIGNATURE-----