Results 1 to 10 of 10
  1. #1
    sdc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    5

    Grouping by a column and sorting by most recent date

    I am trying to sort a construction schedule. I have all of the schedule activities with a completion date that are all tied to a specific home site. I want to filter the schedule activities for each home site by the most recent date of completion by activity.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    off the top of my head...

    SELECT ActivityName, max(CompletionDate) As RecentCompletion
    FROM tableName
    GROUP BY ActivityName;

  3. #3
    sdc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    5

    Still not working. Please see data attached for ideas...

    Below is an example of the data I want to group and sort by. I want to group by the area, sort ascending by the job number and have the most recent date shown of the schedule acitivity end date, filtering out all other activities and dates showing. Any help would be much appreciated!

    Find duplicates for JOBFirstOfAREA_DESCJOB_NUMBERSUPERVISORHOUSE_MODEL_NAMESCHEDULE.ACTUAL_START_DATEPROJ_END_DATEPROJ_CALDURATIONStatusNameSCHEDULE_SACTIVITY.ACTUAL_START_DATEMaxOfACTUAL_END_DATEACTIVITY_NAMEAnderson RidgeADR00001WARD2845 MILLBROOK V2.111/19/20102/4/201178Sold1/20/20111/20/2011Install Wire Shelving

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I have no idea what you pasted. Is that a query?

  5. #5
    sdc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    5
    Yes it was. Can I send you the file?

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    No need. Just paste in the full query. I was looking for indicator words "SELECT" "FROM" "WHERE" etc and couldnt find any to make sense of it. Copy in the query straight from the SQL view and we'll take a look.

  7. #7
    sdc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    5
    SELECT First(AREA.AREA_DESC) AS FirstOfAREA_DESC, JOB.JOB_NUMBER, JOB.SUPERVISOR, JOB.HOUSE_MODEL_NAME, SCHEDULE.ACTUAL_START_DATE, SCHEDULE.PROJ_END_DATE, SCHEDULE.PROJ_CALDURATION, dbo_vutbLotStatus.StatusName, SCHEDULE_SACTIVITY.ACTUAL_START_DATE, SCHEDULE_SACTIVITY.ACTUAL_END_DATE, SACTIVITY.ACTIVITY_NAME
    FROM (((((JOB LEFT JOIN SCHEDULE ON JOB.JOB_NUMBER = SCHEDULE.JOB_NUMBER) INNER JOIN dbo_vutbLot ON JOB.JOB_NUMBER = dbo_vutbLot.JobUnitNum) INNER JOIN dbo_vutbLotStatus ON (dbo_vutbLot.LotStatusID = dbo_vutbLotStatus.ID) AND (dbo_vutbLot.LotStatusID = dbo_vutbLotStatus.ID)) INNER JOIN AREA ON JOB.AREA_ID = AREA.AREA_ID) LEFT JOIN SCHEDULE_SACTIVITY ON SCHEDULE.SCHEDULE_ID = SCHEDULE_SACTIVITY.SCHEDULE_ID) LEFT JOIN SACTIVITY ON SCHEDULE_SACTIVITY.SACTIVITY_ID = SACTIVITY.SACTIVITY_ID
    GROUP BY JOB.JOB_NUMBER, JOB.SUPERVISOR, JOB.HOUSE_MODEL_NAME, SCHEDULE.DATE_TO_START, SCHEDULE.DATE_TO_END, SCHEDULE.ACTUAL_START_DATE, SCHEDULE.PROJ_END_DATE, SCHEDULE.PROJ_CALDURATION, dbo_vutbLotStatus.StatusName, JOB.JOB_TYPE, JOB.LOT_AVAILABLE, JOB.LOT_STATUS, JOB.CLOSED, JOB.JOB_DESC_PE, SCHEDULE.ACTUAL_DURATION, dbo_vutbLot.LotStatusID, SCHEDULE_SACTIVITY.SACTIVITY_ID, SCHEDULE_SACTIVITY.ACTUAL_START_DATE, SCHEDULE_SACTIVITY.ACTUAL_END_DATE, SACTIVITY.ACTIVITY_NAME
    HAVING (((SCHEDULE.PROJ_END_DATE) Is Not Null) AND ((dbo_vutbLot.LotStatusID)=3 Or (dbo_vutbLot.LotStatusID)=5))
    ORDER BY JOB.JOB_NUMBER;

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    SELECT HOUSE_MODEL_NAME, max(ACTUAL_END_DATE) As EndDate, ACTIVITY_NAME
    FROM 'theNameOfTheQueryYouPasted
    GROUP BY HOUSE_MODEL_NAME;

    ^Give this a go and see what happens.

  9. #9
    sdc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    5

    Syntax Error

    Im getting a syntax error. Any chance you could modify my SQL data and paste it so I can see if its what I'm thinking (My user error).

    I really appreciate your help...

  10. #10
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I pasted from your SQL. if what you posted runs, and you replaced 'theNameOfTheQueryYouPasted
    to whatever your query is called, it should run.

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

Similar Threads

  1. Sorting and Grouping
    By mduplantis in forum Queries
    Replies: 3
    Last Post: 07-29-2010, 12:31 PM
  2. Replies: 4
    Last Post: 05-04-2010, 03:33 PM
  3. Date Sorting
    By sfoot0309 in forum Queries
    Replies: 1
    Last Post: 04-25-2010, 09:59 PM
  4. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  5. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 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