Show "Max" of "Sum" per group

[sehmke]

Hello people,

Subject lines are usually cryptic, so I'll try to explain my problem in a lot of detail - don't be scared by the length of it

My company has lots of articles grouped in families, stored in a table
Article sales per month is stored in a table.

table 1 (the one with the articles) is like this:

Family Article
F5200 A120
F5200 A250
G2350 A180
G2350 A450
etc.

So each family has most of the times multiple articles (members).

table 2 (the one with the sales) is like this:

Article Month Quantity
A120 500
A120 200
A120 100
A250 1000
A250 250
A450 300
A180 400
etc.

I created a Totals Query to give me the total sales per article:

Article SumOfQuantity
A120 800
A250 1250
A450 300
A180 400

Now I would like to know within each family which article is the best runner.

The query (queries) should result in a table like this:

Family Article
F5200 A250
G2350 A180

The actual total doesn't even have to show...

I've been breaking my brains on this. No success. Please help.

Thanks for your input.
S.

 

[antomack]

Working from what you currently have in place you can do as follows.

1) Create a query (I've called it FamMax) based on your Family Article table and your Totals Query. Link on article, create a group query with Family and Qty and take Max of quantity field. SQL similar to below
SELECT FamilyArticle.Family, Max(TotalQry.Sumofqty) AS Maxqty
FROM FamilyArticle INNER JOIN TotalQry ON FamilyArticle.Article = TotalQry.article
GROUP BY FamilyArticle.Family;

2) Now build a query using the query from 1, the Family Article table and the TotalQry. Link 1 to Family article on Family, TotalQry to Family article on Article and TotalQry to 1 on Qty to MaxQty. Add Family, Article and MaxQty fields. This will then give you the record(s) for each family with the article(s) that have the maximum sales quantity. If more than 1 article has the same max quantity then each article will show. If you only want to see one record for each family then change to a group query and take First or Last of Article.
SQL similar to below
SELECT FamilyArticle.Family, FamilyArticle.Article, FamMax.MaxOfqty
FROM (FamilyArticle INNER JOIN FamMax ON FamilyArticle.Family = Query14.Family) INNER JOIN TotalQry ON (FamMax.MaxQty = TotalQry.qty) AND (FamilyArticle.Article = TotalQry.article)
ORDER BY FamilyArticle.Family;

 

[sehmke]

Faaaantastic

Dear,

It worked perfectly. Thanks for your crystal clear explanation. If you're ever in Antwerp, I'll buy you a beer or two.

Greetings,
S.