Access Table - Expression Builder unexpected results-CodePudding

I have a huge CSV data file that generates 500,000 rows and 70 columns, running Excel queries over this much data causes my desktop to crash. As an alternative i've managed to import the CSV into Access. The majority of the data fields i need to review/consider within further calculations i've imported as "double" field type. I guess the first question is should i use single rather than double? The values i am considering will only ever report to 2 decimal places.

Within the imported table i've created some new columns, as i need to validate that the sum of underlying values equals the totals reported.

A sum of 5 underlying columns (called SUMofService) [Ancillary Costs] [Incidental Costs] [One-Off Costs] [Ongoing Costs] [Transaction Costs]

I've not reviewed all 500,000 rows, but this formula seems to be summing the values correctly.

Using this value i've then created a new column to compare this total to the total in the report

IIF([SUMofService] = [Total Service],"Match","No Match")

This also seems to work as expected, but there are instances where this field returns a false. Looking at the underlying numbers in [SUMofService] and [TotalService] they match, so i am confused as to why i am seeing the false results.

Could anyone review what i've detailed, and perhaps provide a steer as to whether i've considered something incorrectly.

There are probably better ways to achieve what i'm trying to do, but i haven't really used Access since school and you forget quite a bit in 30 years!!

Any responses are much appreciated - i've googled this as much as i can, but not 100% what to ask, and some responses are so far beyond my level of thinking.

CodePudding user response：

should I use single rather than double? The values I am considering will only ever report to 2 decimal places.

Neither. Use Currency.

That will also provide correct results for:

IIF([SUMofService] = [Total Service],"Match","No Match")

Using Double or, indeed, Single will cause floating point errors - as in this classic example:

? 10.1 - 10.0
 9.99999999999996E-02 

' thus:

? 10.1 - 10.0 = 0.1
False