number of days query

[osuprog]

I am too new to access to do it on my own, I tried and failed...

I need to calculate a number of days in a selected year in a date range.

Example:
Range: 4/15/2008 until 5/15/2009
Query: how many days in the range were in 2008 and how many in 2009?

The date ranges will vary from just couple of days to many years. I will need to extract the number of days for every year.

Any ideas?

[ajetrumpet]

what is difficult about this is the fact that you have to turn the difference in years into rows of record data. The easiest way to do it is to use the crosstab query wizard. IMO, it's the most useful when you have to group or do calculation requirements on more than one field or type of data in the request.

If you do decide to write a simple SELECT query, the first thing you need to figure out is how to generate the records that state the different years. That's really the only thing that makes sense to do. In a crosstab, what you would have are the years as field names and the number of days in each as record lines.

This could be a lot easier in any other place than in an actual query too. If you could meet the requirement on a form somehow, or even by making another table it might make life simpler.

It doesn't seem that stacked queries are an option because the issue is still there that requires you to generate records by year, which cannot be queried out of a table anywhere. Another option might be the Partition() function. I've actually never heard of it being used, but I learned of it the other day from someone else. It is similar to a crosstab, but it's useful in other ways. Check that out too!

[osuprog]

well, I am having problem writing the query itself. The query would have to split the time period to different years and I cant figure out a way to do it...

[ajetrumpet]

well, I am having problem writing the query itself. The query would have to split the time period to different years and I cant figure out a way to do it...

post sample data using [TABLE] tags and there will be a solution posted for you im sure. If you don't know how to use table tags, check out the link in my signature.

[osuprog]

                 2/15/2005   8/14/2005
3/27/2005   8/26/2006       
3/20/2005   3/20/2007
6/27/2007   8/26/2009
2/27/2005   8/26/20010       
2/27/2005   8/26/2006

so again, the query would have to come up with the number of days in each calendar year for each tuple

[weekend00]

create an extra table named "lastDate" as following:
lastDate
12/31/2001
12/31/2002
12/31/2003
12/31/2004
12/31/2005
12/31/2006
12/31/2007
12/31/2008
12/31/2009
12/31/2010
put all year you need inside the table.

then run following query(your table name: "datarange"):

SELECT daterange.fromdate, daterange.todate, Year([Lastdate]) AS yYear, Year([fromDate]) AS fromyear, Year([toDate]) AS toyear, IIf([fromyear]=[yYear],[lastdate]-[fromDate],IIf([toYear]=[yYear],DatePart("y",[toDate]),DatePart("y",[lastdate]))) AS Days1
FROM LastDate, daterange
WHERE (((Year([fromDate]))<=Year([Lastdate])) AND ((Year([toDate]))>=Year([Lastdate])))
and [fromdate]<=[todate]

[ajetrumpet]

create an extra table named "lastDate" as following:
lastDate
12/31/2001
12/31/2002
12/31/2003
12/31/2004
12/31/2005
12/31/2006
12/31/2007
12/31/2008
12/31/2009
12/31/2010
put all year you need inside the table.

that is a good solution weekend, but it may pose a challenge if the file gets any larger. a structure like that does not compliment dynamic needs, but it certainly would work I'm sure...

[ajetrumpet]

are you interested in a crosstab query for this? that's the easiest. <edit> scratch that...maybe not. to do this for one record, here's is something I can come up with...

Function Assist(oTbl As String, _
                nTbl As String, _
                sDateFld As String, _
                eDateFld As String)

On Error GoTo Cleanup

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION ASSUMES THAT YOU HAVE AN 'ID' FIELD THAT UNIQUELY              |
'IDENTIFIES RECORDS IN YOUR DATE TABLE.  IT ALSO ASSUMES THAT YOU HAVE A      |
'START DATE FIELD AND AN END DATE FIELD.                                      |
'_____________________________________________________________________________|
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 9/17/2010                                                              *
'Purpose: To list the number of days between two dates grouped by year.       *
'                                                                             *
'Arguments:                                                                   *
'oTbl > The table you want to produce grouped records from (table with dates) *
'nTbl > The new table that will act as the new query                          *
'sDateFld > Name of the field where the start dates are stored                *
'eDateFld > Name of the field where the end dates are stored                  *
'                                                                             *
'******************************************************************************

Dim sDateIndex As Long 'INDEX VALUE OF THE sDATE FIELD
Dim eDateIndex As Long 'INDEX VALUE OF THE eDATE FIELD
Dim ctr As Long 'GENERAL COUNTER
Dim sDate As Date 'START DATE FOR CURRENT RECORD
Dim eDate As Date 'END DATE FOR CURRENT RECORD
Dim yDiff As Long 'DIFFERENCE IN YEARS BETWEEN sDate and eDate
Dim db As DAO.Database
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset

