Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    Unanswered: What formula to use

    In Access 2016, what formula to use if I want to calculate the Base Income Per Room Type

    Please see attached Table, and also Fifth and sixth guests must pay an additional $20 charge each per day.Guests staying for 7 days or more receive a 10% discount on their daily room rates.

    I've been playing around with the iIf Function, but I keep getting error boxes

    Click image for larger version. 

Name:	Crop Reservations.PNG 
Views:	7 
Size:	65.3 KB 
ID:	16770

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Base income?
    .. don't understand what you mean unless you mean the lowest, in which case use the MIN function, again allied to a GROUP BY if you need to know the lowest rental rate for each room, otherwise use a WHERE clause for a specific room type

    For your other problems I suspect you need to refine your table design.
    In my books you need a table for:-
    RoomTypes
    Rooms
    Persons
    Bookings
    Possibly a table for actual charges or financial movements ( charges, discounts, quoted rate payments etc) against bookings.
    ...and so on. Read up on normalisation and db design
    Last edited by healdem; 02-12-16 at 07:25.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    Thanks!

    It was a very simple formula and I just made it very complicating because I didn't understand how to use queries (the bottom page of a query). would you have some good reads that explain just Queries really well...

    Thanks once again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    When working with databases, especially SQL relational databases get out of the spreadsheet mentailty. Data exists in tables. Data in tables should be related to similar or associated data by defining relationships. Ferinstance if you need to handle sakes of products youd probably need a table for :-
    Products
    Customers
    Orders (with a foreign key, a relationship to customers so you know what customer ordered this item)
    Order details ( with a foreign key to both product, so you know what products are in this order detail line AND order so you know what order this detail line applues to)
    You exteact data from SQL databases using queries. SQL is a query language. Think of it as "give me all the data that matches the following..."
    Accesx muddies the separation if data from presentation by (optionally but by default) tightly integrating the data storage and presentation (forms and reports) and the use of domain functions in its own application language VBA.
    SQL queries do most of the hard work, do presentation stuff (formatting etc) and the final few percent if stuff thats too tricky in SQL.

    Read up on normalisation
    Read up on table design
    Read up on reserved words an symbols, things not to use when designing tables and columns in Access (and SQL)
    By bottom page of query I oresume you mean you use the query designer in Access. There us a whole world out there yet to discover. Switch to SQL View and you will see tge actual SQL the query designrr generates. The designer is very good at what it does but it cannot do everything that you can by writing SQL directly. I tend not to use the query designer as ut is not as powerful or flexible as writing SQL directly
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7
    Thanks so much!\\

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •