Results 1 to 3 of 3
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Calculator to add a percentage to an amount entered in a field

    I am trying to build a database that essentially acts like a calculator.



    The form would have 4 fields: Base Price, Shift, +Hours, Adjusted Price

    There are 3 tables:
    tblShift_Base - which contains 2 rows, 1 for each shift
    tblShift_Hrs - which contains the extra hours and the associated percentage for each shift
    TEMP_TABLE - the table to store the results until reset

    I would like the user to enter the Base Price, Choose the Shift, +Hours and then have a calculated field take the Base Price entered and multiply it by the percentage associated to the shift and +Hours.

    Example...Base Price-100 Shift-2 +Hours 4. In the tblShift_Hrs the percentage value for Shift 2, +hours 4 is 1.6 so the adjusted price would be 160

    Then they hit the reset button to null out all the fields and they can perform another calculation

    I have attached the database I have built so far....I am stuck.

    Thanks in advance for the help

    XTEND_CALCULATOR.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have designated ID fields as primary key yet you are not using them as keys. The ID field in tblShift_Base is the primary key yet you are saving Shift into related table. Need to properly designate and use primary keys.

    The form does not display records because of INNER JOIN in query. Also, each TEMP_TABLE record is joining to many records from tblShiftHrs. Don't use query, just reference TEMP_TABLE as RecordSource.

    Identifying the correct percentage is dependent on both number of hours and shift. Can use a DLookup() in textbox ControlSource.

    I don't understand the data structure. Why have a temp table?
    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
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by June7 View Post
    You have designated ID fields as primary key yet you are not using them as keys. The ID field in tblShift_Base is the primary key yet you are saving Shift into related table. Need to properly designate and use primary keys.

    The form does not display records because of INNER JOIN in query. Also, each TEMP_TABLE record is joining to many records from tblShiftHrs. Don't use query, just reference TEMP_TABLE as RecordSource.

    Identifying the correct percentage is dependent on both number of hours and shift. Can use a DLookup() in textbox ControlSource.

    I don't understand the data structure. Why have a temp table?
    I got it to work. I made a combo field that displayed 2 fields from the base table; shift and hours, then linked that to the temp table. Then to get the adjusted price I looked at the row id of the combination they chose and multiplied it by the percentage I needed. This was based off of a database someone helped me with to calculate weights. I modified it to meet this need as well.

    Thanks for the help.

    I uploaded the DB as well in case someone else can use it.
    Attached Files Attached Files
    Last edited by mindbender; 02-11-2019 at 11:57 AM. Reason: adding db

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

Similar Threads

  1. Have a query show the percentage of an amount
    By TrulyVisceral in forum Queries
    Replies: 4
    Last Post: 05-17-2018, 05:52 PM
  2. Replies: 12
    Last Post: 07-18-2014, 01:22 PM
  3. Cannot change value of a percentage field
    By kdbailey in forum Access
    Replies: 1
    Last Post: 06-19-2014, 02:32 PM
  4. Replies: 16
    Last Post: 05-31-2013, 11:39 AM
  5. Replies: 2
    Last Post: 01-04-2013, 10:05 AM

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