Results 1 to 6 of 6
  1. #1
    str1979 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7

    Need to summarize the output according to location

    I need summarized output where according to unique location the result is displayed.
    There are 4 quarters where every quarter result is summarized according to changing location.
    There are 3 values: completed, on going and yet not completed.
    The first priority is given to completed. second to on going and third to not yet completed.

    In a given location if I want to see that in my first quarter how many projects are completed. Suppose, if 13 locations are there and each location has different values but even one of the single location has completed. we consider it as a completed and likewise same applies to all the locations and quarters.

    There are 16 main locations and every location has 120 to 130 sub locations. We need to compare each and every sub location with the corresponding single location
    and find the result and comprising of all the sub location. The result will be final for the main location.

    Hope I am able to explain the problem.




    Location Q1 Q2 Q3 Q4
    Delhi completed completed on going not yet started

    Mumbai completed on going not yet started not yet started


    For reference, I had attached access database (Database3) in which same is achieved using access but need this thing to be done using excel or macro.
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looking at your dB, you were very close.
    Here is the raw data for both areas:
    Click image for larger version. 

Name:	str1979a.jpg 
Views:	18 
Size:	117.7 KB 
ID:	28437

    Here is your query and a modified version of the same query to change "continuing" to "ongoing"
    Click image for larger version. 

Name:	str1979b.jpg 
Views:	18 
Size:	69.8 KB 
ID:	28438


    Here is the SQL of the modified query:
    Code:
    SELECT DISTINCT Sheet1.CommunityName, 
    IIf(Min([Q-1])="Continuing","Ongoing",Min([Q-1])) AS Qtr1, 
    IIf(Min([Q-2])="Continuing","Ongoing",Min([Q-2])) AS Qtr2, 
    IIf(Min([Q-3])="Continuing","Ongoing",Min([Q-3])) AS Qtr3, 
    IIf(Min([Q4])="Continuing","Ongoing",Min([Q4])) AS Qtr4
    FROM Sheet1
    GROUP BY Sheet1.CommunityName;


    BTW, in the table "Sheet1", the first field is named "NO".
    "NO" is a reserved word in Access (Jet) and shouldn't be used as object names. Better would be "Num", "ID", "LocID"..........

  3. #3
    str1979 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Thanks Steve for the solution.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No problems.....

    Ready to mark this solved??

  5. #5
    str1979 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7

    Thumbs up Solved

    The issue is SOLVED.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For the future, look to the right in the green bar under the thread title at the option "Thread Tools". There are 3 options, the bottom one is "Mark this thread as solved".

    I will mark it solved.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-17-2017, 05:50 AM
  2. Need to summarize data but not sure how...
    By djlabreche in forum Queries
    Replies: 4
    Last Post: 07-08-2014, 08:17 AM
  3. Replies: 4
    Last Post: 04-23-2014, 04:35 PM
  4. Replies: 4
    Last Post: 12-30-2013, 05:51 PM
  5. Summarize on different columns and substract
    By mauro27 in forum Queries
    Replies: 3
    Last Post: 04-27-2010, 01:46 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