Can anyone help me do some calculations in MS Access

[Where Did I Go?]

Hello all,

I need to design a relatively straightforward database in MS Access.

The idea is around a hotel booking system.

I have 4 tables: - relating to guests, rooms, room numbers and reservations

Anyway, as part of the reservations table I have included two fields, namely checking in date and checking out date. Both are set to short-date format.

This is fine. Now we are also given the amount paid per night (in this example these vary with different reservations as apposed to using some standard price for specific rooms - although these are given :s)

My question is, is there some function within Access to either, from the two dates imputted calculate a number of nights stayed - and then using this value multiply with the rate per night field (set to currency), to give a total cost of stay figure - given as a new field.

At the moment I am typing in the total cost of stay figure manually, but I feel this is not very practical.

Would appreciate any guidance. I have looked through the 'help' facilities within MS Access, but this has proved unsuccessful.

Many Thanks.

[duffy90210]

I dont use Access, but in other database, I would use the following format :

Create an answers table, use the formula :
((jdate(field_checkoutdate))-(jdate(checkindate)) X field_rate)

What it should do is to convert the dates into julian date which is a number, when checkout - checkin you should get the number of days stayed, then this will multiply by the room rate.

If you need the info on the form itself, you simply create the extra fields and use the same formulas. If the room rates are different, a separate table holding the room types/value should be use, so when you enter room type, ie RoomType_A, the room vlaue of $50 will appear, or RoomType_C for $95 etc.

HTH

[ken]

As far as I can remember you can't do calculations in an Access table so I'm assuming that you're inputing the data in a form.

In your form field you would use the "Datediff" function for the calculation, in the field where you want the function type

=DateDiff("d",[checkindate],[checkoutdate])*[ratepernight]

The field names will of cause be replaced with the exact field names in your form/table.
HTH.

[Where Did I Go?]

Hi all,

Many thanks for both replies. Have just attempted them, and managed to get a version of Ken's suggestion to work.

I did this by entering the formula within the form. - This successfully calculates what I need it to.
Unfortunately, this information does not get stored into the table (and so you can't perform a successful queory). The figure remains visible on the form.

So, I suppose what I ideally need is some way to perform the calculation but set up as part of the table, and field criteria.

Judgeing from your wording and my attempts, I don't think this is possible.

If anyone can suggest a method to calculate this value (and store it in the relevant field within the table), please let me know.

Many thanks.

[ken]

In MS Access calculated data isn't usually stored in the table but recalculated each time the form/query/report is opened, If you want to run a query using the same information just type the same formula into a query field, In access you can use functions in forms, queries or reports, I believe there is a way to update a table with calculated data but I believe It's a little cumbersome and long winded so you'd probably be better off using functions where needed.

HTH.

[Where Did I Go?]

OK, thank you very much for your time and help.
Is very useful and appreciated :)