+ Reply to Thread
Results 1 to 3 of 3

Prioritize Criterias in Access

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Prioritize Criterias in Access

    Hi,

    I have a question on how to prioritize criteria in Access. For example, I have this excel doc that I always import to access. Let's call this document A.

    Document A has 2 fields:

    Employee ID
    $ Amount

    1 Employee ID can have multiple rows of $ Amount...

    The values in "$ Amount" field can be positive or negative..

    I currently have this query that groups it based on Employee ID and sum the $ Amount...

    So, if Employee ID 1000 has 1000, -500, 700, -200. Then my access query will show this result:

    Employee ID $ Amount
    1000 1000

    I would also like to build another criteria on top of that: If the SUM of the $ Amount if <1000 or >-1000, then I want to exclude that employee ID...

    So in summary, here are the criterias:
    1. Sum the values in the "$ Amount" field
    2. Exclude those employees with negative SUM

    Sum the values first and exclude the ones with negative SUM...

    How to tell access to go through the first criteria first before proceeding to the 2nd criteria? I've attached an example of the excel file here.

    Thanks!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Prioritize Criterias in Access

    If I understand you correctly, you already use an Access query to sum the total amounts for each employee id? And now you want to exclude any employee who has a negative total? Or do you want only employees with a total that is greater than 1000 or less than -1000? In the first case, in the same column on the query add the following in the criteria line
    Please Login or Register  to view this content.
    In the second case, use
    Please Login or Register  to view this content.
    in the same location.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Prioritize Criterias in Access

    So first you want to determine which IDs are between -1000 and 1000 in value. Then you want only those with positive values. Is this correct? If so, why not just have one criteria, between 1 and 1000.

    Anyway, do your first goupby query to get the values between -1000 and 1000. Then build a second query based upon the first as the record source and set the criteria to greater than zero.

    If this is not what you want, then post back with clearer explanation.

    Edit: To slow again.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1