Expression Help

[bboyd]

I am new to the whole Access system besides simple setups. Anyways, I have searched the forum but cannot seem to find an answer to what I am looking for.

A little background, we are a trucking company hauling dry freight which is paid to drivers by the mine, aggregate materials which is paid to the driver by tons they haul(i.e. they haul 25 tons, the rate is $5 per ton=$125 gross).

The way I have my table set up is:

|TON| MILES | RATE | GROSS |

I need to calculate the gross for the two separately, only the dry freight should be calculated by the mile and only the aggregate trucks should be calculated by ton. I have searched the internet and many resources but none show exactly what i'm needing to do.



Thanks for your help in advance!

[ssanfu]

You don't say what all of the fields are in your table, but (to me) it looks like your table is not normalized.

I would have used:

Table_PK Autonumber
Driver_FK Long (FK to driver table)
Quantity Single (or long if only whole tons/miles)
Unit_FK Long (FK to table of units & rates)
UnitRate Currency

The Units table structure would be:

Unit_PK Autonumber
UnitName text (Ton, Mile)
UnitRate Currency

In a query, you would multiply the Quantity * UnitRate to get the gross.

[bboyd]

I don't think the setup that you suggested would calculate either or. I cannot put the quantity as tons/miles in one column, it must be two separate column. I am trying to get it so if the value of tons is 25 and the value of miles is 0 it will ignore the 0 and only multiply the rate times 25 which will give me the gross.

If your solution will do that I apologize and would like a little more explanation on how it will all work together.

[ssanfu]

I don't think the setup that you suggested would calculate either or. I cannot put the quantity as tons/miles in one column, it must be two separate column.

Why must there be two columns for the quantity??
Does the "Quantity" field know whether it is miles or tons??
All you need it to multiply two fields (quantity * rate) to get a result. Then add a field to hold the units (ton, mile, gallon, it doesn't matter).

And accepted database convention is that you shouldn't store the result of the calculation (gross). See http://allenbrowne.com/casu-14.html

See attached database (Access 2000 format).... is this close to what you are trying to do??