Results 1 to 9 of 9
  1. #1
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13

    Need help with Calculation in Qry

    Hi,



    I have this qry "QRYUP".

    Picture 01 is the bones, Picture 02 is actual QRY results. Yes I moved item from one location in whse to another but that shouldn't affect the qty.

    Why is my QRY reporting 50 as "TotalRm"..........60-15-10 = 35 lbs. Should I be using a simple SUM OR is there a better way to do this?

    Thanks in advance
    Attached Thumbnails Attached Thumbnails 01.JPG   02.JPG  

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You are grouping by location and have 2 different locations
    J-90-J has 60 - 10 =50
    T-90-T = -15. This isn't shown as the filter is for sum > 0

    BTW you have some very confusingly named fields such as BatchDate which isn't a date
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    Just so we are clear

    I started with

    60 lbs in T90T
    -15 lbs in T90T
    Changed location to J90J
    -10 lbs in J90J

    So I should have 35 left.......... Somehow its only counting J90J & not combining all the transactions

    THX

  4. #4
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    BTEW you have some very confusingly named fields such as BatchDate which isn't a date
    YES RIDDER it works for me , but you are correct its actually a text field ;-)

    OK so how can we make this work? Since everything is under the code B69686968

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Don't group by location - remove that field from your query
    Then it will add all 3 records giving a sum = 35
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    Quote Originally Posted by ridders52 View Post
    Don't group by location - remove that field from your query
    Then it will add all 3 records giving a sum = 35
    I am not trying to be hard, but I need to keep that field since location is important to track where the stock is.

    THANKS FOR YOUR IDEAS & REPLIES

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I didn't intend you to delete the location from the table - just from the query

    However if you need that field in that query, another suggestion is to remove the >0 filter.
    You will then get 2 records but at least you'll see the overall result

    OR instead display the data in a report where you can easily do overall totals as well as location totals if you want.
    Just use he grouping feature of reports
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    omer123456 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    13
    Great idea about Report & I appreciate it.

    Another question: Once I have a location in the table & I want to change it , what's the best way to update table with new location...... Remember I have employees that don't know access so I am trying to do it from a Qry-> Form. Is that the best way?

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    End users should NEVER have access to tables or queries
    ALL user interaction should ONLY be via forms
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Sum If Calculation Help
    By CT_AccessHelp in forum Access
    Replies: 8
    Last Post: 03-08-2016, 10:13 AM
  2. Age Calculation
    By muncher in forum Access
    Replies: 3
    Last Post: 05-20-2015, 03:52 PM
  3. How to do this calculation
    By rickroller5 in forum Access
    Replies: 4
    Last Post: 09-07-2014, 01:22 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. Calculation
    By buienxg in forum Access
    Replies: 1
    Last Post: 11-16-2011, 07:20 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