Results 1 to 9 of 9
  1. #1
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154

    Select Data from Table for calculation

    Dear Guru



    I would like to request for help

    I want to calculate the total room rate from another table that store the price

    For example,

    Room rate -
    1/10 is 100,
    2/10 is 140
    3/10 is 100
    4/10 is 150

    if i stay from 1/10-3/10, how can i calculate that : expecting answer as 100+140

    Thanks
    Eric

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you mean 'from 1/10-2/10'?

    What data can be used to group records? Why are you adding only 1/10 and 2/10?
    Last edited by June7; 09-23-2017 at 09:43 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    June
    Thanks and please find attached of the room

    Different hotel has different rates in date.


    I used to use Index and Match in Excel to locate the rate
    When it comes to Access i do not know how

    it is up to the user to select Hotel A or B, and once they enter the period of stay, then the total rate should come out.

    Dlookup or Switch or something more approperiate.

    I can have separate table to store all rates

    Thanks
    Attached Thumbnails Attached Thumbnails Room_calculation.JPG  

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Showing what you do in Excel is hardly helpful. What is your database structure? Might have a look at https://access-templates.com/downloa...es+v2-608.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    See attached file
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Certainly not a relational database. Will have to create a VBA function to calculate the room cost.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    June

    Can you help me out ?!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I'll be happy to review code you have issue with but you have to build it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154
    June

    Thanks and really appreciated

    As i do not know "VBA"nor Marco, i think i will give up and try other alternative

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-27-2016, 04:06 PM
  2. Replies: 7
    Last Post: 08-20-2013, 06:03 AM
  3. Calculation using Table Data
    By scoobz1234 in forum Access
    Replies: 22
    Last Post: 04-12-2013, 12:31 PM
  4. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM
  5. Select Data from SQL into an Access table
    By MichaelC in forum Programming
    Replies: 4
    Last Post: 07-28-2010, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums