call duration/costs...

[quasimodo]

I'm have a phone call table, which i have made into a form.

I've entered a formula to calculate the duration of the call and this works perfectly on the form, but doesnt get displayed on the phone call table. What do i need to do so the duration calculated also ends up on the table.

There are 2 types of calls (national, international) which both have a different standard cost per minute. The cost per minute is on a seperate table to the phone call table, and i was wondering how i would calculate the cost using the duration and type of call (either national or international). Would I need a query or is there a formula that I can enter into the textbox in the form design view? and if it is a query that is needed, will i be able to do it so the cost enters into the cost textbox, and not in a new window or subform?

or should i just incorporate some VBA into it, and have a command button which, when clicked, will calculate and display the cost of the call in the cost textbox?

 

[Mile-O]

As you shouldn't store calculated values in a table, you can use a query to calculate your call costs.

This query can then be the underlying recordsource for your form.

 

[quasimodo]

so i wouldnt have a record of the call duration in my phone call table...? but only on the form?

if i use a query to calculate the cost, then would that also not appear in the table, and only on the form?

 

[quasimodo]

and could u please also tell me if my tables are alrite...

 

[Mile-O]

Regarding your tables, they seem okay. The only quibble would be NumberOfNights as this could also be calculated from the CheckInDate and the CheckOutDate.

And, with respect to call duration, this can be left to be calculated in a query or on a form.

 

[quasimodo]

so i could/should take out NumberOfNights and Duration from the tables, and only have them on the forms?

how can i transfer the query result of the cost of a call into the actual table without having to type it in?

 

[Idjit]

In regard to your tables, if you will be having guests from North America you'll want to include a field in tblCustomer for State/Province.

I'm also wondering whether it might not be better to link phone calls and messages to GuestBillID rather than RoomNumber. This would save some effort in filtering those tables by check in/out dates in order to limit the records to a particular guest stay.

As Mile points out, to follow the principles of database normalization you want to avoid redundancies in your tables. This is why he recommends deleting any field that is a duplicate of or can be directly computed from other existing fields. You can always use a query to reconstruct this information on the fly, so there is no need to waste space by storing it in the table. (Of course when rates change, the queries won't give accurate totals for charges incurred under the old rates).