Calculations in tables

[Guest]

I have several tables that are related to each other (one to many in most
cases) I have a customer table, event table, and equipment table. The goal
is for a customer to go online, enter their personal data (this will update
the customer table), then enter/select the date time of their event, which
pieces of equipment they want (pulling data from the equipment table) and
where the event is going to happen; this will update the event table. I need
to figure out the amount of time they will be renting the equipment for (from
the starttime field and endtime field) then determine the cost per hour (from
the equipment field), multiply the right pricing tier and hours, plus add in
mileage to determine a total cost. I really would appreciate any help I
could get, I plan on looking at similar data bases that are already online
and see if they help.

 

[Guest]

Hi, Douglass.

Don't add a field to a table for the number of hours, just calculate it from
the start and finish times on your form and/or in a query. Here it's shown
as the Control Source for an unbound form control named txtHours.

=(EndDate - StartDate)*24

To get the unit price, use the DLookup function on the Products table, in
the AfterUpdate event of your Equipment selection combo box:

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!YourComboBox

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Equipment", strFilter)

For the total, you can either "hardwire" in the cost/mile into your form and
change it periodically, or store it in a table, and look it up. Store the
Mileage in an Event table field. The total is:

=Me!UnitPrice*Me!txtHours + Me!Mileage * $.35

Assign it to an unbound textbox control.

Hope that helps.
Sprinks