qry that group dates (1 Viewer)

[radur]

This is my table
ID Data1 Data 2 Value
1 01/01/2011 31/01/2011 200
2 01/02/2011 28/02/2011 200
3 01/03/2011 31/03/2011 500
4 01/04/2011 30/04/2011 200
5 01/05/2011 31/05/2011 400
6 01/06/2011 30/06/2011 400
I want a qry that group the value by first data1 and last data2 but I don't know what clause to use
Ex:
Data1 Data 2 Value
01/01/2011 28/02/2011 200
01/03/2011 31/03/2011 500
01/04/2011 30/04/2011 200
01/05/2011 30/06/2011 400
Thanks for your support !

 

[stopher]

Welcome to the forum.

I can't see any logic here. Are you grouping on Value? In which case, why are their two records for Value=200 in your results?

I would have expected the results to look like this:

01/01/2011 30/04/2011 200
01/03/2011 31/03/2011 500
01/05/2011 30/06/2011 400

Also note that Value is a reserved word. So if you continue to use it as a field in your table, you may run into problems later.

Chris

 

[radur]

The dates from tbl is a period 01/01/2011 - 31/01/2011 = january
The number after dates is a value
I want the qry to group dates and showing, if the value is the same, the full period, without grouping dates if the value is changing, and I showed the result that I want to see after I run the qry
EX
01/01/2011 28/02/2011 200
01/03/2011 31/03/2011 500
01/04/2011 30/04/2011 200
01/05/2011 30/06/2011 400
If the result is not this than it would be a wrong answer, because in the interval 01/01/2011 30/04/2011 the value is not 200 (01/03/2011 31/03/2011 = 500)

 

[vbaInet]

This is my table
ID Data1 Data 2 Value
1 01/01/2011 31/01/2011 200
2 01/02/2011 28/02/2011 200
3 01/03/2011 31/03/2011 500
4 01/04/2011 30/04/2011 200
5 01/05/2011 31/05/2011 400
6 01/06/2011 30/06/2011 400

You cannot group it like that using your current dataset.

If you had this:

This is my table
ID Data1 Data 2 Value
1 01/01/2011 31/01/2011 200
1 01/02/2011 28/02/2011 200
3 01/03/2011 31/03/2011 500
4 01/04/2011 30/04/2011 200
5 01/05/2011 31/05/2011 400
5 01/06/2011 30/06/2011 400

Then it will be possible.

 

[radur]

First, I want to thank you for your answers and support.
Second, I cannot use what you are telling me because:
- in my tbl I have 200 records;
- as I said the two columns of dates are an interval = a month
- the "value" column can be renamed and is a salary (the column is formed by summing many records and I don't know what is the final value)
- the result from the qry that I want is establishing the salary between hired date and leaving date
- that's why I want the qry to group periods in which the salary is the same
- that's why I cannot use your solution because I would have to know the salary in order to give the same ID, if the salary value is the same.

 

[philben]

Hi,

I have a solution but it's complex, slow and not conventional...
In one word it's DIY !

• Add this function in a VBA Module:

Public Function TheRange(ByVal dDate As Date, ByVal lValue As Long) As Long
    On Error GoTo ErrTag
    Dim oDb As DAO.Database
    Dim oRs As DAO.Recordset
    Dim sSQL As String
    Dim lCount As Long
 
    Set oDb = CurrentDb
    sSQL = "SELECT Data1 FROM MyTable WHERE Value=" & lValue & _
            " AND Data1>" & Format(dDate, "\#mm/dd/yyyy\#") & _
            " ORDER BY Data1"
    Set oRs = oDb.OpenRecordset(sSQL, dbOpenForwardOnly)
 
    With oRs
    lCount = 1
    Do Until .EOF
        dDate = DateAdd("m", 1, dDate)
        If .Fields(0) = dDate Then
            lCount = lCount + 1
        Else
            Exit Do
        End If
        .MoveNext
    Loop
    .Close
    End With
    TheRange = lCount
ErrTag:
    Set oRs = Nothing
    Set oDb = Nothing
    Exit Function
End Function

• The query

SELECT T.Data1, Q.LastMonth AS Date2, T.Value
FROM [COLOR=red]MyTable[/COLOR] AS T,
 (SELECT DateAdd("m",TheRange([data1],[value]),[Data1])-1 AS LastMonth, Max(TheRange([data1],[value])) AS Range
  FROM [COLOR=red]MyTable[/COLOR] 
  GROUP BY DateAdd("m",TheRange([data1],[value]),[Data1])-1
  ) AS Q
WHERE (Month([Data1])=Month([LastMonth])-[Range]+1) AND (Year([Data1])=Year([LastMonth]))
ORDER BY T.Data1;

I assumed that the name of your table is "MyTable"

Philippe