Building a database for campground reservations, and wondering about calculating a price field

[MattRGM]

Hi all!


My parents have just bought a campground and so I am using access to help them build a database which will help to record the lot rentals and such. So far I have managed just fine.
Now however, I am trying to create something so that the database will do the pricing. I haven't worked with access in a couple of months so I am finding some information I used to know is slipping. I feel like between my tables and queries I have the information I need to do this but I can't figure it out.

In other tables, I have information such as a field to establish if the rental is daily, weekly, monthly or seasonal. I have the arrival and departure date as well as a day between calculator. I also have listed on the lot table the price for a day, week, month and seasonal.

Initially, my plan was I was going to have the rental type field and then have a length field as well, and then calculate the price by having it look up the proper rental type (Day, week, etc) and the corresponding price and then multiply by the number in the length field. However I realized that this method won't work for example, if we have a person renting for 9 days because we would need to charge them for a week and two days. I know I could build a very long and complicated if function, but that would be insanely long in order to cover all the different combinations of days and weeks and months that could be encountered. My next idea involved building a rather long conversion formula that would convert the "days between" section into weeks, months, and so on based on what rental type it was classified under and then multiplying that number by the rate for that category. This idea technically works, however the cost is way off. Example: Charging a person for 1.3 weeks rather than a week and 2 days would be undercharging them by $30. There are 30 lots on the campground, the season will run about 20-25 weeks (we are still determining when our starting weekend will be) and so imagine this were to happen even 20-30 times in a season, and the gap grows as you go up to 10-13 days, it would mean a lot of money lost.

So, if there any suggestions on how I can charge a person for this mixture? I imagine there must be some way rather than relying on people to manually calculate and fill in the numbers

I likely won't be able to answer any replies immediately (I need to head out for a bit) but I would greatly appreciate any assistance any of you have.

[ssanfu]

I would write a UDF (user defined function) to do the calculation. It could read the entries (1 month, 1 week, 3 days) and return the amount.