Results 1 to 8 of 8
  1. #1
    Join Date
    May 2013
    Posts
    56

    Smile Unanswered: Calculated Fields

    In Access 2013, is there a way to use a field from one table to be a part of a calculated field on another table.

    For instance I have a Table of Projects and the Budget Amount for each. I have another table where I am forecasting costs by month. I would like to calcuate the costs by entering a percentage. The cost field would be an expression of Percentage * Budget Amount.

    Is this possible??

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Usually, you build a query that combines data from both (or more) tables, then use that query as the data source of the form.
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    6

    Question Addition of sums

    Good Day,

    I was wondering if it was possible to add multiple items to a transaction or record and then update the quantities(plus them).

    Eg: I have 10 in stock of ItemA, I have 10 in stock itemB. I would like to receive 5 on both and update the stock to 15. I would like to be able to do this in one transaction.

    Any help would be appreciated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    storing calculated fields isn't a smart call, unless you have good reasons to do so.
    Stock is usually the sum of transactions (things that add to subtract from stock). Ive seen Stock control systems that run from a known balance and then add tracnsactions since that date.

    you could do what you want with a stored procedure from Access 2010 on
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In SQL:
    Code:
    UPDATE [SomeTable] 
    SET [Quantity] = [Quantity] + 5
    WHERE [ItemDescription] IN ( 'ItemA', 'ItemB' );
    In VBA:
    Code:
    Dim c_SQL As String
    c_SQL = "UPDATE [SomeTable] " & _
            "SET [Quantity] = [Quantity] + 5 " & _
            "WHERE [ItemDescription] IN ( 'ItemA', 'ItemB' );"
    CurrentDb.Execute c_SQL, dbFailOnError
    As Healdem wrote, you should not directly change the quantity, except when you perform an inventory adjustment after a control of the stock.
    Have a nice day!

  6. #6
    Join Date
    Oct 2013
    Posts
    6

    Question I shall try

    Thanks, shall give it a bash. You guys don't perhaps have any tables already setup for receiving or dispatching stock?

    Any thing will help

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably, but why not try and sketch out your own design based on what you need to support your application or coursework
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2013
    Posts
    56
    I am the person who asked the original question about using a field from one table to calcuate the value of a field in another table. I have the form bound to fields both tables. How would I use a query to calcualte the value for the fields?

Posting Permissions

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