Return value from previous record (1 Viewer)

I am new to Access, being primarily an Excel user, so I would appreciate any help you guys can offer me!

I am trying to create an automated charge calculator for customers. Here's how the scenario works:

There are many customers - each customer has their own unique ID. Each month, we total up their orders. They are required to order a certain amount to stay within their contract. If, during a month, they order MORE than the obligated amount, that amount carries over to the next month. If they order less in the next month, BUT they have credit carried over from a previous period, no charge will be assessed (if their carryfoward amount + their orders for the month meets the minimum amount).

I have a table of all order data from every customer. I would like to create a query that automatically calculates their charge for the month based on their previous month's carryover.

Columns in query (titled "Charge Calculation"):
Customer Number
Customer Name
Month
Month's Order Total
Month's Obligation Total
Difference (between Order and Obligation - this is their carryforward amount)

I would like a column that looks within their Customer Number for the previous month's date (all months are simply entered as 6/1/10 or 5/1/10) and returns the previous month's difference amount.

Any suggestions? I've tried using the Dlookup function with little success.

Thanks in advance!

Could you use DSum instead?

JamesMcS said:

Could you use DSum instead?

Click to expand...

I'm a little confused as to what I should put into the "criteria" field of the expression in order to lookup the "difference" value for the previous month only within the customer's record.

So far I have: DSum("[Difference]","Charge Calculation", ??)

Thanks for the help so far!

Probably something along the lines of