Update or Append query help

[B Miller]

This is probably obvious but I am a bit 'rusty' with my SQL and so I am
missing it.

I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
Inventory is branch, date, and inventory levels of various products
(structure needed for join with another table). DAILY_INV2 is an intermediary
conversion table and is branch, date, product id, quantity and storage
location.

I am trying to set the quantity for a particular product in the Branch Daily
Inventory based on the quantity value from the matching row in the DAILY_INV2
table. Here is my SQL...

UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP Plant
ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
[DAILY_INV2].Quantity
WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
Location])="1"));

The test run lists values of all zeros which is not the correct result.

Any insight?

 

[Ron Hinds]

This is probably obvious but I am a bit 'rusty' with my SQL and so I am
missing it.

I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
Inventory is branch, date, and inventory levels of various products
(structure needed for join with another table). DAILY_INV2 is an intermediary
conversion table and is branch, date, product id, quantity and storage
location.

I am trying to set the quantity for a particular product in the Branch Daily
Inventory based on the quantity value from the matching row in the DAILY_INV2
table. Here is my SQL...

UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP Plant
ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
[DAILY_INV2].Quantity
WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
Location])="1"));

The test run lists values of all zeros which is not the correct result.

Any insight?

The Date column might be a datetime data type. If so, it is unlikely the two
would have the same value thus they are not practical in the JOIN. If that
is the case try removing the Date columns from the JOIN and move them to the
WHERE clause, testing for e.g.

[Branch Daily Inventory].Date > #07/06/2006# AND [Branch Daily
Inventory].Date < #07/08/2006# AND DAILY_INV2.Date > #07/06/2006# AND
DAILY_INV2.Date < #07/08/2006#

to get records for 07/07/2006

 

[B Miller]

When I run the query as a 'select' I get records with values. But when I
test the update, I get the right number of rows but the column is 0. Silly
question, but does the update query show the results after the update or
before?

This is probably obvious but I am a bit 'rusty' with my SQL and so I am
missing it.

I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
Inventory is branch, date, and inventory levels of various products
(structure needed for join with another table). DAILY_INV2 is an intermediary
conversion table and is branch, date, product id, quantity and storage
location.

I am trying to set the quantity for a particular product in the Branch Daily
Inventory based on the quantity value from the matching row in the DAILY_INV2
table. Here is my SQL...

UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP Plant
ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
[DAILY_INV2].Quantity
WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
Location])="1"));

The test run lists values of all zeros which is not the correct result.

Any insight?

The Date column might be a datetime data type. If so, it is unlikely the two
would have the same value thus they are not practical in the JOIN. If that
is the case try removing the Date columns from the JOIN and move them to the
WHERE clause, testing for e.g.

[Branch Daily Inventory].Date > #07/06/2006# AND [Branch Daily
Inventory].Date < #07/08/2006# AND DAILY_INV2.Date > #07/06/2006# AND
DAILY_INV2.Date < #07/08/2006#

to get records for 07/07/2006

 

[B Miller]

When running an update query in test mode using data from another table, it
does not show the real results. When I ran the query in 'real' mode, for a
single row, it correctly updated the targeted field with the correct value;
in test mode, the targeted field remained at zero - the non-updated value.
Very confusing.