Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Access Update with Aggregate

    Friends,

    How can i Update a table colum with a sum() from another table in Access.

    Example

    Update firsTable F set F.mycolumn=(Select sum(x) from SecondTable S where F.column=S.Column )

    In this case Access result : Operation must use an updatable query

    I have tried others statment but no success !

    Please send me a Statment thats works

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    What exactly are you summing? The entire column from the second table? Or only the sum of the records that match?

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    ottomatic,

    I 'm summing only the sum of records that match.

    Thank you

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    You should look at the help for a function called DSum

    DSum(expr, domain, [criteria])

    Example
    The following example totals the values from the Freight field for orders shipped to the United Kingdom. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK.

    Dim curX As Currency
    curX = DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")
    The next example calculates a total by using two separate criteria. Note that single quotation marks (') and number signs (#) are included in the string expression, so that when the strings are concatenated, the string literal will be enclosed in single quotation marks, and the date will be enclosed in number signs.

    Dim curX As Currency
    curX = DSum("[Freight]", "Orders", _
    "[ShipCountry] = 'UK' AND [ShippedDate] > #1-1-95#")
    You can use a domain function in the Update To row of an update query. For example, suppose you want to track current sales by product in a Products table. You could add a new field called SalesSoFar to the Products table, and run an update query to calculate the correct values and update the records. Create a new query based on the Products table, and click Update on the Query menu. Add the SalesSoFar field to the query grid, and enter the following in the Update To row:

    DSum("[Quantity]*[UnitPrice]", "Order Details", "[ProductID] = " _
    & [ProductID])
    When the query is run, Microsoft Access calculates the total amount of sales for each product, based on information from an Order Details table. The sum of sales for each product is added to the Products table.

    Your problem could get complicated and you may have to use VB to code your solution.

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    I will try it.

    Thank you

Posting Permissions

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