Results 1 to 2 of 2

Thread: Access Design

  1. #1
    New Lounger
    Join Date
    Jan 2003
    Ocean, New Jersey, USA
    Thanked 0 Times in 0 Posts

    Access Design

    Trying to:
    1. select records from Table A based on criteria x
    2. based on criteria y, select one of two numeric fields on each record selected in step 1
    3. sum the selected values
    4. set the value of a field in Table B equal to the sum calculated in step 3

    Seems like some combination of Update and "Total" query/queries are needed, but can't quite get it. Run into messages such as "Operation must use an updateable query", etc. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 Posts

    Re: Access Design

    Depending on criteria y, you might execute steps 1 and 2 in one query, or you might need two separate queries. If you're unsure, create two queries - it won't hurt.

    I assume you know how to create a query for step 1.

    For step 2, create a new query with a calculated field. Say that the numeric fields are Num1 and Num2; the calculated field would look like NewNum: IIf(y,[Num1],[Num2]), where y is the condition. For illustration purposes, I'll call the query qryNewNum.

    Please note that step 3 will return a single value - the sum of NewNum. If you're going to set the value of a field in Table B to this value, this table must already contain at least one record, and unless you impose further criteria, the field will be set to NewNum in all records of Table B.

    You can combine steps 3 and 4 in one update query based on Table B. Add the field to be updated to the query grid, and set the new value to DSum("NewNum","qryNewNum"). This will sum the values of NewNum for all records in qryNewNum.

    It's also possible to create another intermediate query: a Totals query that sums NewNum, and then use a DLookup instead of a DSum to retrieve the already summed value.

    Post back if you need more assistance.

Posting Permissions

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