Data Sum Problem

[kbfnet]

Hello,

I have a report that is not summing correctly. Instead of giving me the sum of a seven day period it is giving me the daily total for each day times 3. For example, when my date range is 04/07/2005 to 04/13/2005 I will get data for 04/07/2005 three times. This repeats through April 13th. I have included the SQL below in case it helps. Any thoughts on this problem would be great!

Thanks!
Kbfnet


SELECT
DAILYSUMMARY."STORENO", DAILYSUMMARY."DAILYSUMMARYEODNO",
DAILYSUMMARYEOD."STORENO", DAILYSUMMARYEOD."EODDATE", DAILYSUMMARYEOD."RETAILSALES", DAILYSUMMARYEOD."SALONSALES", DAILYSUMMARYEOD."RETAILDISC", DAILYSUMMARYEOD."SALONDISC", DAILYSUMMARYEOD."GOLDCARDSTOTAL", DAILYSUMMARYEOD."GIFTCARDSTOTAL", DAILYSUMMARYEOD."GIFTCERTSTOTAL", DAILYSUMMARYEOD."TOTALDEPCC", DAILYSUMMARYEOD."TOTALNONTIPPAYOUT", DAILYSUMMARYEOD."TOTALSHORTLONG", DAILYSUMMARYEOD."TOTALDEP", DAILYSUMMARYEOD."SALESTAX",
DAILYREDEMPTIONSUMMARY."PAYMENTTYPEID", DAILYREDEMPTIONSUMMARY."ISSUEDAMOUNT", DAILYREDEMPTIONSUMMARY."REDEEMEDAMOUNT"
FROM
{ oj ("csmart"."dbo"."DAILYSUMMARY" DAILYSUMMARY INNER JOIN "csmart"."dbo"."DAILYSUMMARYEOD" DAILYSUMMARYEOD ON
DAILYSUMMARY."STORENO" = DAILYSUMMARYEOD."STORENO" AND
DAILYSUMMARY."DAILYSUMMARYEODNO" = DAILYSUMMARYEOD."DAILYSUMMARYEODNO")
INNER JOIN "csmart"."dbo"."DAILYREDEMPTIONSUMMARY" DAILYREDEMPTIONSUMMARY ON
DAILYSUMMARYEOD."STORENO" = DAILYREDEMPTIONSUMMARY."STORENO" AND
DAILYSUMMARYEOD."DAILYSUMMARYEODNO" = DAILYREDEMPTIONSUMMARY."DAILYSUMMARYEODNO"}
ORDER BY
DAILYSUMMARYEOD."STORENO" ASC,
DAILYSUMMARY."STORENO" ASC

 

[Madawc]

Try a running total, using a selection formula.

Also try showing your details, since something is probably being selected three times, easy to get with complex links.

If this is the problem, you can group the duplicates and then do your totaling once per group.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems.

Madawc Williams (East Anglia, UK)

 

[kbfnet]

Great ideas. I am going to give them a try. I always forget to give my version. I am using version 8.5.