Detailed sales generating inaccurate/inconsistent data

Update:

I've done some queries and figured where the numbers are being pulled from. Here is where the inconsistency takes place:

Doing a SUM(Price) on TransactionEntry does not fetch the same results as a SUM(Total) - SUM(SalesTax) on [Transaction].

Any ideas?

-- Alex Nielsen

Check the Report Library on CustomerSource for an updated version which fixes the G/P field.

-- Jason Hunt Advanced Computer Systems

Jason,

Will do. Any ideas on the discrepancy between TransactionEntry and [Transaction]?

-- Alex

I suspect the query is not for a specific TransactionNumber. Try running the two following queries and you will notice they are equal: SELECT SUM(Price) From TransactionEntry WHERE TransactionEntry.TransactionNumber = 'xx'

And.

SELECT SUM(Total) - SUM(SalesTax) From [Transaction] WHERE TransactionNumber = 'xx'

The reason the Active Report Gross Profit column does not work on the Detailed Sales reports when grouping is because groupmethodAverage is not a weighted average of what you sold. The report that is on CustomerSource is a Summary report and therefore won't have the same problem if you change it so there is no grouping after the report has been generated. I would also modify the Detailed Sales report so the profit margin column has no grouping at all.

Rob

I just had an Aha! moment after days of kicking this around in my head. These sorts of problems are always caused by the smallest thing.

The reason my query did not work was because I was only fetching SUM(Price). I should have been fetching SUM(Price * Quantity).

Now, my only concern is how a professional programmer could have made that same mistake when they produced this report. Hopefully that is a rare occurence...

Thanks for all of your help!

-- Alex Nielsen