Re: Query Help.. Creating a running Total Field?????
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 10/28/04
- Next message: WeeShawn: "Re: how to used parameter query?"
- Previous message: david epsom dot com dot au: "Re: Parameter Error on Calculated Field (Not typo or "visible" problem"
- In reply to: Darryn Ross: "Re: Query Help.. Creating a running Total Field?????"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 27 Oct 2004 23:59:26 -0500
Dear Darryn:
Are we now down to cases? Here's an approximate query:
SELECT ClientCode, [Date], Autonumber,
(SELECT SUM(T1.Debit - T1.Credit) FROM YourTable T1
WHERE T1.ClientCode = T.ClientCode
AND (T1.[Date] < T.[Date]
OR (T1.[Date] = T.[Date] AND T1.Autonumber < T.Autonumber)))
AS RunningBalance
FROM YourTable T
ORDER BY ClientCode, [Date], Autonumber
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Thu, 28 Oct 2004 14:17:49 +1000, "Darryn Ross"
<darryn@datawave.com.au> wrote:
>Hi Tom,
>
>Thanks for your help but i am still a little confused with this... your hat
>theory explained it well but now i am not sure exactly how to make it work?
>adding a PK like an autonumber will not help because the list needs to be
>ordered by ClientCode and Date and the running totals re-set everytime the
>clientcode changes.
>
>I did find an example form the MS Access website but it uses a Totalling
>Query with Group by... and i tried to manipulate it to fit into my scenario
>however the query wasn't liking my changes. How do i go about ordering or
>recording information to allow the 'Hat' theory to be correctly reflected?
>
>Regards
>
>Darryn
>
>
>
>
>
>"Tom Ellison" <tellison@jcdoyle.com> wrote in message
>news:7ho0o0djuv8j8gq24f7348v9qse7u6u02i@4ax.com...
>> Dear Darryn:
>>
>> To make this work it is essential that there be a column or set of
>> columns that uniquely orders the rows. It must be unambiguously
>> obvious from the data which rows precede any give row, and which rows
>> follow. Otherwise, without an unambiguous order, the running sum
>> cannot be unambiguously calculated.
>>
>> The information that creates this unambiguous ordering must be
>> recorded somewhere in each row. I don't see that. Perhaps you have
>> some other rows that could provide that ordering.
>>
>> To put it another way, if you wrote each row on a slip of paper and
>> threw them all in a hat, drawing them out at random, could you come up
>> with a singe value for the running sum for each slip of paper? If
>> not, then neither can a query do this for you.
>>
>> Tom Ellison
>> Microsoft Access MVP
>> Ellison Enterprises - Your One Stop IT Experts
>>
>>
>> On Thu, 28 Oct 2004 13:03:07 +1000, "Darryn Ross"
>> <darryn@datawave.com.au> wrote:
>>
>> >Hi
>> >
>> >I need to have a running total field in my of my queries and i can't get
>it
>> >to work!!!
>> >
>> >My Fields are ClientCode, Debit, Credit, Running Balance (Debit - Credit)
>> >
>> >i need the running balance to calculate the running balance of every
>> >transaction for each ClientCode then go back to 0 eg...
>> >
>> >ClientCode, Debit, Credit, Running Balance (Debit - Credit)
>> >
>> >1, 100.00, 0.00, 100.00
>> >1, 200.00, 0.00, 300.00
>> >1, 0.00, 100.00, 200.00
>> >2, 0.00, 100.00, -100.00
>> >2, 300.00, 0.00, 200.00
>> >3, 500.00, 0.00, 500.00
>> >4, 50.00, 0.00, 50.00
>> >
>> >I do not want a Group By i need all transactions listed individually with
>> >the accurate running balance for each code??
>> >
>> >Is This even possible?? or do i need to do it in my application? was
>hoping
>> >access could do all the processing for me.
>> >
>> >
>> >Regards
>> >
>>
>
- Next message: WeeShawn: "Re: how to used parameter query?"
- Previous message: david epsom dot com dot au: "Re: Parameter Error on Calculated Field (Not typo or "visible" problem"
- In reply to: Darryn Ross: "Re: Query Help.. Creating a running Total Field?????"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
