0

So my challenge is to take the quantity of Chips and Units of Chips in Table 1 and copy it into the On Hand Amount and Units for Cookies and Donuts in Table 2. I have a start below, but I'm still missing how to populate with only "chips" data. 

UPDATE [Table 2].[On Hand Units]
SET [Table 2].[On Hand Units]="kg"
WHERE (([Table 1].[Units]) LIKE "*kg") AND (([Table 2].[Store ID]) NOT LIKE "234" OR "456");

UPDATE [Table 2].[On Hand Amount]
SET [Table 2].[On Hand Amount]=[Table 1].[Quantity]
WHERE (([Table 1].[Units]) LIKE "*kg") AND (([Table 2].[Store ID]) NOT LIKE "234" OR "456");

Example

June7
  • 19,874
  • 8
  • 24
  • 34
user1255441
  • 141
  • 2
  • 11
  • 1
    Ideally, the 'on hand' value should be calculated when needed, not saved. Am confused - you say table 2 is for 'Cookies and Donuts' yet you want to save Chips data. I don't think your "OR" criteria will do what you think. Did you try criteria `Table1.Name='Chips'`? Why does it appear you are just duplicating data? – June7 May 30 '18 at 17:43

1 Answers1

0

Try something like the following:

UPDATE B
SET B.[On Hand Amount] = A.Quantity, B.[On Hand Units] = A.Units
FROM [TABLE 1] A
JOIN [Table 2] B ON A.[Store ID] = B.[Store ID] AND A.Name = B.Name
WHERE B.[Store ID] NOT IN ('234', '456')
websch01ar
  • 2,103
  • 2
  • 13
  • 22
  • SQL syntax will not work in MS Access as it does not use `FROM` clause in `UPDATE` and `JOIN` precedes `SET`. Access dialect follows MySQL in this regard. – Parfait May 30 '18 at 17:56
  • I did not know whether the OP had upgraded his Access applications to use a SQL Server backend...doesn't everyone do that? Regardless, this is an easy fix. The general idea still stands. See the second answer here for the syntax: https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join – websch01ar May 30 '18 at 18:14
  • I would not make that assumption unless mentioned or tagged. Access apps can connect to any known backend (SQL Server, Oracle, Postgres) but these linked tables must still adhere to its JET/ACE SQL dialect unless using pass-through queries. Nonetheless, your answer can easily be adjusted for Access. – Parfait May 30 '18 at 18:20
  • Perhaps this should be an append query instead? I want to take the quantity of chips from Table 1 and copy it to cookies and donuts. Also The name=name solution doesn't work because the names are different. And June, yes I'm duplicating data for an extremely large data set. – user1255441 May 31 '18 at 19:00