Access query to retrieve a field from an earlier record?

[PSchubert]

Greetings All,

I am writing a billing and invoice program. I want to create a query that retrieves the previous month's balance for a given client so that I can include it on the current month's invoice. Here's my setup so far, which works just fine for the current invoice total:

tblInvoice:
PK idxInvoice (AutoNumber)
nfldidxClients (Number)
dfldDate (Date/Time)
nfldInvoiceNumber (Number)
dfldStartDate (Date/Time)
dfldEndDate (Date/Time)

qryTotalInvoice:
Field: idxInvoice
Table: tblInvoice
Criteria: [Forms]![frmInvoice]![txtidxInvoice]

Field: dfldStartDate
Table: tblInvoice
Criteria:

Field: TotalInvoice: [qryTotalMonthlyHourlyCharges]![SumOfAmount]+[qryTotalMonthlyExpenses]![SumOfcfldExpnsAmount]-[qryTotalMonthlyPayments]![SumOfcfldPymtAmount]
Table:
Criteria:

So far, so good. All of the calculations are done using idxInvoice. How can I call upon the figure returned by [qryTotalInvoice]![TotalInvoice] and put it into the Previous Balance field on the next month's invoice? I can't seem to get around the fact that next month's invoice has a different idxInvoice value. Do I need to append the [TotalInvoice] figure to a PreviousBalance table? I was hoping not to save a calculated value for accuracy's sake.

Any help would be most appreciated.

 

[BrianLe]

The following is based on help I got from GingerR a few month's ago for a similar problem and it works good. I've tried to modify it for your problem. Hope it works for you.

I don't know how you are calculating the amount for each order, so I'll assume there is field in tblInvoice called
OrderAmount. I think it would be best if there was a tblOrders that had quantities and unit costs. Then the OrderAmount would be calculated in a separate query.

You need a table called tblCalendarDates, with every date listed in it. That way you can get the in-between dates that have no data. So make a table called tblCalendarDates and fill it with every date that you'll ever need (like from 1/1/2000 - 12/31/2020 or something. You may want to build it in Excel (using drop-down it's easier) then copy/paste into an access table).

FIRST QUERY (called qryPreviousTotal): uses tblCalendarDates and builds running sums of the data between the dates. I used a form with text boxes to select the start and end dates.

SELECT tblCalendarDates.CalendarDate, IIf([CalendarDate]=DMin("dfldStartDate","tblInvoice"),0,CLng(DSum("OrderAmount","tblInvoice","dfldStartDate<#" & [CalendarDate] & "#"))) AS OrderAmountRollingSum,[OrderAmountRollingSum]AS PreviousTotal
FROM tblCalendarDates LEFT JOIN tblInvoice ON tblCalendarDates.CalendarDate = tblInvoice.dfldStartDate
WHERE (((tblCalendarDates.CalendarDate) Between [Forms]![frmInvoiceReport]![tbxStartDate] And ([Forms]![frmInvoiceReport]![tbxEndDate])));

SECOND QUERY (uses query #1, gets current totals and brings in the "Previous Totals" and adds them for Current Total):

SELECT qryPreviousTotal.CalendarDate, qryPreviousTotal.PreviousTotal, Sum(nz(tblInvoice.OrderAmount,0)) AS InvAmount, Sum([PreviousTotal]+nz([OrderAmount],0)AS CurrentTotal
FROM tblInvoice RIGHT JOIN qryPreviousTotal ON tblInvoice.dfldStartDate = qryPreviousTotal.CalendarDate
GROUP BY qryPreviousTotal.CalendarDate, qryPreviousTotal.PreviousTotal;

Hope this helps.