Update Query

[samot79]

Hi,

I have a table with different recipes and now I want to copy old recipes to create new recipes, this will be useful if many ingredients are similar in different recipes.

tblRecipes


recipeID
ingredientID
qty

So lets say I want to select all rows where recipeID = 24 and update table with those rows but change recipeID to, lets say 25.

[ranman256]

Make a select query using criteria=24. (This would be picked on a form)
then convert it to append query to add it back to the table (excluding the ID)

user picks recipe on form,
then run append query

[Forbes]

Select recipeID
FROM tblRecipes
Where (recipeID.tblRecipes)="24"

This is your start, I am not sure why changing the recipeID would be pertinent? the Unique ID should be given to every entry? maybe I'm missing something, why not just assign the new recipes new IDs?

[samot79]

Hi,

Thanks for you quick help.

@forbes - Lets say recipeID 24 contains 100 ingredients and I want to create recipeID 25 which only contains one extra ingredients.

I have now created a select query that where I have 24 as criteria (I am using [Enter recipeID] on critiera). I have created an append query on this query where I also use parameter value for the new recipe ID ([Enter New recipeID]). But this is not working, qry appends 0 rows to my table.

[samot79]

ranman256 - How can I make the query so the user can enter the recipeID he wants to copy to? Nothing happens when I leave recipeID out from append query.

Now I select all rows with recipeID = 24 and I want to append these rows to the same table but also change recipeID to 25. RecipeID should be parameter values.

[Forbes]

This is a very interesting idea, i do see what you mean now...

INSERTINTO tblrecipes (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM tblrecipes
WHERE recipeID='24';

[samot79]

This is a very interesting idea, i do see what you mean now...

INSERTINTO tblrecipes (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM tblrecipes
WHERE recipeID='24';

It works now with a small change in hyour code. Lets say column2 is my recipeID.

INSERTINTO tblrecipes (column1, recipeID, column3, ...)
SELECT column1, [Enter new recipeID], column3, ...
FROM tblrecipes
WHERE recipeID=[Enter old recipeID];
It works, but not sure if this is the best way of doing it.

Thanks for your help.

[Forbes]

No problem, I hope you find a more streamline process, this was the best that I have off the top of my head, good luck!