Different results Access 2.0 to 2016

[DeeCee]

@ridder52 very kindly converted my Access 2.0 db to 2016. It's an accounting package for my business. I'm getting a slightly different result ($1.20) when running a summary of all outgoing transactions. Incoming is fine.

There's 22,000-odd entries, basically quantity, cost and tax, which may be 0. Grand total is circa $3.5M, so it's tiny discrepancy.

The sum of the entries that don't have tax seem to be ok, so there must be something very slightly different in the way the two programs are calculating this. None of the queries or calculations have been changed.



Can anyone shed some light on this?

Cheers

[isladogs]

$1.20 discrepancy in $3.5 million!! Peanuts!

Although I did the conversion for you, without looking at your code I've got no idea how the calculation was done.
Others will have even less idea!

So please can you explain how the calculation was done including any formatting used in each step e.g. Round / Fix / Int / Abs etc

[DeeCee]

$1.20 discrepancy in $3.5 million!! Peanuts!

Although I did the conversion for you, without looking at your code I've got no idea how the calculation was done.
Others will have even less idea!

So please can you explain how the calculation was done including any formatting used in each step e.g. Round / Fix / Int / Abs etc

Lol I know, I'm not losing sleep over it, just my mild OCD kicking in. I'll have a dig, see what I can find. Cheers

[DeeCee]

Here's the query:



Everything identical in both dbs

[isladogs]

I'll look at it later as I still have the files.
I expect it may well be the effects of rounding and then summing the rounded values.

Two questions for now
1. Any idea which version is 'correct'.....if either is?
2. Two of your expressions here have CLng(....*100/100). Why?

[isladogs]

Unable to investigate as I only have the FE files.

Two of your expressions here have CLng(....*100/100). Why?
What happens if you remove all of that?

So instead of:

SELECT DISTINCTROW costs.record, costs.date, CVDate(Format([date],"mmm yyyy")) AS mnth, costs.account, costs.inv, costs.merch, costs.product, costs.quantity, costs.cost, costs.discount, costs.cashbook, costs.[chq no], CLng(([quantity]*[cost]*(1-[discount]))*100)/100 AS [net total], costs.[gst rate], CLng(([net total]*[gst rate])*100)/100 AS gst, [net total]+[gst] AS [total inc gst], costs.[chq presented?]
FROM costs;

you use:

SELECT DISTINCTROW costs.record, costs.date, CVDate(Format([date],"mmm yyyy")) AS mnth, costs.account, costs.inv, costs.merch, costs.product, costs.quantity, costs.cost, costs.discount, costs.cashbook, costs.[chq no], ([quantity]*[cost]*(1-[discount]) AS [net total], costs.[gst rate], ([net total]*[gst rate]) AS gst, [net total]+[gst] AS [total inc gst], costs.[chq presented?]
FROM costs;

You should never use calculated values in others field in the same query as it can create unpredictable results.
You have reused both [net total] and gst.
Either create a second query for calculating those values or set out the calculation in full each time

So the second version above would become:

SELECT DISTINCTROW costs.record, costs.date, CVDate(Format([date],"mmm yyyy")) AS mnth, costs.account, costs.inv, costs.merch, costs.product, costs.quantity, costs.cost, costs.discount, costs.cashbook, costs.[chq no], ([quantity]*[cost]*(1-[discount]) AS [net total], costs.[gst rate], (([quantity]*[cost]*(1-[discount])*[gst rate]) AS gst,([quantity]*[cost]*(1-[discount])+(([quantity]*[cost]*(1-[discount])*[gst rate]) AS [total inc gst], costs.[chq presented?]
FROM costs;

When you look at that you may well realise that the [total inc gst] expression could probably be simplified!

Who knows what the outcome will be after you make those changes ... but it probably will be different

Also look at the number of decimal places and number format used for each field

[DeeCee]

Cheers. I really don't know why I used the CLng thing. It was 24 years ago lol, and I've hardly played with Access since. I will have a play when I get time and use your suggestions. It's actually served us very well in all those years and always reconciled.