Basic calculations

[Jayee]

I have successfully imported my fleet data from a variety of Excel spreadsheets to Access, and got everything the way I want - with just one exception.

I want to do some basic calculations using some fields of the database - to start off with, I want a field to show a sum of the two fields (currency values).

How do I do this ? I do hope you can help me.

Regards,
Jay/UK

 

[THWatson]

Jay
It is best not to store calculated values in the table itself.

You can sum the required fields in a query, or in a report. It depends upon what you want to do with the calculations.

Tom

 

[missinglinq]

As a rule THWatson's advice is correct. Note, however, that there are times when you MAY want to store a calculated value. Say you are calculating the cost of an order of widgets. The cost-per-unit for widgets, today, is $1.50. 100 units will obviously cost $150 TODAY! But next year, the price-per-unit for widgets might be $2.00 a unit, and the price in the ItemsPricingTable has been changed to reflect this. If you then go back over old invoices the cost calculation for today's order will now show 100 untis costing $200 instead of the correct $150!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[THWatson]

Well, sure, you can store the total cost of orders of widgets in the table if you wish. However, if you store the Price and the number of units in the table, it seems to me there is no need to store the calculated cost of individual orders. As long as that Price isn't updated when prices change, the cost of that older order can still be obtained.

Tom

 

[Jayee]

Hi,

The calculated fields are mainly to be used in reports - eg. Difference in fuel expenditure between one month and the next ? AND total cost of fuel over the course of a year ?

However, I do want some fields in my forms because I have one called payment schedule and I want it to calculate the "advance rental" from the monthly rental as follows - "monthly rental x 3" and show the result. I also want it to show the total monthly cost by adding the finance rental to the maintenance charge.

I am pleased to say that I managed to find a Access web site which showed me the way to do basic formulae, so I have at least made a start on the basic calculations mentioned above.

I may, of course, need some help if I need to do more complex calculations, but I will be back in touch then.

Regards,
Jay/UK

 

[THWatson]

Jay
All the best with your project!

Tom