Question Expression Help? (Algebra is FUN!)

I have a table with the fields 'Weight (kg)', 'State/Territory', 'Cost per Kilogram' and 'Postage Cost'. It depends on which state they want to send their item to as to the cost per kilogram. I would like to make this table into a form where:
1. The user inputs the weight into a text box.
2. The user selects the state from a combo box.
3. The cost per kilogram doesn't show.
4. In a box at the bottom, the total cost of postage is displayed. This should be automatically calculated, which will be the weight * cost per kilogram.

It is important that the weight per kilogram isn't shown, and this is selected by the user choosing a state to send their item to.

Any help would be greatly appreciated!!

I would separate the 'State/Territory' & 'Cost per Kilogram' to a separate table

tblLocations
-pkLocID primary key, autonumber
-txtLocation
-curCostPerKg

You would then reference the key field from the above in your existing table


'Weight (kg)', 'fkLocID', currCostPerKg

In the location combo box, you would bring in the cost field in the combo box's row source.

Select pkLocID, txtLocation, curCostPerKg FROM tblLocations

You would set its column width property to zero (so the user does not see it). You could reference the column that holds that cost in your expression

=weight(kg)*comboboxname.column(2)

Access starts counting at zero, not 1, so the cost field is column 2 not 3.

Since the cost for a location could change, you would want to store the cost in the underlying shipping table. You will need a control on the form for that, but so that your user does not see it,you would set it's visible property to No.

BTW, you would not store the total Postage Cost in the table since it is a calculated value. Calculated values are generally not stored--only the components used in the calculation.

I would recommend that you download a database from samples on AWF or some simple template database from microsofts templates (->google them) open it up and see how simple things are done. The main issue is the use of queries to present, concatenate or manipulate data.Queries are what make databases tick.

jzwp22 said:

I would separate the 'State/Territory' & 'Cost per Kilogram' to a separate table

Click to expand...

So what fields are in each table? Do I need to build a relationship between these?

jzwp22 said:

'Weight (kg)', 'fkLocID', currCostPerKg

Click to expand...

What is 'fkLocID'?

jzwp22 said:

Select pkLocID, txtLocation, curCostPerKg FROM tblLocations

Click to expand...

Is this for a combo box?

Sorry about my silly questions. I don't understand anything :s

I would recommend that you download a database from samples on AWF or some simple template database from microsofts templates (->google them) open it up and see how simple things are done. The main issue is the use of queries to present, concatenate or manipulate data.Queries are what make databases tick.

Click to expand...

Do a tutorial on acces tables or queries - google. "I don't understand anything" is a poor excuse for not making an effort to find some relevant basic information yourself, and unlikely to bring you forward very speedily. Download a sample database and see what is happening. Make an effort yourself.

So what fields are in each table?

Click to expand...

The following table will hold the states/territories you mentioned. There would be 3 fields in the table as indicated by the names after the hyphens:

tblLocations
-pkLocID primary key, autonumber
-txtLocation (field to hold the state/territory name)
-curCostPerKg (field to hold the cost for the indicated state/territory)

You order table (I assume since you did not provide the name) would look something like this.

tblOrder
-pkOrderID primary key, autonumber
-some reference to a customer?
-weight (your field to hold the weight)
-fkLocID foreign key field to tblLocations, specifies where the item is going
-curCostPerKg (the shipping cost for the particular item; the value is copied from the combo box based on tblLocations into this field when an item is selected from the combo box)

I do not know what other fields you might have in your table, so I will leave that to you.

Do I need to build a relationship between these?

Click to expand...

Yes, the pkLocID in tblLocations is the one side of a one-to-many relationship with the fkLocID field of tblOrder.

I've attached an example database.

Thank you for all your help! I've been doing Access tutorials for a month now and I thought I knew how to use it, but apparently not Thanks for all your time!

You're welcome.