Decimal Places - rounding

[Elaine180]

Hello,

I can't seem to figure out why my query is rounding decimal places.  I'll attach some screenshots.  I must not have the settings proper.  The form is calculating to 2 decimal places just fine.

But the query that writes those calculations to the table is rounding for some reason

Is Standard, 2, not what I should have it set at?

Appreciate any help

[#2]

@Elaine180 

First, it is generally undesirable to save calculated values to a table in the first place, so you might need to rethink why this should be done at all. Whenever you have component values for a calculation stored as individual fields, that is adequate to recalculate the other value as needed, where needed. In a report for example.

That said, you probably have a different datatype defined for the field in the table. What is it?

[#3]

@George_Hepworth 

Thank you. 

I thought I would need to write those values to the table so that I could use them later in calculations for reports.... no?  For example, if I'd like to know how many acres planted between a date range.

Either way, this is the plantings table, I set them to Double, Standard, 2.  Is that not correct?

[#4]

Also, I will try generating a report now where I calculate the Acres Planted right in the report and not use the number from the table (hopefully it won't round).

[#5]

@Elaine180 

Well, you can store and re-use the calculated values. However, the same formula which calculates them in this query can also be used anywhere else you need to re-calculate the value. This is in line with good relational database design practice. 

The reason we avoid storing calculated values is that hard-codes it into a field. If one of the components later gets changed, perhaps due to a correction for a data entry error, that calculated value becomes invalid and that's a bad thing.

Sometimes, we DO store a calculated value because we want to retain the "As Of This Date Calculated Value", knowing that a future calculation may be different because of changed conditions. A selling price as of the date of sale is an example of this case. Prices go up all the time, so we store the calculated price as of the date of sale so future reports accurately that As Of price for the date of the sale.

Here, there should be no such adjustments except when you correct a value entered incorrectly. 

Now, the reason for the loss of decimals could be in the calculation. Can you post the ACTUAL expression, not just a screen shot? 

Thanks.

[#6]

It would be helpful to see the calculation. It might expose something a screen shot can't.

[#7]

I made a query to do the calculations, I can't insert a screenshot of it for some reason so here's the sql ... sorry, it's a bit complicated, it's calculating from 2 different tables. But the query result gives the 2 decimal places I need (and it displays them in the form properly) I just need that 4.21 and 2.99 to not round when they write to the table.

SELECT Tbl_Plantings.PlantingKeyID, Tbl_Plantings.AssignedCropName, Tbl_Plantings.DatePlanted, Tbl_Plantings.FieldKeyID, Tbl_Plantings.SeedVarietyKeyID, Tbl_Plantings.BedsPlantedCount, [BedsPlantedCount]/[BedsPerAcreInField] AS AcresPlanted, Tbl_Plantings.PopulationPlanted, [AcresPlanted]*[PopulationPlanted] AS SeedAmtPlanted, Tbl_Plantings.EmployeeKeyID, Tbl_Fields.FieldKeyID, Tbl_Fields.FieldName, Tbl_Plantings.SeedTransactionType, Tbl_Plantings.PlantingSeedTypeKeyID
FROM Tbl_Fields INNER JOIN Tbl_Plantings ON Tbl_Fields.FieldKeyID = Tbl_Plantings.FieldKeyID;

[#8]

That worked!  I very much appreciate your advice - I'm not going to write the Acres Planted to the planting table, I will show the calculation in the form (just for info) and then calculate and show the result in the report.

[#9]

Congratulations on solving the problem and good luck with the project.