Return most frequent product category

[Morten]

Hi,

I have a table with contract numbers (Kontraktnr), Item numbers and product category (UNSSPC_kode). The UNSPSC hierarchy is based on four levels: 1. Segment, 2. Family, 3. Class and 4. Commodity.

I need to find the most frequent product category on the lowest level (commodity level), grouped by contract number. But if the result is two or more product categories with the same count, then I would like to get the most frequent product category on the next level which is Class level and so on.

In my example I have two most frequent product categories on contract number 4700000001. The categories: 42131601 and 42131501 returns 5. So the right product category for us would be the Class level 42131600, because there is 6 contract items with this code.

Is is possible to make a query to return the most frequent product category based on four columns? And could anyone help me with the query design?

Best regards

 

[plog]

You provided sample starting data. Now can you show me what you expect returned based on that data? Don't try to re-explain it, show me what data you expect to be returned when you use your Kontrakter table.

 

[Morten]

You provided sample starting data. Now can you show me what you expect returned based on that data? Don't try to re-explain it, show me what data you expect to be returned when you use your Kontrakter table.

Thank you plog for your reply.

I would expect a result like this:

ID Kontraktnr Assigned_UNSPSC
1 4700000001 42131600
2 4700000002 41101201

As you can see contract 4700000001 has a UNSPSC code assigned at a higher level in the UNSPSC hierarchy.

I hope you understand what I'm trying to achieve.

Thank you for your time.

Best regards
Morten

 

[plog]

You've lost me in your terms (Segment, Family, Class, Commodity, product category, code). Only a few of which resemble field names.

Can you repost your description using only field names? I believe we are working with data from the 'qryUnspscGrupper' query. Using the field names in there, please describe what you want to occur.

 

[RogerCooper]

You are not going to be able to do this in a single query. Essentially you want the frequency of higher level categories to tie-break the lower level categories.

First you need a query that derives each of the higher level categories from the commodity code.

You would then need to have a query for the frequency of at each level.

Finally you would need to have a query for each level that creates an adjusted frequency combining the frequency at each level with some fraction of the higher level frequencies (perhaps *.01 of the higher level).

This requires 9 different queries. Or simply change the requirement and tie-break using alphabetical order.

 

[Morten]

You are not going to be able to do this in a single query. Essentially you want the frequency of higher level categories to tie-break the lower level categories.

First you need a query that derives each of the higher level categories from the commodity code.

You would then need to have a query for the frequency of at each level.

Finally you would need to have a query for each level that creates an adjusted frequency combining the frequency at each level with some fraction of the higher level frequencies (perhaps *.01 of the higher level).

This requires 9 different queries. Or simply change the requirement and tie-break using alphabetical order.

Hi Roger,
You're on the right track. It seems a little bit complicated to solve this issue in Access, if I am going to need 9 queries.

Best regards
Morten

 

[Morten]

You've lost me in your terms (Segment, Family, Class, Commodity, product category, code). Only a few of which resemble field names.

Can you repost your description using only field names? I believe we are working with data from the 'qryUnspscGrupper' query. Using the field names in there, please describe what you want to occur.

Hi again,

You're right. I need to work with the data from 'qryUnspscGrupper'.

I first need to check the UNSPSC frequency in the field name 'UNSPSC_code'. If one code is more frequent than another, then this will be the assigned UNSPSC code for this contract.

But if two or more codes have the same frequency, then I must check the frequency level in the next field 'UNSPSC_klasse'. If one code is more frequent than another, then this will be the assigned UNSPSC code for this contract.

But if two or more codes have the same frequency, then I must check the frequency level in the next field 'UNSPSC_familie'. And so on.

Best regards
Morten

 

[plog]

Roger's correct, this will take a ton of queries. My advice would be to use VBA Recordsets to loop through all the data to determine this.

 

[RogerCooper]

My advice is to change your requirement for tie-breaking. I have a similar issue. I need to assign a default ship via to customers. I use the most used ship via for the past year for each customer. If there is a tie, I just use the first in alphabetical order. No one cares.