Grouping/Sorting Question

  • 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
 
Chris,

Sort/Group the report on the Company field (Ascending)
followed by the Quote Total Field (Descending).

You can access the Sorting/Grouping options by clicking on
the Sorting/Grouping icon on the toolbar or while in the
report design view select View, Sorting and Grouping.

You can also set the company group header and footer here
if you need to display totals for each group.

Hope this helped.

Xcelsoft
-----Original Message-----
I have a report that lists all our customers and the
Click to expand...
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
Click to expand...
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
Click to expand...
the report, or do I have to build a special query for this?
 
That will not work because I want to show "the largest Quote Total first"
followed by all the other quotes given for that company. I believe your
suggestion would list the companies in alphabetical order first and then
show the largest quote total within that company first which will not help.
Your suggestion would cause my example below to read like this:
Company Quote # Quote Total
Burger King 23 $65,000.00
McDonalds 100 $999,999.00
McDonalds 12 $10,000.00
McDonalds 19 $5,000.00
McDonalds 112 $100.00
Popeyes 89 $42,000.00
Popeyes 200 $200.00
Wendys 17 $800,000.00
Wendys 51 $50,000.00

That is no good. Since McDonalds has the highest Quote Total, they should
be listed first w/all their quotes. Thanks for the response though, anyone
else have any suggestions? Can I do this straight from the report, or do I
have to build a special query for this?


Thanks,
Chris
 
Chris,

Sorry I overlooked the protion of your original question
that asked " for the company with the largest Quote Total
first."

You can accomplish this pretty easily by using two queries
and the report sorting/grouping as explained below.

Query1 will have two fields:

1. Company - Group on this field.

2. High_Quote: Quote_Total - Create this field in the
query and set the Total line for this field to First and
sort Descending.

This will produce the unique list of highest quote totals
in descending order by company that looks like below:

McDonalds 999999
Wendys 800000
Burger King 65000
Popeyes 42000

Query2 will be a combination of Query1 and the original
table the contains the data joined on the compay
field "where the joined fields from both tables are equal"
and will have the following fields.

Company - From Query1

HighQuote_Company: [High_Quote] & [Company] - Sorted
Descending. Create this field in the query, this will put
the high quote total on each company's transaction
respectfully.

Quote_Total - Sorted Descending from your table.

Quote_Number - from your table.

If you run this query you will get the following:

Company: HighQuote_Company: Quote_No: Quote_Total:
McDonalds 999999McDonalds 100 999999
McDonalds 999999McDonalds 12 10000
McDonalds 999999McDonalds 19 5000
McDonalds 999999McDonalds 112 100
Wendys 800000Wendys 17 800000
Wendys 800000Wendys 51 50000
Burger King 65000Burger King 23 65000
Popeyes 42000Popeyes 89 42000
Popeyes 42000Popeyes 200 200

When you format the report you will Sort/Group on the
HighQuote_Company field "Descending" followed by the
Quote_Total field "Descending".

I have this setup in a test D/B and it works just fine.

Note: You do not have to display the HighQuote_Company
combination field on the report as this is used for
sorting/grouping only.

Let me know if you need further assistance.

XcelSoft
 
Back
Top