Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Cambridge, Ontario, Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Calculations (2002)

    Hi

    I'm fairly new to Microsoft Access so my knowledge of how to perform some functions is pretty limited. My boss has asked me to create a program for the company that will work on many levels.

    Presently I'm designing and inventory control system. I have and inventory table (tInventory) set up that list all of the materials we have and the quantity of each we have in stock. I also have an assembly table (tAssembly) that lists what materials and the amounts that are required to manufacture a customers product. These two tables are in a many-to-many relationship.

    The problem I'm having is trying to get the calculation of stock in the query back into the inventory table. The calculation I'm using right now is,

    New Stock: [Quantity In Stock] - [Quantity Required] * [Quantity Ordered]

    That part of the calculation is working. I cannot seem to figure out how to put the answer of the calculation into the inventory table.
    Is this event possible to do?

    Thanking you in advance

    Julia
    Assistant Office Manager

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Calculations (2002)

    You can change a query into an update query (menu option Query | Update Query). This will add a new line to the query design grid: Update To. In the column for Quantity in Stock, enter the expression to calculate the new quantity in the Update To line.

  3. #3
    New Lounger
    Join Date
    May 2004
    Location
    Cambridge, Ontario, Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Calculations (2002)

    I've tried to do an update query several times trying different things in the Update To line. I haven't been able to get it to work. When I enter an expression to calculate the new quantity I always get a message telling me that it's invalid.
    Any suggestion?

    Thank you,
    Julia

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Calculations (2002)

    You do need to take the label out of the expression in your prior post when you make it an update query - have you done that?
    Wendell

  5. #5
    New Lounger
    Join Date
    May 2004
    Location
    Cambridge, Ontario, Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Calculations (2002)

    It worked!!!!
    Thank you both for your help. It was much appreciated

    Julia

Posting Permissions

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