Unanswered: Pricing returned DVD's (join by highest value in last 2 weeks)
The real world problem is this: for our accounts receivable (we are a DVD wholesaler), we want to credit our customers (DVD retail/rental stores etc...) for returned DVD's based on the price of the title when they returned it (original pricing and invoices are lost once our customer removes DVD's from shipping boxes and mixes them up on shelves for several months), with a 2 week "grace period" on either side of the date received to allow for shipping times. Generally (but not always), over time, the price of a given title falls as it becomes older and less popular. For example:
returnedItems (generated when we receive a return from customer):
returnID, itemNumber, Qty, dateReceived
A001, 100, 2, 03/03/07
A001, 101, 1, 03/16/07
A001, 102, 1, 01/14/07
itemPriceDates (for each item, price and starting date for each price)
100, $20.00, 01/01/05
100, $15.00, 02/25/07
101, $12.00, 01/01/04
101, $10.00, 03/01/07
101, $09.00, 03/10/07
102, $40.00, 01/01/04
102, $35.00, 01/15/07
In the above scenario, customer is due (2*20.00+2*10.00+1*40) so the query should show returnID, amtOwed:
This is because for item 100 the highest price in the two week period prior to receiving it was $20.00 (we give them benefit of doubt, even if it's more likely they shipped on or after 2/25). Can the returnID/amtOwed table be generated with a single query? I can do basic querys but this one is beyond me.
Our department is processing hundreds of these returns every month and believe it or not, it is being done by hand! (Someone manually looking at the itemNumber/dateReceived and then at the highest price in the two weeks prior to receiving it). We use JDE for our A/R but somehow the capability to do this has not been built into the system, but we can export all relevant data into MS Access. Amazing what strange ancient worlds still survive in our modern civilization!
As you are dealing with money, I would suggest that you do your calculations in VBA rather than trying to create a complex query. This also gives you the opportunity to trap intermediate results for audit purposes.
Your architecture might run something like this:
1. Select the list of all customers where the count of all unprocessed items is greater than zero.
2. For each customer in this set, create a recordset of their unprocessed items (VBA copes well with nested queries) and perform the arithmetic in memory as you have described it. Store the aggregate results for each customer in an output table .
3. For each record in the same recordset, enter the processing date to ensure that they are not processed again.
4. Run a query or report on the join of the input table (the line items) and the output file (the group totals) to document your transactions.
This process could be run from a form displaying the work to be done and incorporating a button to launch the processing. The results can be viewed afterwards in the same form.