Solved Append/Add Query Question

[NearImpossible]

Hi all, been awhile since i've been around, but working on another Access database and have a question so I figured what better place since you guys have helped in the past!!

I have 2 tables with one being a temp build table where I can do quantity calculations (Table1) before moving it to the final table (Table2)

My question is, Is it possible to append the data from table 1 to table 2, but if the item already exists in table 2, then the quantity would be added to the exisiting value vs appending it?

Example :

Temp Table1

ItemID	ItemName	ItemQuantity	ItemMeasurement
1	Italian Seasoning	2	tbsp
2	Salami	3	oz

Exisiting Table2

ItemID	ItemName	ItemQuantity	ItemMeasurement
1	Italian Seasoning	5	tbsp
2	Provolone Cheese	3	oz

End Result of Table2

ItemID	ItemName	ItemQuantity	ItemMeasurement
1	Italian Seasoning	7	tbsp
2	Provolone Cheese	3	oz
3	Salami	3	oz

Please advise

thank you

 

[isladogs]

Yes. Use an UPSERT query AKA UPEND query.
Basically its an UPDATE query with an OUTER join so that it APPENDS unmatched records.

UpEnd / Upsert Query

A very common database task is to both append new records to a table and update existing records. The standard approach is to use an APPEND query together with an UPDATE query. This article explains how you can combine both the APPEND & UPDATE into one query. The combined query is often known as...

www.isladogs.co.uk

 

[theDBguy]

Hi. Maybe this will help.

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will...

thedbguy.blogspot.com

Edit: Oops, too slow...

 

[NearImpossible]

not sure what im missing, followed isladog's steps, but it doesn't appear to be doing anything even though it stats its updating records.

Trying to update the list table from the list build table.

thank you

Updated database file

 

[XPS35]

Create a separate update and append query.
It is important that you always perform them one after the other in the correct order. I assume that table1 can and should be emptied afterwards.
You should therefore execute the following queries in one and the same procedure.

UPDATE Table1 INNER JOIN Table2 ON Table1.ItemName = Table2.ItemName
SET Table2.ItemQuantity = [Table1].[ItemQuantity]+[Table2].[ItemQuantity];

INSERT INTO Table2
SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.ItemName = Table2.ItemName
WHERE Table2.ItemID Is Null;

DELETE Table1.* FROM Table1;

 

[NearImpossible]

Thanks guys, i'm going to start this DB over as I tried to take some shortcuts which are starting to bite me in the but, will be back if I need more assistance.

thanks again !!

 

[MajP]

Your sql was backward. You want to update the ShoppingList not the ShoppingListBuild

UPDATE
   ShoppingListBuild
   LEFT JOIN
      ShoppingList
      ON ShoppingListBuild.Ingredient = ShoppingList.Ingredient
SET
   ShoppingListBuild.[Meal Name] = [ShoppingListBuild] ! [Meal Name], ShoppingListBuild.Measurement = [ShoppingListBuild] ! [Measurement], ShoppingListBuild.Receipe_Size = [ShoppingListBuild] ! [Receipe_Size]
WHERE
   ShoppingListBuild.Ingredient) = [ShoppingList] ! [Ingredient]
  
;

You should have had

UPDATE
   ShoppingListBuild
   LEFT JOIN
      ShoppingList
      ON ShoppingListBuild.Ingredient = ShoppingList.Ingredient
SET
   ShoppingList.[Meal Name] = [ShoppingListBuild] ! [Meal Name], ShoppingList.Measurement = [ShoppingListBuild] ! [Measurement], ShoppingList.Receipe_Size = [ShoppingListBuild] ! [Receipe_Size];

Notice in the SET commands. You want to update the values in ShoppingList not in ShoppingListBuild. Also in you are doing a left outer join so there is no where condition.

 

[isladogs]

Agree with @MajP but a few other points:
1. Both tables should have a primary key field. In this case, ID would be appropriate as a PK field
2. Why duplicate the same data in 2 tables? Data should only be stored once
3. If you must do this then why not update all fields?
4. Use the ID field to join the 2 tables in your query.
5. Suggest your UPSERT/UPEND query becomes:

UPDATE ShoppingListBuild
    LEFT JOIN ShoppingList ON ShoppingListBuild.ItemID = ShoppingList.ItemID
SET ShoppingList.[Meal Name] = [ShoppingListBuild]![Meal Name], ShoppingList.Meal_Servings = [ShoppingListBuild]![Meal_Servings],
      ShoppingList.[Select] = [ShoppingListBuild].[Select], ShoppingList.Measurement = [ShoppingListBuild]![Measurement],
     ShoppingList.Receipe_Size = [ShoppingListBuild]![Receipe_Size], ShoppingList.Ingredient = [ShoppingListBuild].[Ingredient];