Results 1 to 2 of 2
Thread: Access Design
2003-02-23, 22:34 #1
- Join Date
- Jan 2003
- Ocean, New Jersey, USA
- Thanked 0 Times in 0 Posts
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?
2003-02-23, 23:08 #2
- 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.