Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Auckland, North Island, New Zealand
    Thanked 0 Times in 0 Posts

    populate a year from date ranges (2000 SR 1)

    I have records like this:

    Fields and value

    record 1:

    identifier: 1001
    from_date: 1 Feb 2001
    to_date: 25 Feb 2001
    value: 300
    Owner: ABC

    record 2;

    identifier: 1001
    from_date: 1Mar 2001
    to_date: 31 Mar 2001
    value: 450
    owner: XYZ

    There are numerous records for each identifier and around 10,000 records in total.

    I need to produce a query that will show how many days in the year (and what they are) that do not have any data.

    So for the above example: 3 days missing, 26-28th Feb.

    Then calculate an estimate for the missing data based on the average of the data available for each identifier

    so (450+300)/(25+31 days) = 13.4 per day * 3 days = 40.2

    I would like to be able to rank each identifier by the number of days missing in a time period (between mar 2000 and Sept 2002 or just 1 year) and list the owner prior to the missing period (ie ABC)

    Thanks in advance for your assistance

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Western Massachusetts, USA
    Thanked 0 Times in 0 Posts

    Re: populate a year from date ranges (2000 SR 1)

    Simon, try this.
    First copy this Function to a new Module and name it anything but DaysInMonth

    Function DaysInMonth(dteInput As Date) As Integer
    Dim intDays As Integer
    ' Add one month, subtract dates to find difference.
    intDays = DateSerial(Year(dteInput), Month(dteInput) + 1, Day(dteInput)) _
    - DateSerial(Year(dteInput), Month(dteInput), Day(dteInput))
    DaysInMonth = intDays
    End Function

    Then create a query I'll call Query1
    In Query1, you are going to add the Table that has your records in it. Then add the fields
    identifier and value
    Then add the following expressions
    Then turn on the Totals and Groupby identifier, Sum value, Sum TotalDaysInMonth, and Sum TotalDaysWithData.
    Now create another query
    Add Query1.
    Bring all the fields down to the Field line
    Then add this expression
    MissingDataCalculationSumOfValue/SumOfTotalDaysWithData)*(SumOfTotalDaysInMonth - SumOfTotalDaysWithData)

    That should give you the first part of what you want. Now about ranking, I'm not entirely sure if it can be done. I don't have any data readily available to test it on. Post back if you have problems with the first part.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: populate a year from date ranges (2000 SR 1)

    <hr>First copy this Function to a new Module and name it anything but DaysInMonth<hr>
    Just to clarify, he means you should name the module anything but DaysInMonth. The name of the function is OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts