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.
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
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.
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?
I see where I went wrong.
Groeten,
Peter
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:
Then make a query to produce the output: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
Edit: I am not sure about the 01M002 part.Code:SELECT ID, CODE, Min(FROM) AS MinOfFROM, Max(TO) AS MaxOfTO FROM T1 GROUP BY ID, CODE, BLOCK ORDER BY BLOCK
Groeten,
Peter
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:
The query will be: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
Code:SELECT BLOCK, CODE, Min(FROM) AS MinOfFROM, Max(TO) AS MaxOfTO FROM T1 GROUP BY BLOCK, CODE;
Groeten,
Peter
Solved!!!
I appreciate your great help!
Merry Christmas to all.
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