Results 1 to 5 of 5
  1. #1
    jurbin is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    15

    Unique Values based upon ID column AND Date/Time column via query

    Hello,



    I am trying to create a query (and Im not super familiar with SQL) to try and take a large set of data and limit the values coming from the table to values that limit an ID column to UNIQUE VALUES ONLY, but select the values to remove based upon date and time.

    I have a data set with columns shown as [Work Item Number] [Item Type] [Item Subtypes] [Queue First Status] [Event Date] [Event Type], etc etc.

    A work item can be worked multiple times over a couple days, weeks or months, so certain items in the data may change, but not all of them and all I care about is the data from the FIRST review. So I would like to limit my query by saying "Only show unique WorkItems by Work Item number (column 1) and show the First/Earliest row that the workitem was reviewed (column 5 - Event Date)"

    I know there is a way to do it, but I cannot figure it out through normal means and as I said, I am not great with SQL or VBA. Thanks in advance for any help!
    Attached Thumbnails Attached Thumbnails 111111.png  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A TOP N query might work - review http://allenbrowne.com/subquery-01.html#TopN
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or a query something like

    Code:
    SELECT A.*
    FROM myTable as A inner join 
        (SELECT workitemno, min(eventdate) as earliest FROM myTable Group By workitemno) AS B 
            ON A.workitemno=B.workitemno and A.eventdate=b.earliest

  4. #4
    jurbin is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2014
    Posts
    15
    Quote Originally Posted by June7 View Post
    A TOP N query might work - review http://allenbrowne.com/subquery-01.html#TopN

    Do I have to type that into the VBA section for it to work on my query, or can that be simply typed into the field in the query design? I apologize, I don't use VBA or SQL much, just the basics of Access DBs and queries so I would appreciate any response with help!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That SQL statement would be typed (or for Ajax suggestion, copy/paste) in SQL View of query builder. Can switch to Design View to see what looks like there.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  2. Replies: 1
    Last Post: 05-14-2015, 06:38 PM
  3. Replies: 2
    Last Post: 08-12-2012, 10:56 AM
  4. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

Tags for this Thread

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