Adding a running total fouls report results

[cisscott]

CR 9.2 / CE 9.2 / SQL Server 2K SP 4 / Win 2K Adv Server

Hi guys. I have another one of those "how the heck do you track this" problems. I have a fairly complex report I designed called our Event Sales Report. It pulls from 4 different tables in our SQL Server Database. I need to add some subtotals at the report footer. THese should total the money received for each seperate payment method, ie Amount paid in cash, amount paid via Visa, MC, and Discover, and amount paid by vouchers. As soon as I enter the running total for the cash transactions, it changes all of the numbers on the report just a little bit (enough to make them wrong). It's almost as if it changed the selection formula or something. I'm thinking the problem is with the one-to-many or many-to-one relationships of the tables. I have tried every way of linking, and ordering the links that I can think of, and still the same results. Anyone have ANY idea what I can do to come to a solution? Thanks !!

 

[cisscott]

OK, after more research I know whats causing the problem, I just cant fix it. the basic idea is this:

sum(eventseat.price) where eventpaid.buyer = 'A' and (eventseat.orderid = eventpaid.orderid)

table EVENTSEAT is a one-to-many relationship with table EVENTPAID. I believe this is causing it to add duplicate records into the sum. Any suggestions? Thanks.

 

[gyfu]

Have you tried using distinct count?

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[Madawc]

I'm puzzled about what sort of count you're using. You said 'running total' but then showed a 'sum'.

As you said, the existing method is somehow making a difference. I'm surprised it happens at all, but it would be less surprising for a summary total, which goes to work on the data before it is printed.

If you're not using a true Running Total, but have tried to make your own using code, that may be the problem. Things might be OK if you used the regular running totals from the Field Explorer.

If that doesn't fix it, try Database > Show SQL QUery. Paste the code to a text file and then compair what changes with your sum.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

When using the running total expert, in the evaluation section, use a formula:

{eventpaid.buyer} = 'A' and
{eventpaid.orderid} <> previous({evenetpaid.orderid})

This assumes that you have your records sorted or grouped by order ID.

-LB

 

[cisscott]

Thanks for the replies guys! Ill try a these suggestions out today. Havent used DISTINCT COUNT cuz im not really after a count. I have tried using both a running total and a sum function, but get the same kind of whacky results either way.