Access Expression Help

[mbtransport]

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!

 

[MazeWorX]

There are a couple of ways but a simple update query would do it
What identifies the type of load?

This query assumes the table is called Table1 - Substitute it for your table name. It also assumes there is a field to identify the type of Load called LoadType with a value of Agg to identify the aggregate loads anything else in this field will cause it to calculate Miles * Rate

UPDATE Table1 SET Table1.Gross = IIf([LoadType]="Agg",[Ton]*[Rate],[Miles]*[Rate])

You should know that totals are not usually written to a db they are calculated when needed. The same IIF can be assigned to a control on a form or Report which will calculate when the form or Report is loaded with data

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

How are ya mbtransport . . .

How are youd identtifying [blue] dry freight/aggregate materials[/blue] in the table?

[blue]Your Thoughts . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mbtransport]

Thanks for y'all's responses. I will attempt to make Maze's solution, I'm sure it will work but me getting it to work is the challenge. I plan on using a number system to identify the load type ( 1=aggregate,2=dryfreight, 3=flatbed,...4,,,5,,,6,,,7,,,

 

[TheAceMan1]

mbtransport . . .

For [blue]load type[/blue] id use a listbox or combobox on the form to make the selections (descriptive selections that is). Descriptive will be much more meaningful to your users and they won't have to remember a number system!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

As Ace has suggested a combo can display Descriptions but could for you write numbers to the fields. Create another table for the LoadType with descriptions and a TypeID field. you would then display the description and write the id to the table

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

Howdy MazeWorX . . .

IMHO ... I see no need (outside of a primarykey in the table) to number [blue]descriptions[/blue] that already say it all! To me it adds unneeded complexity ... but ... if thats what [blue]mbtransport[/blue] wants ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Actually i wasn't suggesting he add a numbered column as well as the primary key column. I assumed he and anyone else would know that they were one and the same ( see 'Normalized Data') any who i digress. mbtransport i created a quick example of what i speak for your viewing pleasure. keep in mind this is only one of many ways to accomplish what you want

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[mbtransport]

Maze, That is exactly the direction that I am trying to go towards.! To clarify the reason for the number for the load type is because we have so many tickets that come in that we must enter, it is much faster and efficient to use the 10 key side of the keyboard than using the entire keyboard to enter the loads in. Everything that we do Customers, Origins, Destinations, Load Types has a number associated with it. After years of doing this we have them burned in our memory and know exactly what number goes with which information. Any new person has to learn our system or they don't need to be around

Maze, Is there a way that I can build off your form you created?

 

[MazeWorX]

Its your to do what you want with. We use this site to get and give help. if you want to display the number you can adjust the column sizes. The combo actually contains the LoadType data from the table I just bind the control to the ID column, display the Description and hide the 3rd. This can be accomplished by specifying the column widths. If you look at the properties you will see the column widths set as 0";2";0" in other words the first column is set to 0" 2nd to 2" and the 3rd to 0". The control is based on a query of the Table LoadType which contains all 3 columns of the table. The bound column represents the value of the control which is assigned when you select from the list of choices which is referenced in the 'On form load' and 'on change' events with vba

Any other questions just let us know

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

MazeWorX . . .

[blue]After years of doing this [purple]we have them burned in our memory and know exactly what number goes with which information[/purple].[/blue]

Look like that numeric ID column is needed after all ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

yep, although not for the reason i was thinking about I personally like to display them with the descriptions. Great if the seasoned employees know the numbers difficult to train a new employee though. But what ever floats your boat i guess

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[mbtransport]

MaZeWorX,
Can you give me a bit of insight in how to get into what you have created? All I have been able to get done is change the view from form, spreadsheet,design, etc. I have not been able to edit anything besides that.

 

[mbtransport]

Nevermind, I figured it out! Thanks Thought!