Grouping Sorting Question - sorry for the dual post - I posted the last one in HTML format by mistak

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

I have a report that lists all our customers and the price quotes we gave
them. One customer usually has many different price quotes that we have
given them over the years. My report also lists the Quote Total next to the
Quote Number.

I want to be able to Group/Sort by showing all the quotes for the company
with the largest Quote Total first. Here is an example:

Company Quote # Quote Total
McDonalds 100 $999,999.00
McDonalds 12 $10,000.00
McDonalds 19 $5,000.00
McDonalds 112 $100.00
Wendys 17 $800,000.00
Wendys 51 $50,000.00
Burger King 23 $65,000.00
Popeyes 89 $42,000.00
Popeyes 200 $200.00


Anyone know how to do this? Can I do this straight from the report, or do I
have to build a special query for this?


Thanks,
Chris
 
Nelson said:
I have a report that lists all our customers and the price quotes we gave
them. One customer usually has many different price quotes that we have
given them over the years. My report also lists the Quote Total next to the
Quote Number.

I want to be able to Group/Sort by showing all the quotes for the company
with the largest Quote Total first. Here is an example:

Company Quote # Quote Total
McDonalds 100 $999,999.00
McDonalds 12 $10,000.00
McDonalds 19 $5,000.00
McDonalds 112 $100.00
Wendys 17 $800,000.00
Wendys 51 $50,000.00
Burger King 23 $65,000.00
Popeyes 89 $42,000.00
Popeyes 200 $200.00


Anyone know how to do this? Can I do this straight from the report, or do I
have to build a special query for this?
Click to expand...

You can not sort a report by a value calculated in the
report. So you do have to create a query for this,

The easiest way to approach this is to first create a totals
query that calculates the largest quote for each company.

Query: CalcMaxQuotes
SELECT company, Max(QuoteTotal) As MaxQuote
FROM something
GROUP BY company

Then your new query can Join that query in with whatever
you're using now:

SELECT somthing.*, CalcMaxQuotes.MaxQuote
FROM something INNER JOIN CalcMaxQuotes
ON something.company = CalcMaxQuotes.company

Now your report can group on the expresssion:
=Format(MaxQuote, "0000000000") & company
 
That did it. Thank you very much!

Chris


Marshall Barton said:
You can not sort a report by a value calculated in the
report. So you do have to create a query for this,

The easiest way to approach this is to first create a totals
query that calculates the largest quote for each company.

Query: CalcMaxQuotes
SELECT company, Max(QuoteTotal) As MaxQuote
FROM something
GROUP BY company

Then your new query can Join that query in with whatever
you're using now:

SELECT somthing.*, CalcMaxQuotes.MaxQuote
FROM something INNER JOIN CalcMaxQuotes
ON something.company = CalcMaxQuotes.company

Now your report can group on the expresssion:
=Format(MaxQuote, "0000000000") & company
Click to expand...
 
Back
Top