UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Create A Dynamic Sorting Method, Access 2016    
 
   
LLCadet
post Jul 9 2019, 03:42 PM
Post#1



Posts: 6
Joined: 22-May 19



Good afternoon everyone. I'm going to do the best I can to explain the data as it's given and as it's needed, so please bear with me.

I have a database (Access 2016) with a list of actions to complete. These actions are assigned to a resolution/statement. When a resolution is received, we use a form to populate a list of necessary actions for it as part of a datasheet subform; basically a transaction log. There are also revision/optional actions that can be inserted but do not prepopulate--They can be added to the subform at the end of the list.

I.E.

Resolution 4-04

1. Resolution(s) Received.
2. Proofread all resolutions to ensure descriptions match.
3. Resolution confirmed at 75 words or less (dept. #1).
4. Resolution confirmed at 75 words or less (dept. #2).
5. Typeset.
6. Proofread.
7. (Revision) Error found. Re-do Typeset.
Etc.

Now, the issue I'm having is that I somehow need a query to list them in numeric order as displayed UNTIL it reaches #7. Then, it needs to jump back to #5. I realize that I can create a field with a value for #7, giving it a value of 5, but how do I get that to display in that order?

1, 2, 3, 4, 5, 6, 7, 5, 6, etc.

VBA might be an option as well, but is it doable in SQL?

[Edit]Forgot to mention, these will be displayed on a report as well (hence the head-to-wall scenario I'm facing).[/Edit]
This post has been edited by LLCadet: Jul 9 2019, 03:44 PM
Go to the top of the page
 
theDBguy
post Jul 9 2019, 03:51 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,047
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

If your table only has 7 rows of data, then you cannot really show more than that, unless I'm missing something from your description. In other words, how can your sort go back to #5 after going through all seven items in the list, if the list only has 7 items in it? If you have more than 7 items in the list and you want them to be sorted a particular order, then if there's no inherent sorting order in the data, you may have to add a sorting field to force the custom sort you require.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LLCadet
post Jul 9 2019, 03:58 PM
Post#3



Posts: 6
Joined: 22-May 19



Heh, there are more than 7 rows smile.gif 19 total actions right now, with 4 of them revisions (meaning 15 are pre-populated). However, at certain points, a revision can send you back towards the beginning (think of it like Chutes and Ladders).

[Edit]Lemme try to provide additional clarity:

1. Resolution received from Communications Department
2. Proofread all resolutions to ensure they match
3. Resolution confirmed at 75 words or less (by Communications Dept)
4. Resolution confirmed at 75 words or less (by our Dept)
5. Typeset
6. Proofread the typeset
7. (Revision) Revision required for the typeset [Takes us back to 5]
8. Submit proof to the provider for approval of resolution via the Communications Dept
9. (Revision) Received a request from the provider for a correction [Takes us back to 5]
10. Received approval from the provider via the Communications Dept
11. Submit final English proof to translation vendor
12. Receive Spanish proof from translation vendor
13. Proofread Spanish proof from translation vendor
14. (Revision) Resubmit proof to translation vendor [Takes us back to 12]
15. Final English proof is ready for submission
16. Final Spanish proof is ready for submission

Everything not a revision is populated.[/Edit]
This post has been edited by LLCadet: Jul 9 2019, 04:06 PM
Go to the top of the page
 
theDBguy
post Jul 9 2019, 04:22 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,047
Joined: 19-June 07
From: SunnySandyEggo


So, it doesn't sound like you are asking for a sorting order, but more like a "step" sequence. For example, if there are 7 prepopulated tasks and you add another task that requires two previous tasks to be redone, you would then have the original 7 tasks, the new task as #8, and then the previous two tasks to be redone as #9 and #10. Correct? If so, we'll need the rules on how to determine which tasks need to be readded to the list when a revision is added at the end.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LLCadet
post Jul 9 2019, 05:37 PM
Post#5



Posts: 6
Joined: 22-May 19



Tasks are populated into a transaction table via a button click on a form (everything non-revision). The subform is in datasheet view and displays all the actions populated. It allows additions, so you may click on the action combobox of a new record and select the action/revision. A button is also on the form to allow a requery as needed. You select the appropriate necessary actions afterwards.

Say it populates 1-6, 8, 10-13, and 15-16 from my previous post. Now, at action #6, you find a typo. You insert Action #7 at the end of the list, then insert Action #5 and Action #6. Now it needs to be sorted in the order that the actions must be completed.

(Completed is another field on the table: True/False)

Are you recommending that we insert a sequence field into the transaction table? Basically, starting from 1 until the end of the number of actions but then systematically bumping up other numbers in the sequence to insert revisions?
Go to the top of the page
 
theDBguy
post Jul 9 2019, 08:27 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,047
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
Are you recommending that we insert a sequence field into the transaction table? Basically, starting from 1 until the end of the number of actions but then systematically bumping up other numbers in the sequence to insert revisions?
Yes, if I understand your requirements correctly. If you have a list of 20 or 30 tasks and they have to be listed in a certain order, and that way cannot be determined from existing data, then yes, you may have to add additional data to determine the sorting order.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post Jul 10 2019, 03:54 AM
Post#7


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


I do this by adding a "sortfield" to my query. I set the sortfield with a function which processes the rows according to the elements I want to sort, and then sort the query on that column.

so sortfield: sortme (dpartmentcode, regioncode, costcentrecode, etc)


Typically, my form will show all the sort elements, (eg department, region, cost centre etc) and the user will select the order of the elements for the sort.
The report/query will them process the records in accordance with this "calculated" sort.


I hope that makes sense.



PS: I share the same concerns as theDBGuy. If you have already sorted a query on fields 1 thru 7, it's hard to understand how sorting it on fields 1 thru 7, and then 5 again, can produce a different result to sorting on fields 1 thru 7.



--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
dale.fye
post Jul 10 2019, 08:15 AM
Post#8



Posts: 137
Joined: 28-March 18
From: Virginia


I would agree with dbGuy that you need to add another [sequence] field to the table, which allows you to insert items with pervious values (#5, #6) of task number, but which also allows you to insert those items anywhere in the process by adjusting subsequent [sequence] values and then inserting these two tasks with the missing [sequence] numbers.

Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
LLCadet
post Jul 10 2019, 09:45 AM
Post#9



Posts: 6
Joined: 22-May 19



Rock on everyone. I'll see what I can do and let you know if I run into any snags. Thanks.
Go to the top of the page
 
theDBguy
post Jul 10 2019, 09:52 AM
Post#10


Access Wiki and Forums Moderator
Posts: 76,047
Joined: 19-June 07
From: SunnySandyEggo


Hi. You're welcome. We're all happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 03:25 PM