Grouping Columns with dates

[gauravnagpal]

Hi
I am working on a very large table that i have to group by Min & Max Dates.
ColumnA ColumnB
1-Jan Melbourne
2-Jan Melbourne
3-Jan Melbourne


4-Jan Melbourne
5-Jan Sydney
6-Jan Sydney
7-Jan Sydney
8-Jan Sydney
9-Jan Sydney
10-Jan Sydney
11-Jan Sydney
12-Jan Melbourne
13-Jan Melbourne
14-Jan Melbourne
15-Jan Melbourne
I want the above table to provide an output as below:
Min Max City
1-Jan 4-Jan Melbourne
5-Jan 11-Jan Sydney
12-Jan 15-Jan Melbourne
i have done this in excel by sorting dates and then comparing values in Column B
any help will be greatly appreciated

[pbaldy]

Try

SELECT ColumnB, Min(ColumnA) As MinDate, Max(ColumnA) As MaxDate
FROM TableName
GROUP BY ColumnB

[Bob Fitz]

I can’t see a way to do this without a third column that can be used to group on. If you add a field to the table called ColumnC, then the Sub below would populate ColumnC with the data required to make this query work

SQL for query

SELECT Min(TableName.ColumnA) AS MinDate, Max(TableName.ColumnA) AS MaxDate, TableName.ColumnB
FROM TableName
GROUP BY TableName.ColumnB, TableName.ColumnC;

Sub

Public Sub AddValueToField()
Dim Rec As DAO.Recordset
Dim strSQL As String
Dim strPrevTown As String
Dim i As Integer
strSQL = "SELECT TableName.ColumnA, TableName.ColumnB, TableName.ColumnC " & _
"FROM TableName " & _
"ORDER BY TableName.ColumnA;"
Set Rec = CurrentDb.OpenRecordset(strSQL)
   With Rec
      .MoveFirst
      strPrevTown = .Fields("ColumnB")
      Do Until .EOF
         .Edit
         If strPrevTown = .Fields("ColumnB") Then
            .Fields("ColumnC") = i
         Else
            i = i + 1
            .Fields("ColumnC") = i
         End If
         .Update
         strPrevTown = .Fields("ColumnB")
         .MoveNext
      Loop
   End With
End Sub

[pbaldy]

It appears I didn't look closely enough at the example.

As Bob said, you need a way of differentiating the multiple groupings of the same city, so give his code a whirl.

[Bob Fitz]

It appears I didn't look closely enough at the example.

Don't bang the head too hard Paul. From what I've seen of your replies, and I've learnt a lot from many of them, it's not often that you miss something.

[pbaldy]

You're too kind. The brain cramps are coming faster now, so I think senility is right around the corner. Wait, no, I'm going to blame it on being on an iPad and it was bedtime. Yeah, that's it!

[gauravnagpal]

Thanks Bob. That worked like a charm
Regards
gaurav

I can’t see a way to do this without a third column that can be used to group on. If you add a field to the table called ColumnC, then the Sub below would populate ColumnC with the data required to make this query work

SQL for query

SELECT Min(TableName.ColumnA) AS MinDate, Max(TableName.ColumnA) AS MaxDate, TableName.ColumnB
FROM TableName
GROUP BY TableName.ColumnB, TableName.ColumnC;

Sub

Public Sub AddValueToField()
Dim Rec As DAO.Recordset
Dim strSQL As String
Dim strPrevTown As String
Dim i As Integer
strSQL = "SELECT TableName.ColumnA, TableName.ColumnB, TableName.ColumnC " & _
"FROM TableName " & _
"ORDER BY TableName.ColumnA;"
Set Rec = CurrentDb.OpenRecordset(strSQL)
   With Rec
      .MoveFirst
      strPrevTown = .Fields("ColumnB")
      Do Until .EOF
         .Edit
         If strPrevTown = .Fields("ColumnB") Then
            .Fields("ColumnC") = i
         Else
            i = i + 1
            .Fields("ColumnC") = i
         End If
         .Update
         strPrevTown = .Fields("ColumnB")
         .MoveNext
      Loop
   End With
End Sub