Sorting without grouping

[Guest]

I have a report which shows detail of invoices to customers which fall into 2
categories – new work and monthly support payments. I want the report to sort
and group as follows:
Group 1 – Customer
Group 2 – Type of Sale (Category)
Group 3 – Invoice Date
Group 4 – Monthly Rate

The problem I have is that in the Monthly Rate I want a total for the group
BUT when I put this in the group footer I get the total repeated for each
Invoice Date. The Invoice Date is there for a sort only and I'm not showing
the group header or footer for that field.

If I switch Groups 3 and 4 I get the total as I want it but the sort order
within the group is wrong as the monthly rate may increase or decrease over
time.

Am I missing something obvious here…… any help much appreciated

Sheila

 

[Marshall Barton]

I have a report which shows detail of invoices to customers which fall into 2
categories – new work and monthly support payments. I want the report to sort
and group as follows:
Group 1 – Customer
Group 2 – Type of Sale (Category)
Group 3 – Invoice Date
Group 4 – Monthly Rate

The problem I have is that in the Monthly Rate I want a total for the group
BUT when I put this in the group footer I get the total repeated for each
Invoice Date. The Invoice Date is there for a sort only and I'm not showing
the group header or footer for that field.

If I switch Groups 3 and 4 I get the total as I want it but the sort order
within the group is wrong as the monthly rate may increase or decrease over
time.

It sounds like the total text box is in the wrong group
footer section. Over what range to you want to total? It
almost sound like you want it in the date of maybe event the
sale type group footer.

 

[Guest]

Hi Marshall

The total I want is for the Monthly Rate and I'm looking to count number of
months and sum total of payments. I can get it to do this no problem if I
take the Invoice Date out of the equation BUT then it sorts on the Monthly
Rate only and records in each rate are not sorted on the date. What I want is
to put the sort in but not have it treated as a group which is what seems to
be happening. Thanks for your help

Sheila

 

[Marshall Barton]

I still say you have not defined the range of dates to use
in the total. You can not say that you want it over the
monthly rate and then say the dates should be ignored.

You mentioned something about a month count. Maybe you need
to add a group level for months.

 

[Guest]

Marshal, I don't think I can have explained this very well. I have clients
who make monthly support payments which may increase or decrease over time.
What I would like to see in the report is most recent information 1st hence
sort on date.

However, I would like the information presented to show a total for each
different monthly rate which may be over several months thus grouping and
totals are on Monthly Rate. I can sort the dates within each Monthly Rate but
can only sort the groups in ascending or descending order of the Monthly
Rate. Sample data below which I hope clarifies.
Rate per Month 85 No. of Payments 4
01/07/2005 255 3 months
01/06/2005 85 1 month
Rate per Month 90 No. of Payments 15
01/05/2005 270 3 months
01/02/2005 270 3 months
01/11/2004 270 3 months
01/08/2004 270 3 months
01/05/2004 270 3 months
Rate per Month 95 No. of Payments 1
01/08/2005 95 1 month
Your help is appreciated and I'm sorry if I am missing something obvious
here but I just can't see how to get the result I want

Sheila

I still say you have not defined the range of dates to use
in the total. You can not say that you want it over the
monthly rate and then say the dates should be ignored.

You mentioned something about a month count. Maybe you need
to add a group level for months.
--
Marsh
MVP [MS Access]

The total I want is for the Monthly Rate and I'm looking to count number of
months and sum total of payments. I can get it to do this no problem if I
take the Invoice Date out of the equation BUT then it sorts on the Monthly
Rate only and records in each rate are not sorted on the date. What I want is
to put the sort in but not have it treated as a group which is what seems to
be happening.

 

[Marshall Barton]

Marshal, I don't think I can have explained this very well. I have clients
who make monthly support payments which may increase or decrease over time.
What I would like to see in the report is most recent information 1st hence
sort on date.

However, I would like the information presented to show a total for each
different monthly rate which may be over several months thus grouping and
totals are on Monthly Rate. I can sort the dates within each Monthly Rate but
can only sort the groups in ascending or descending order of the Monthly
Rate. Sample data below which I hope clarifies.
Rate per Month 85 No. of Payments 4
01/07/2005 255 3 months
01/06/2005 85 1 month
Rate per Month 90 No. of Payments 15
01/05/2005 270 3 months
01/02/2005 270 3 months
01/11/2004 270 3 months
01/08/2004 270 3 months
01/05/2004 270 3 months
Rate per Month 95 No. of Payments 1
01/08/2005 95 1 month
Your help is appreciated and I'm sorry if I am missing something obvious
here but I just can't see how to get the result I want

I don't think you're missing something obvious. Nothing
about this is obvious ;-)

If your sample above is the hoped for output, then it looks
like you want to group on rate then sort on date. But you
said you tried that it wasn't what you wanted, so at this
point I guess I wondering how you do want the result to
appear??

Just a stab in the dark now, maybe you do not want to group
on the rate at all, how about using a subreport in the sales
category group footer to display the total for each rate???

 

[Guest]

Marshall, you're quite right what I want to do is to group on rate and sort
on date BUT I want the groups to also appear in descending date order. It's
like a sort on date then rate then date WITHIN the rate. As you see in the
sample I sent the last group really needs to be 1st because it is the latest
date. I'll try the sub report and see if that works - thanks for your time.

Sheila

 

[Marshall Barton]

Ahhh, you want the rates sorted by the latest date within
the group? Right? If so, forget the subreport idea.

Well now I see the confusing issue, a report can not sort on
a value that is calculated within the report. You need to
modify the report's record source query to calculate the
latest date for each rate. Something along these lines:

SELECT *, DMax("InvoiceDate", "table",
"MonthlyRate = " & MonthlyRate) As RateDate
FROM table
WHERE . . .

Now, the report can use the following Sorting and Grouping:
Group 1 – Customer
Group 2 – Type of Sale (Category)
Sort 3 – RateDate
Group 4 – Monthly Rate
Sort 5 – Invoice Date

 

[Guest]

That's fantastic Marshall, thanks for all your help and glad we got there in
the end!

Sheila

Ahhh, you want the rates sorted by the latest date within
the group? Right? If so, forget the subreport idea.

Well now I see the confusing issue, a report can not sort on
a value that is calculated within the report. You need to
modify the report's record source query to calculate the
latest date for each rate. Something along these lines:

SELECT *, DMax("InvoiceDate", "table",
"MonthlyRate = " & MonthlyRate) As RateDate
FROM table
WHERE . . .

Now, the report can use the following Sorting and Grouping:
Group 1 – Customer
Group 2 – Type of Sale (Category)
Sort 3 – RateDate
Group 4 – Monthly Rate
Sort 5 – Invoice Date
--
Marsh
MVP [MS Access]

Marshall, you're quite right what I want to do is to group on rate and sort
on date BUT I want the groups to also appear in descending date order. It's
like a sort on date then rate then date WITHIN the rate. As you see in the
sample I sent the last group really needs to be 1st because it is the latest
date. I'll try the sub report and see if that works - thanks for your time.