Some kind of decizional descending sort...

[Ebadin]

Hi,
Here is my situation:
I have a table with 3 fields: category, person and sum.
What I need it's a group on category and after that an descending sort on sum (this way I have the maximum as the first value per each category).

Well you'll probably say that it's a simple select, well not exactly, here is the tricky part: I can't have the same person consecutive per category. If it happens, the next lower sum line should switch with it.

What do you think?
Thanks

 

[namliam]

Could you elaborate please? Like an example? I dont quite get your question.... I am sure its doable... but dont quite understand what you exactly want.

Maybe upload a small DB with some sample data?

 

[Ebadin]

Thanks for answering... I attached a sample DB and a screenshot from a report that it's also in the DB.

On the screenshot I highlighted the problem areas.
What I need it's actualy for the blue cells to get between the 2 values in the red cells... I don't want to have 2 persons with the same name, one after another, yet the sum value must be descending per category...

hope this time it was better

 

[DCrake]

Do you want james to appear twice in the report?

if not then use First()

Also do not have a field call SUM as this is a reserved word

 

[namliam]

So yeah, what Mr Crake said... You do want James to show up twice???
Or show him once with a total of 57? Or an average of 28.5?

If you want him twice but the second time below Tom, what would be your logic? Why do that? Etc.

Maybe a altered GIF with what you would like to see would help?

 

[Ebadin]

@DCrake: yes i want all the values; the mdf I sent it was just simplified
@namliam: I'm sorry about your sister in law... just read about it... terrible;

@All: The logic it's not that weird. Let me brief about the purpose of this.
It's for risk analysis. So I have a couple of people identify risks, then grade them (the grade it's the sum column) and assign a category for the risks.

What I need it's a table/view in which I should have grades (sums) sorted descending per category but I can't have 2 risk identified by the same person in a sequence (the red cells in the attachemnt). In risk analysis "one" can be subjective so it's better to have an global look. Maybe "James" exagerates with grading the risks and that may screw my analysis.

So, bottomline, everytime, in a category, I have 2 rows with risks graded by the same person (red cells), the fallowing lower row (blue cells) should be between the prior 2.

I know it's preaty confusing, but it makes sense, maybe my english it's not that good ... I am tring to stay away from VBA and hopped it can be done in a query.

Thanks

PS: I attached an AVI file (changed extension to TXT so I can upload)

 

[Rabbie]

It looks like it will be quite a lot of work. VBA is one solution although it might be worth looking at Subqueries and aggregate queries if you don't want to go down the VBA route. It would be helpful if you could post a mock-up of what you would like the end result to be.

 

[namliam]

So instead of
Person 1 Cat A 30
Person 1 Cat B 27
Person 3 Cat C 25

You want
Person 1 Cat A 30
Person 3 Cat C 25
Person 1 Cat B 27

Or something like that? Or should the 27 even change to 24???

 

[Ebadin]

So instead of
Person 1 Cat A 30
Person 1 Cat B 27
Person 3 Cat C 25

You want
Person 1 Cat A 30
Person 3 Cat C 25
Person 1 Cat B 27

Or something like that? Or should the 27 even change to 24???

You kinda nailed it . The data should be grouped by category.
No, value shouldn't change of course... The line's integrity must be kept. Also the descending order should be kept for the non duplicate values. I may even get 3 times, 4 times the same person in a row. Tricky!
I eddited my previous post so I added a small avi (i changed ext. to txt so I can upload)

Categ A | Person 1 | Risk AA | 30
Categ A | Person 1 | Risk AF | 27
Categ A | Person 3 | Risk AG | 25
Categ A | Person 2 | Risk AE | 22

becomes:

Categ A | Person 1 | Risk AA | 30
Categ A | Person 3 | Risk AG | 25
Categ A | Person 1 | Risk AF | 27
Categ A | Person 2 | Risk AE | 22

 

[namliam]

This isnt exactly easy to do... I am thinking you have to find another column that you have a UDF to calculate it... then use that column to sort on.

Definatly you will need some VBA coding...

 

[Ebadin]

This isnt exactly easy to do... I am thinking you have to find another column that you have a UDF to calculate it... then use that column to sort on.

Definatly you will need some VBA coding...

Yeah... I was afraid of that... I think I am gonna just do the sorting and grouping and then in vb I will check the duplicates and perform the swich.
Guess I have to brush my dusty VB knowlege...

Thanks a lot guys!

 

[gemma-the-husky]

in order to do this in access, you have to be able to sort the data in your required sort order.

now it seems that what you are tring to do is arbitrarily change the order because of a set of circumstances

so what if you get 3 risks assessed by the same guy together?
eg 111221

what if bumping up the next assessment means your first assessment fails again
112122

what if on a given day, he is the ONLY guy to assess any risks
111111

and note that if one guy has 2 more than the others put together, you cant avoid having two of his together
--------

anyway, to try and do this i think you need to add another field to your table, called risksequence if you like , and iterate the record set with vba, trying to find an algorithm that will exmaine every record in turn, and set an appropriate sort sequence.

bubble sorting is the easiest algorithm to understand - effectively you bubble sort one item at a time - so effectively you place the items in sequence by examining all the items and deciding which one goes first, then which one goes second etc (the items bubble up to the top) - now a bubble sort needs in the order of n^2 (n-squared) comparisons, so for a lot of rows, it becomes inefficient.

hence people then invented better sorting methods, generally based on sorting subgroups of the whole set, and then interleaving the subgroups. These are more linear (ie every item is NOT compared with every other) and are therefore more efficient for bigger datasets.)

but I think your algorithm wont be easy at all, because in order to allocate the sequence for an item, you need to know the characteristics of several items both above and below the selected item.

 

[Ebadin]

You are preaty much right... except this scenario, there will never be just one person

what if on a given day, he is the ONLY guy to assess any risks
111111
--------

with the vba I got stocked to 122253 (like you said)... if I have more then 2 values, well I'm in deep s***.

thanks for the perspective I was kinda blinded by the duplicates and I missed the other scenarios with more than 2 in a sequence...

thanks


Edit:
Wait..... I've just got an ideea: What if i count the distinct persons (let's say n) and split the table in n tables, per person, sort them descending on sum, and then take the first one of each table.... something like this:

Initial table:
Categ A | Person 1 | Risk AA | 30
Categ A | Person 1 | Risk AF | 27
Categ A | Person 3 | Risk AY | 26
Categ A | Person 3 | Risk AG | 25
Categ A | Person 2 | Risk AE | 22

Table1:
Categ A| Person 1 | Risk AA | 30
Categ A| Person 1 | Risk AF | 27

Table2:
Categ A| Person 3 | Risk AY | 26
Categ A| Person 3 | Risk AG | 25

Table3:
Categ A| Person 2 | Risk AE | 22

Now all you need to know it's the order in which you should read the tables:
1st ROW Table1 Table2 Table 3
2nd ROW Table1 Table2 Table 3
---------------------------------------

I think I got it.

 

[Ebadin]

Nevermind... stupid ideea...
Still open to suggestions

 

[namliam]

What you have is a monster, that needs to be tamed using some VBA.... Probably run your sum query store that result in a temp table with a blank 'ordering' field.

First fill this field in the 'normal' order using VBA, then 'reorder' this field to make things be as you want them to be...

It is definatly not funny