Set db = CurrentDb
db.Execute "DELETE * FROM tbl2" 'DELETE OLD DATA IN TABLE IF ANY THERE
Set rsOld = db.OpenRecordset(oTbl, dbOpenDynaset)
Set rsNew = db.OpenRecordset(nTbl, dbOpenDynaset)

'OPEN TABLES
rsOld.MoveLast
rsOld.MoveFirst
rsNew.MoveLast
rsNew.MoveFirst

'GET DATE FIELDS INDEXES HERE
    For ctr = 0 To rs.Fields.Count - 1
       If rs.Fields(ctr).Name = sDateFld Then
          sDateIndex = ctr
             Exit For
       End If
    Next ctr
        For ctr = 0 To rs.Fields.Count - 1
           If rs.Fields(ctr).Name = eDateFld Then
              eDateIndex = ctr
                 Exit For
           End If
        Next ctr

With rsNew

    Do Until rsOld.EOF
    
        sDate = rsOld.Fields(sDateFld)
        eDate = rsOld.Fields(eDateFld)
        yDiff = Year(eDate) - Year(sDate) 'DIFFERENCE WITH BASE 0
        
            For ctr = 0 To yDiff
                .AddNew
                    If ctr = 0 Then
                        !ID = rsOld!ID
                        !cyear = Year(sDate)
                        !cDays = Abs(DateDiff("d", sDate, "12/31/" & Year(sDate)))
                    ElseIf ctr = yDiff Then
                        !ID = rsOld!ID
                        !cyear = Year(eDate)
                        !cDays = Abs(DateDiff("d", _
                                              "1/1/" & Year(eDate), _
                                              eDate))
                    Else
                        !ID = rsOld!ID
                        !cyear = Year(DateAdd("yyyy", CDbl(ctr), sDate))
                        !cDays = Abs(DateDiff("d", _
                                              "1/1" & !cyear, _
                                              "12/31" & !cyear))
                    End If
                .Update
            Next ctr
            
                    rsOld.MoveNext

    Loop

End With

Cleanup:
    rsOld.Close
    rsNew.Close
    db.Close
        sDateIndex = 0
        eDateIndex = 0
        ctr = 0
        sDate = 0
        eDate = 0
        yDiff = 0
        Set db = Nothing
        Set rsOld = Nothing
        Set rsNew = Nothing

End Function

I did not test it. There are some things manually written in as you can see. the argument are from the table to be analyzed. There is a better way, but if you're in a hurry to produce something that works, this is one alternative...

[weekend00]

are you interested in a crosstab query for this? that's the easiest. <edit> scratch that...maybe not.

I like crosstab but I am wondering how a crosstab query generate the middle years for in this case.

(**** I did not see you code until now ******)

[osuprog]

the file is much larger - about 6000 rows of data. I will give it a try in a moment, very interesting solution that I not fully understand but I will.

Thank you, if there are other solutions, I will try them all

[osuprog]

weekend, your query works well and even with 6000 rows is instantaneous.
I need to change the way it outputs the calculations, I should be able to do it

[ajetrumpet]

weekend, your query works well and even with 6000 rows is instantaneous.
I need to change the way it outputs the calculations, I should be able to do it

Great! Then you don't need all that stupid code.

[weekend00]

rsOld.MoveLast
rsOld.MoveFirst
rsNew.MoveLast
rsNew.MoveFirst

What does above codes do? I was thinking the record pointer is pointing to the first record when the recordset is open. is there something relate to fields.count? Would you explain a little bit?

[osuprog]

I will try the crosstab in a moment, I am quite interested how this is going to work. For now I am trying to use the pivot to rearrange the data so I can summarize how many days I have total in every year.

[ajetrumpet]

What does above codes do? I was thinking the record pointer is pointing to the first record when the recordset is open. is there something relate to fields.count? Would you explain a little bit?

you're right weekend. the pointer is pointing to the first when you open a dataset like that, but for whatever reason, the first record is all that is opened and available. I guess somebody overlooked the alternative. I believe it does the same thing for all types of sets (e.g. - dynaset, dynamic, snapshot, etc...). To compensate, you always have to move to the last and then back to the first to make all of the records available for manipulation. It's kind of a pain but your datasets will always have 1 record in them unless you do it.

as far as fields are concerned, they have a base of 0, but their count has a base of 1. This is another oversight by MS that really should be fixed because it's confusing. Fields aren't the objects that have this problem either. There's plenty others. The only problem with fixing it is that a table with no fields would have a count of -1. But then again, you can't create a table with 0 field in it, can you?