Count Date ranges 1

[Excorsa]

Hi, I have a table with milestone info. In this table I have a date that I want to create a report based off of. This is what I need it to look like:

Total Past Due 73
Up to 7 days past due 3 4%
8-30 days past due 13 18%

and so on. How do I create a query to do this? I am very new with access so any help would be greatly appreciated. Thanks.

 

[dhookom]

Table and field names please with maybe some sample records?

Will your "days past due" ranges ever change or are they going to stay exactly the same until eternity?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Excorsa]

The "days past due" ranges will not change those will be the ranges that they will always want to see.
Table Name: Milestones

MilestoneNum PDDMSDate
P02-MS-01 11/28/2004
P02-MS-02 11/28/2004
P02-MS-03 3/28/2005
P02-MS-04 1/28/2006
P02-MS-05 2/28/2006
P02-MS-06 3/28/2006
P02-MS-07 6/28/2005
P02-MS-08 1/28/2006

 

[dhookom]

I would open a new blank module and create a function like:

Function GetPastDueTitle(pDate As Date) As String
    Select Case DateDiff("d", pDate, Date)
        Case 1 To 7
            GetPastDueTitle = "Up to 7 days past due"
        Case 8 To 30
            GetPastDueTitle = "8-30 days past due"
        Case Else
            GetPastDueTitle = "Send Bubba to Collect"
    End Select
End Function

You can then use this function anywhere you would use a function. For instance:
SELECT MilestonNum, PDDMSDate, GetPastDueTitle([PDDMSDate]) as DueWhen
FROM MileStones;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Excorsa]

I'm sorry I havn't been vary clear. I am not trying to generate the titles. I need to figure out how many milestones are in each range. And then take that and figure their percentage. The titles can be static in report.

 

[dhookom]

If you have a query that returns the range/title for each record as I have provided, you should be able to create a totals query from that query to "figure out how many milestones are in each range".

Use your new query in a report to calculate the percents. I think one of the report wizards will do that for you.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]