Running totals in a query (with an added twist!)

[PortyAL]

Hi

My job involves taking statistical samples from a range of data for audit examination, and I'm trying to program an access database to do this for me. I'll try to explain below what I need:

I have a query set up as follows:

SELECT a.ID, a.Year, a.Period, a.[Batch Ref], a.Date, a.Supplier, a.[Supplier Name], a.Reference, a.[Reference 3], a.Type, a.Gross, a.Discount, a.[Account Code], Sum(b.Gross) AS Total
FROM [qry Invoices] AS A INNER JOIN [qry Invoices] AS B ON A.ID>=B.ID
GROUP BY a.ID, a.Year, a.Period, a.[Batch Ref], a.Date, a.Supplier, a.[Supplier Name], a.Reference, a.[Reference 3], a.Type, a.Gross, a.Discount, a.[Account Code];

This gives the total population with a running total for Gross. I have a pre-set sampling interval and want to pick the records where the running total reaches the sampling interval, and then reset the running total to 0 and continue. The example below might clarify things:

(Assuming a sampling interval of £2000)

Inv.No Gross Total
12121 £1000 £1000
52525 £500 £1500
54511 £600 £2100 (therefore choose this item)
14141 £580 £580 (total resets to 0)
15455 £1100 £1680
54554 £200 £1880
44111 £250 £2130 (therefore choose this item)
74545 £540 £540 etc.

Is this possible? (I appreciate it may be more suited to a spreadheet, but I thought I'd try it in Access first)

AL

 

[PHV]

I'm glad to discover here that my post in your thread (

http://www.tek-tips.com/viewthread.cfm?qid=1060073)

solved your issue.

 

[PortyAL]

I must apologise. I thought I had posted a message telling you the code worked etc. but I've checked the thread again and it doesn't appear to be there. Don't know what happened.

I'm still a learner at Access (previously used Lotus Approach) and have found this forum a great help in solving a range problems, although I think I might be trying too much too quick with this sampling program.
I could do it in Excel, but the data needs filtered alot before the sampling interval is applied to it.

AL

 

[PHV]

Have you tried to use a report doing the running total ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PortyAL]

I can show the running total in a report, but don't know how to reset it to zero each time the sample interval is reached. The more I look at it the more I think it's a job for Excel.

AL