Calculate value in a query that uses data from different records

[rodcwashoe]

Hi,

I'm trying to create a calculated field that uses data from different records. For example: There is a record created in the transaction table for each item sold that contains the customerID, item and amount. I can create a report that totals the amount and calculates a fee by customerID , but I need to create a query or a table from a query that will provide the total amount for each customer in a single field that can be used to calculate a fee that is base upon the total amount.



I've been trying to work this out for several days and my brain is tired.

Thanks

[June7]

Having a record pull data from other record(s) of same table can be tricky. Options:

1. subquery, review http://allenbrowne.com/subquery-01.html

2. domain aggregate functions (DSum, DAvg, DCount, etc) - search Access Help

3. VBA function called by query

4. build a report using Sorting & Grouping features and do summary calcs in report and group sections header/footer

[ranman256]

Yes, in your 1st qry..Qry1 it would be the list of data in the trans. tbl. (you can sum this too if needed by select [CUST ID] , Sum([Amt]) as Total ).
Now the fees, there's easy and hard ways...
1. if its a simple calc (say percent), add another field select [CUST ID] , Sum([Amt]) as Total , Sum([Amt])*0.2 as Fee

2. if it is a table lookup you must add the table to the query, and possible ranges.

3. you could make a Function() ( instead of lookup table), write your own function to return the fee value , given the Amt value

[rodcwashoe]

Thanks June7 and Ranman256 for the quick response.

I will look at your suggestions and try to make it work. Will let you know.

Thanks