Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2017

    Unanswered: Update Query Help

    I greatly appreciate any help you can provide - I haven't been able to find anything useful for my issue. Fair warning: I'm not an Access jedi, so I apologize upfront for my ignorance.

    My DB is for holding recipes and providing "batch planning" to the store owner. If you look at the picture 1, this is the form that the user utilizes to select all the cupcake recipes she wants to make for that week (ignore the numeric boxes). Right now she can select up to 10 recipes (or less), hit "Run Query" and utilizing a "Create Table Query" it'll create the following table, listing out the recipe ingredients, quantities, etc. (see picture 2).

    The boxes next to the combo box selectors are meant to be multipliers. For example, if she's going to make 3 batches of the red velvet for the week, I was wanting to have an Update Table Query run after the Create Table Query that would multiply all the red velvet cupcake ingredient quantities by 3. The part that's throwing me off is how each cupcake will have multiple records due to having multiple ingredients, so how do I have the multiplier effect all the red velvet records without messing up the other records, etc.

    To help clarify some language:
    (1) the combo boxes on the form in picture 1 are called cupcakeselect1.....cupcakeselect10
    (2) the numeric text boxes on the form in picture 1 are called multiply1...multiply10
    (3) the create table query I utilize to make the table in picture 2 is called CupcakeIngredientQuery
    (4) The table created from the query mentioned above is called PivotTableSource

    The general logic of how I thought this might work is:

    IF cupcakeselect1 IS NOT NULL and cupcakeselect1 = PivotTableSource.Cupcake Name, then multiply PivotTableSource.Qty by multiply1 ......repeat for all 10 pairings of cupcakeselect#/multiply#

    Sorry for the long post! Thank you in advance for your time, thoughts, and help!Click image for larger version. 

Name:	Picture1.jpg 
Views:	2 
Size:	33.6 KB 
ID:	17324Click image for larger version. 

Name:	Picture2.jpg 
Views:	2 
Size:	49.3 KB 
ID:	17325Click image for larger version. 

Name:	Picture3.jpg 
Views:	2 
Size:	50.8 KB 
ID:	17326

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    You'll need to use the WHERE clause in your UPDATE statement.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts