Results 1 to 11 of 11
  1. #1
    Chancho1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3

    Group by and Distinct not working

    Hi Team/Experts,


    I have the following table:

    Click image for larger version. 

Name:	TableSource.jpg 
Views:	23 
Size:	46.4 KB 
ID:	49259


    and I want this result:

    Click image for larger version. 

Name:	TableResult.jpg 
Views:	22 
Size:	25.4 KB 
ID:	49260


    but using Group by and DISTINCT is not working...
    I Appreciate any help guys!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,730
    So what is the SQL statement you attempted? Did you try using Min() and Max() aggregate functions? Why is the ID different in the output?

    If the issue is forcing the SPBX CODE into two groups, that would be difficult (probably impossible) to accomplish with SQL alone. Is there a typo for the CODE producing 9 to 15 range?

    Really best to provide data as text (use the table builder tool on the Advanced editor) instead of image so it can be copy/pasted. Or attach a file (Excel, CSV, Access).
    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
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    124
    For what it's worth, you can try the following:

    Code:
    SELECT T1.ID & "001" AS newID, T1.FROM, T1.TO, T1.CODE
    FROM (SELECT ID, Min(FROM) AS MinF, CODE FROM T1 GROUP BY ID, CODE ) M INNER JOIN T1 ON (M.MinF = T1.FROM) AND (M.ID = T1.ID)
    Groeten,

    Peter

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,730
    Nice try Peter but still not the desired result per example.
    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.

  5. #5
    Chancho1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3
    Hi Guys,
    Using Min and Max give that, the query give the max value from the table, and not the max in that range.

    Click image for larger version. 

Name:	image3.jpg 
Views:	18 
Size:	15.0 KB 
ID:	49265

    I think using a function VBA will do the trick...IŽll try with VBA....If you find another solution via SQL let me know.
    Attached Thumbnails Attached Thumbnails image3.jpg  

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    chanch01,
    As June suggested, provide data not a graphic.
    You have overlapping values n your ranges.
    Most would have 0 to <3, then 3 to <9.
    How do you handle a value of exactly 3? It fits in 2 (distinct) slots in your sample?

  7. #7
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    124
    I see where I went wrong.
    Groeten,

    Peter

  8. #8
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    124
    Another try.

    Perhaps you could use a combination of VBA and SQL. For that you need to create an extra column in the original table. I named that column BLOCK. You can fill the column with VBA:
    Code:
    Dim rs As RecordsetDim PrevCode As String
    Dim Block As Integer
    
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM T1")
    PrevCode = ""
    Block = 0
    
    
    While Not rs.EOF
        If rs!CODE <> PrevCode Then
            Block = Block + 1
            PrevCode = rs!CODE
        End If
        
        rs.Edit
        rs!Block = Block
        rs.Update
    
        rs.MoveNext
    Wend
    
    
    Set rs = Nothing
    Then make a query to produce the output:
    Code:
    SELECT ID, CODE, Min(FROM) AS MinOfFROM, Max(TO) AS MaxOfTO
    FROM T1
    GROUP BY ID, CODE, BLOCK
    ORDER BY BLOCK
    Edit: I am not sure about the 01M002 part.
    Groeten,

    Peter

  9. #9
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    124
    If you want the ID to get a higher sequence number for every "block" with the same code, you could fill the BLOCK in the table as follows:
    Code:
    Dim rs As Recordset
    Dim PrevCode As String
    Dim tBlock As String
    Dim MaxBlock As Variant
    
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM T1")
    PrevCode = ""
    tBlock = 0
    
    
    While Not rs.EOF
        If rs!CODE <> PrevCode Then
            MaxBlock = DMax("BLOCK", "T1", "CODE = '" & rs!CODE & "'")
            If IsNull(MaxBlock) Then
                tBlock = rs!ID & "001"
            Else
                tBlock = rs!ID & Format(Val(Right(MaxBlock, 3)) + 1, "000")
            End If
            
            PrevCode = rs!CODE
        End If
        
        rs.Edit
        rs!Block = tBlock
        rs.Update
        rs.MoveNext
    Wend
    
    
    Set rs = Nothing
    The query will be:
    Code:
    SELECT BLOCK, CODE, Min(FROM) AS MinOfFROM, Max(TO) AS MaxOfTO
    FROM T1
    GROUP BY BLOCK, CODE;
    Groeten,

    Peter

  10. #10
    Chancho1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    3
    Solved!!!
    I appreciate your great help!
    Merry Christmas to all.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    3,329
    Quote Originally Posted by Chancho1 View Post
    Solved!!!
    I appreciate your great help!
    Merry Christmas to all.
    Care to mark it solved then please? That way we know we do not need to look at it, unless we want to see exactly what was offered.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 0
    Last Post: 02-11-2020, 08:12 PM
  2. Replies: 2
    Last Post: 12-05-2011, 04:53 AM
  3. Union and Group by/Distinct
    By Rixxe in forum Queries
    Replies: 3
    Last Post: 11-10-2010, 09:45 AM
  4. SELECT DISTINCT not working
    By johnmerlino in forum Queries
    Replies: 2
    Last Post: 10-25-2010, 06:48 PM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

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