MS Access room different price according to date, duration, occupation (1 Viewer)

[relvin]

Hi,
I have been using MS Access for some tasks and it is very handy. Due to increase efficiency on my new job I also want to create a database. But I am a little bit confused, because there are too many factors to consider. I hope you can help me by giving some ideas. So here is the situation.
I am supposed to give information to the customers according to their needs, while talking via phone.
I want to enter check-in and check-out dates and tell them prices on different premises. But prices vary according to:
1) Premises
2)Room type
3) whether staying one or two people on the room-single or double( because prices are full board)
4) Prices changes due to month of the year, and let say if someone check-in time is 29th of December and check-out time is 03rd of January. 2 days should be calculated on December's price and 2 days on January's price.
5) Prices are different also if someone stays up to 7 days, 7-13 days, 14-20 days and so on.
6) and also there are discounts sometimes(not always) premises offer like if someone stays 7 days, 1 night is free so he/she pays for 6 days.
On premises some room premises has same type of room while others may have different, I am planning to handle this via junction table. But for other issues I am really having hard time to configure.
So I hope you may give me some ideas.
Thanks in advance for your contribution

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=87194

 

[Gasman]

@relvin If you crosspost, please advise each forum you have done so. This prevents members wasting their time offering the same advice.
They can also see what had previously been offered.

 

[relvin]

@Gasman sorry for that, I will keep in mind that, and you can be sure that this will not be repeated in the future.

 

[plog]

What you described is more of a coding task than a database task. Access can still get you there, its just that the heavy lifting is most likely going to be a VBA function, not a series of tables (although they might be involved) and queries.

I suggest you get pen and paper and write an algorithm for how to calculate prices. You've got a good outline (#1-6 above), but now you need to fill in the gaps and make the logic complete. Once you have that, then you need to test it--and try and break it. Throw really weird edge cases at it (36 days that spans 3 months and 2 years and see if your algorithm handles everything correctly.

Then you need to take that algorithm and turn it into a VBA function that you pass all those variables too and it returns a price.