Sort a Group based upon a Subgroup value

[Jorist]

I have a report that delviers data in the following manner:

Company A
Item A 60
Item B 20
Item C 120
Company B
Item A 100
Item B 10
Item C 20
Company C
Item A 75
Item B 100
Item C 130

This report needs to have the data grouped so that the company's Item A,
Item B, and Item C remain with the comany. Further Items A - C need to be
sorted in their alphabetic order. However I need Company A, Company B and
Company C sorted based upon the value of Item A highest to lowest. As such,
the example above would result with the order of Company B, Company C, then
Company A with everything else being the same.


If anyone has any suggested solutions, I would greatly appreciate it. Thanks
in advance.

 

[John Spencer MVP]

What does the query look like for the report? Are you using a sub-report or
are you just using Sorting and Grouping?

Somehow you need to get the value of item A into every record of the main
report. Perhaps you can use DLookup (slow with lots of records, but easy to
implement).

SELECT Company, ItemName, ItemQuantity
, DLookup("ItemQuantity","SomeTable","ItemType = 'A' AND Company='" & Company
& "'") as SortOn
FROM SomeTable


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Do you mean the sort should look like this --
Company A Item A 60
Company C Item A 75
Company B Item A 100
Company B Item B 10
Company A Item B 20
Company C Item B 100
Company B Item C 20
Company A Item C 120
Company C Item C 130

 

[Jorist]

The new results should look like this --

Company B
Item A 100
Item B 10
Item C 20
Company C
Item A 75
Item B 100
Item C 130
Company A
Item A 60
Item B 20
Item C 120


This way, the value of each company's Item A determins the company's order
in the Company sort order.

 

[Jorist]

While the report at issue does use a sub-report, the data that we are looking
at here is not impacted by that sub-report. The sub-report simply provides a
dynamic listing of the paramaters that were used to develop this report.

This data is organized by using Sorting and Grouping.
Group on Company
Group on Item

To complicate the issue, it is possible that not every company will have an
Item A. In these cases they would then sort by Item B. If there were no Item
B then they would sort by Item C. If there is no Item A or B for a company,
then they will always have an Item C.

Thanks in advance for any help.

 

[KARL DEWEY]

Create this query substituting your table and field names --
SELECT Q.Company, (SELECT COUNT(*) FROM [Jorist] Q1
WHERE Q1.[Item] = Q.[Item] AND Q.[Item] = "A"
AND Q1.[QTY] > Q.[QTY])+1 AS High_Sort
FROM Jorist AS Q
WHERE (((Q.Item)="A"))
ORDER BY Q.QTY DESC;

Join it in your present query on Company.

In the report sort on High_Sort and Item.

 

[John Spencer MVP]

Could you post the query that is the source for the report? If you can then
someone can probably post the modification to the query.

The addition of grouping on A or B or C makes this a bit more complex. For
instance, if there is not Item A for a company then does the company go after
all the companies that do have an Item A. And do those that only have an Item
C go after all those that have Item A or Item B?

Probably you need a saved query something like the following that you join to
your current query to return three values.
SELECT Company
, Max(IIF(ITEM="A",Item,Null) as ItemA
, Max(IIF(ITEM="B",Item,Null) as ItemB
, Max(IIF(ITEM="C",Item,Null) as ItemC
FROM SomeTable
GROUP BY Company

Then in the Grouping dialog you need to add top level sorting using a
statement like
ItemA
and sort descending
Then additional levels for ItemB and ItemC

If you want to intermix you might use
=NZ(ItemA,Nz(ItemB,ItemC))

Or you might need to use a different type of query and return the TOP 1 value
for each company based on the Item type.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County