Running Total in query

[Vish Narayan]

I have a table with four columns :

Autonumber ProductGroup Amount Date

The table consists of transactions for different dates . The dates are already sorted . But I am having a problem in getting the running total for each product group. I need the running total for every date for each product group. When the product group changes then a new running total is required.
Please assist as I have not been able to find a solution so far.
Thanks

[NeoPa]

May I ask why you believe you need this facility in the query?

The reason I ask is because such a facility is provided as standard in reports and forms, where it makes most sense they be used, but doing so in a query makes little sense generally and is also quite complicated to produce (involving subqueries as it does). Running such a query would also be much less efficient due to that point too of course.

[Vish Narayan]

I have a cross tab report and would like to use this query as the source to get the month end balances for balance sheet accounts like Tr ade receivables and Trade payables under the respective months in the report. Is there any other way to get the balances at the end of each month displayed in a cross tab report under the respective months?

[NeoPa]

I never play with Cross-Tab queries, but Report Controls have a property that handles running sums for you. I would say it is entirely unnecessary for the query to handle it.

[Vish Narayan]

Can you please give me an example of how to get the running total in a crosstab report for just two months say Jan and Feb for the monthend closing balances?

[NeoPa]

No. Your question doesn't even provide enough information to make that really possible, but mainly because you need to do some of the work yourself. I have already pointed you in the right direction. All you need to do now is look and you will find.

[Vish Narayan]

I am aware that report controls have the running total "overgroups possibility. I am only asking if this also applies to a cross tab report. If you are unable to show an example its fine. I was posting my question as I have seen great solutions being provided in this forum to many complicated queries which the member having tried from all angles still did not manage. We do not run to the forum without making an effort first. Thanks for your support.

[NeoPa]

Vish Naryan:
I am aware that report controls have the running total over groups possibility. I am only asking if this also applies to a cross tab report.

It may have helped to make that clearer earlier, but communication is always hard when dealing with language, especially a fundamentally foreign one, so no complaints there. Now that is clear, I have to admit that, though I'd like to help, I don't have the relevant experience as I never use Cross-tabs myself (See comment in post #4). I would only be creating something from scratch just to look into what you already have available to see.

I appreciate your attitude is fine and you weren't, like some members, posting as your first response to anything remotely new or confusing, but that was only made clear after your last post, so please excuse me for any misunderstanding. Please remember next time though, the rules do indicate that you should show what work you've already done in your question post. This avoids any confusion between posters such as yourself and those who are just here to get others to do all their work for them.

[Vish Narayan]

No issues. A friend suggested the following method to get the running total in a cross tab report. I was told to create a record ID for each item in the source table and obtain the running total in the query . Hence managed to get the running totals per product group.The records were already sorted by product group and transaction date. For those months where no transactions existed for some product groups, I created a "missing months query" and appended to the main transactions table .Another query was created that identified the Max of record Id for each ProductGroup. This way I was able to get the running total balance for each month for the last date for each Group. The report was then an easy task.