Results 1 to 4 of 4
  1. #1
    DeeBoyd is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    2

    Formula calculating on primary key (auto number)

    Within a qry: TotalDropRate: [AmtofDrops]*[DropsRate]
    The AMTifDrops column is manually entered value eg. 4
    The DropsRate is from a lookup table.
    Table is ID,Rate,Description. ID number is the auto number primary key.
    eg.

    ID Rate Description
    1 $30
    2 $40
    3 $50
    4 $60

    The formula above is calculating on the ID number instead of the rate. So it is TotalDropRate: 4*3 = 12 Where I need 4*50=200


    Any insight?? I am stuck.
    Thanks in advance

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is your dropsrate field a lookup field? if so, that is the reason why.

    Solution - change the lookup back to text so you can see what you are dealing with. Then in your query link to the lookup table there

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dee,

    You may find the following free videos will improve your understanding of database concepts.
    They are useful for working with any database.
    Good luck.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

  4. #4
    DeeBoyd is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    2
    Thank you so much! Late night and didn't think the lookup in the table was my problem. Your awesome for the quick reply. It helped to finish up last night. Totally appreciated.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-10-2016, 12:53 PM
  2. Replies: 7
    Last Post: 11-19-2014, 05:20 PM
  3. Replies: 4
    Last Post: 06-20-2014, 09:23 PM
  4. Calculating a new field using a formula
    By mccambe21 in forum Queries
    Replies: 1
    Last Post: 09-04-2013, 12:16 PM
  5. Primary Key Auto Number not available in Excel for import
    By justphilip2003 in forum Import/Export Data
    Replies: 1
    Last Post: 04-30-2013, 07:52 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