Grouping

[greg2725d]

So I have a simple database set up for customer service contracts. These contracts need to be reviewed yearly. I need to create a report that shows us all of our past due contracts. However, these reports need to be sorted by:
1: 0-30 days past due
2: 30-60 days past due


3: 60-90 days past due
4: 90+ days past due

I was able to calculate the days over due just fine, This just tells me how many days from the current day another day is.

DateDiff("d",[Products]![Initial Date],Now())-365

But I have no idea on how to group them, the way I need them. Any help would be great. I'm fairly new to the reporting part of access btw.

[Robeen]

The best way to do this is to create a query in which you have a field that calculates how many days the contract is overdue by - Name it DaysPastDue.

Then - If you don't already have a Module in your Database - create a new Module.
You can accept the default name of Module1 if you want.

In the module - create a Function that:
Receives the number of days overdue as a parameter and
Returns a String [Eg: 0-30 Days Past Due, 30-60 Days past Due . . .].
In your Function - you will look at the Value that is passed in and if the number of days is less than 30 - you will Return "0-30 Days Past Due" . . . etc.
Name the Function DaysOverdue.

[Let me know if you need help with this - it's quite easy actually].

Create another query - using the first query as the data source.
In the second query - in your DaysPastDue field - make it like this:

OverdueBy: DaysOverdue([DaysPastDue)
What this will do is - for each record in your table it will look at the number of DaysPastDue for that record - and it will return you a Value like 0-30 Days Past Due, 30-60 Days past Due . . ..

Now you can create a Report based on this second Query - and you will be able to group your records in the report on the 'OverdueBy' field in your second query.

This may sound complicated to you - but if you do it one step at a time - it will work out just fine.

Let me know if you need help.

[alansidman]

I would do this with a cross tab query based upon the calculation you show. Here is a short tutorial on how to build a cross tab query.

http://www.datapigtechnologies.com/f.../crosstab.html

Alan

[Robeen]

Thanks for posting that tutorial, Alan. It was very helpful.

[alansidman]

Crosspost

http://www.access-programmers.co.uk/...=1#post1100090

Please read this. http://www.excelguru.ca/node/7

[greg2725d]

I apologize, I was unaware of proper corssposting procedure.

[Robeen]

Did you get the solution you need?

[greg2725d]

I'm working on it now. I keep getting pulled away from my desk. Will post when I do though. thanks guys for your help.