Hello everyone,
I'm trying to create an MS Access 03 database to be used in optimizing flow for various tasks. The idea is I have a table with task names, duration of each task, and the name of a task that needs to be completed before the current task may commence.
A typical example would be:
AutoNumber / TaskName / Duration / GroupName
1 / Take out trash / 5 minutes / None
2 / Put in fresh bag / 1 minute / Group 2
3 / Watch TV / 30 minutes / None
4 / Eat Dinner / 20 minutes / None
I have a linked table (1 to many relationship) which has
TaskName / ContingentOn
which holds records such as:
Put in Fresh bag / Take out trash
Watch TV / Put in fresh bag
where task "putting in a fresh bag" may only be done once task "Take out trash" has been completed, and "Watch TV" can only be done once a fresh bag is put in.
Several tasks require that multiple previous tasks are complete before starting.
Here is where I need some help.
In order to handle the groups of items, my thought is to add the group as a task, then calculate the duration of the group of tasks. So, in the above example, I would simply add the time required to take out the trash to the time required to put in a fresh bag.
The trouble comes when multiple tasks can be completed at the same time. So, suppose you're packing for a trip and you have 5 bags (each requiring a differing amount of time to transport) to carry out to the car, and you have two people to carry them. How do you optimize which person carries what bag?
I would like to have Access put together all combinations of the two people moving the five bags, then test to find the smallest duration.
I am able to loop through my tables to extract duration values one by one, but I can't think of a way to come up with each of the combinations without putting them in by hand (which would be a real pain in the -ss... a small example of 5 objects with 2 people has 10 different combinations.)
Can anyone point me in the direction of some VBA code for calculating combinations of "n" objects in groups of "k"?
Any help would be much appreciated.
--Thanks!
9 3031 ADezii 8,834
Recognized Expert Expert
Hello everyone,
I'm trying to create an MS Access 03 database to be used in optimizing flow for various tasks. The idea is I have a table with task names, duration of each task, and the name of a task that needs to be completed before the current task may commence.
A typical example would be:
AutoNumber / TaskName / Duration / GroupName
1 / Take out trash / 5 minutes / None
2 / Put in fresh bag / 1 minute / Group 2
3 / Watch TV / 30 minutes / None
4 / Eat Dinner / 20 minutes / None
I have a linked table (1 to many relationship) which has
TaskName / ContingentOn
which holds records such as:
Put in Fresh bag / Take out trash
Watch TV / Put in fresh bag
where task "putting in a fresh bag" may only be done once task "Take out trash" has been completed, and "Watch TV" can only be done once a fresh bag is put in.
Several tasks require that multiple previous tasks are complete before starting.
Here is where I need some help.
In order to handle the groups of items, my thought is to add the group as a task, then calculate the duration of the group of tasks. So, in the above example, I would simply add the time required to take out the trash to the time required to put in a fresh bag.
The trouble comes when multiple tasks can be completed at the same time. So, suppose you're packing for a trip and you have 5 bags (each requiring a differing amount of time to transport) to carry out to the car, and you have two people to carry them. How do you optimize which person carries what bag?
I would like to have Access put together all combinations of the two people moving the five bags, then test to find the smallest duration.
I am able to loop through my tables to extract duration values one by one, but I can't think of a way to come up with each of the combinations without putting them in by hand (which would be a real pain in the -ss... a small example of 5 objects with 2 people has 10 different combinations.)
Can anyone point me in the direction of some VBA code for calculating combinations of "n" objects in groups of "k"?
Any help would be much appreciated.
--Thanks!
I'm not sure that I understand your request exactly, but it seems like you are referring to Nested Loops using Arrays as in: -
' 5 Objects - 7 People
-
-
Dim astrObjects(1 To 5) As String, intObjectCounter As Integer
-
Dim astrPeople(1 To 7) As String, intPeopleCounter As Integer
-
-
For intObjectCounter = 1 To UBound(astrObjects)
-
For intPeopleCounter = 1 To UBound(astrPeople)
-
Debug.Print "Object#" & Str(intObjectCounter) & " <==> Person#" & Str(intPeopleCounter)
-
Next
-
Next
OUTPUT: -
Object# 1 <==> Person# 1
-
Object# 1 <==> Person# 2
-
Object# 1 <==> Person# 3
-
Object# 1 <==> Person# 4
-
Object# 1 <==> Person# 5
-
Object# 1 <==> Person# 6
-
Object# 1 <==> Person# 7
-
Object# 2 <==> Person# 1
-
Object# 2 <==> Person# 2
-
Object# 2 <==> Person# 3
-
Object# 2 <==> Person# 4
-
Object# 2 <==> Person# 5
-
Object# 2 <==> Person# 6
-
Object# 2 <==> Person# 7
-
Object# 3 <==> Person# 1
-
Object# 3 <==> Person# 2
-
Object# 3 <==> Person# 3
-
Object# 3 <==> Person# 4
-
Object# 3 <==> Person# 5
-
Object# 3 <==> Person# 6
-
Object# 3 <==> Person# 7
-
Object# 4 <==> Person# 1
-
Object# 4 <==> Person# 2
-
Object# 4 <==> Person# 3
-
Object# 4 <==> Person# 4
-
Object# 4 <==> Person# 5
-
Object# 4 <==> Person# 6
-
Object# 4 <==> Person# 7
-
Object# 5 <==> Person# 1
-
Object# 5 <==> Person# 2
-
Object# 5 <==> Person# 3
-
Object# 5 <==> Person# 4
-
Object# 5 <==> Person# 5
-
Object# 5 <==> Person# 6
-
Object# 5 <==> Person# 7
Thanks for the reply, ADezii,
I created code similar to yours to do exactly what you have there -- that is, cycle through each record within each grouping (though, your code seems more elegant than mine...) but the problem I'm having is basically this.
Given 5 values: {1,2,3,4,5}, taken 2 at a time (A and B), how to I get out all the orders of those numbers? That is, I want the output of:
A: 1 2 3 4
B: 5
A: 1 2 3
B: 4 5
A: 1 2
B: 3 4 5
A: 1
B: 2 3 4 5
A: 1 3 4 5
B: 2
A: 1 3 4
B: 2 5
A: 1 3
B: 2 4 5
A: 1 2 4 5
B: 3
A: 1 2 4
B: 3 5
A: 1 2 3 5
B: 4
The idea is that there is no difference between A and B ( so 1,2,3 :: 4, 5 is the same as 4,5 :: 1, 2, 3) But every combination of those 5 numbers is divided among A and B.
Any help would be much appreciated.
Thanks!
ADezii 8,834
Recognized Expert Expert
Thanks for the reply, ADezii,
I created code similar to yours to do exactly what you have there -- that is, cycle through each record within each grouping (though, your code seems more elegant than mine...) but the problem I'm having is basically this.
Given 5 values: {1,2,3,4,5}, taken 2 at a time (A and B), how to I get out all the orders of those numbers? That is, I want the output of:
A: 1 2 3 4
B: 5
A: 1 2 3
B: 4 5
A: 1 2
B: 3 4 5
A: 1
B: 2 3 4 5
A: 1 3 4 5
B: 2
A: 1 3 4
B: 2 5
A: 1 3
B: 2 4 5
A: 1 2 4 5
B: 3
A: 1 2 4
B: 3 5
A: 1 2 3 5
B: 4
The idea is that there is no difference between A and B ( so 1,2,3 :: 4, 5 is the same as 4,5 :: 1, 2, 3) But every combination of those 5 numbers is divided among A and B.
Any help would be much appreciated.
Thanks!
I'll get back to you on this and see if I can come up with something.
I'll get back to you on this and see if I can come up with something.
Thanks! I've been thinking about it, and it may turn out to be easier to use a bunch of completion flags (one for each event) and just go through the full set of discrete time intervals. So at minute 5 after taking out the trash has been complete, it sets flag "flg_taking_out _the_trash" to 1, which then allows an if statement starting the next task to begin.
If it were to check tasks by order of decreasing duration, I believe it would optimize it fairly efficiently, if perhaps not perfectly.
Thoughts?
What about Task, Status, Person?
If you have 5 tasks to be acomplished but not assigned to a particular person
Beginning State:
Task_Status_Per s
01_00_00
02_00_00
03_00_00
04_00_00
05_00_00
In progress:
Task_Status_Per s
01_01_0A
02_01_0B
03_00_00
04_00_00
05_00_00
Completed:
Task_Status_Per s
01_01_0A
02_01_0B
03_01_0A
04_01_0A
05_01_0B
Loop thru each task and check status. Enable following task if previous is complete. Each time capturing who accomplished.
This is similar logic to game programming for multiplayer team tasks.
You could add a task group to the string and group status if you don't want another group to begin before the previous one is complete.
Just a thought here - Some tasks don't need to be done in order (Wash Dishes, Take Out Trash) but both need to be done before Watch TV can occur.
What about Task, Status, Person?
If you have 5 tasks to be acomplished but not assigned to a particular person
Beginning State:
Task_Status_Per s
01_00_00
02_00_00
03_00_00
04_00_00
05_00_00
In progress:
Task_Status_Per s
01_01_0A
02_01_0B
03_00_00
04_00_00
05_00_00
Completed:
Task_Status_Per s
01_01_0A
02_01_0B
03_01_0A
04_01_0A
05_01_0B
Loop thru each task and check status. Enable following task if previous is complete. Each time capturing who accomplished.
This is similar logic to game programming for multiplayer team tasks.
You could add a task group to the string and group status if you don't want another group to begin before the previous one is complete.
Just a thought here - Some tasks don't need to be done in order (Wash Dishes, Take Out Trash) but both need to be done before Watch TV can occur.
Hi jaxjagfan, thanks for responding.
I want to make sure I understand what you wrote above... You're basically saying to do what I proposed, which is have a flag change state when a task becomes complete. Then the code loops through again and re-evaluates whether each task may be completed. Am I about right?
I like it, and if ADezii doesn't have time/inclination to get back to me, I may just end up going that route. Thanks for the input.
The only down side to this setup is it doesn't truely optimize the order in which tasks are complete. Suppose you have 4 tasks, and two people to do them. The task lengths are (in minutes, say) 10, 9,2,1. If you simply start the next longest task as soon as the current one finishes, the two people would complete:
A: 10, 2
B: 9, 1
So it would take 12 minutes to complete all four tasks. However if the tasks were completed this way:
A: 10, 1
B: 9, 2
it would only take 11 minutes to do the task (which is ideal).
Can anyone think of a way to do this without checking each combination of user/tasks?
Hi jaxjagfan, thanks for responding.
I want to make sure I understand what you wrote above... You're basically saying to do what I proposed, which is have a flag change state when a task becomes complete. Then the code loops through again and re-evaluates whether each task may be completed. Am I about right?
I like it, and if ADezii doesn't have time/inclination to get back to me, I may just end up going that route. Thanks for the input.
The only down side to this setup is it doesn't truely optimize the order in which tasks are complete. Suppose you have 4 tasks, and two people to do them. The task lengths are (in minutes, say) 10, 9,2,1. If you simply start the next longest task as soon as the current one finishes, the two people would complete:
A: 10, 2
B: 9, 1
So it would take 12 minutes to complete all four tasks. However if the tasks were completed this way:
A: 10, 1
B: 9, 2
it would only take 11 minutes to do the task (which is ideal).
Can anyone think of a way to do this without checking each combination of user/tasks?
Yea - thats sort of what I was thinking - Close enough anyhow.
You could end up with
A:10, 9
B: 1, 2
The way to get around that would be to have A and B available for task assigments. If A:10 is in progress then assign B:9. You may still end up with an A:2 but that will depend upon the task dependencies and ACTUAL completion times (what if garbage bag broke).
You also need to randomize the starting assignment so it will not always be A:10.
A & B may help each other out when idle. This would cut time required in half or double power-hours assigned (depending on how you look at it).
Yea - thats sort of what I was thinking - Close enough anyhow.
You could end up with
A:10, 9
B: 1, 2
The way to get around that would be to have A and B available for task assigments. If A:10 is in progress then assign B:9. You may still end up with an A:2 but that will depend upon the task dependencies and ACTUAL completion times (what if garbage bag broke).
You also need to randomize the starting assignment so it will not always be A:10.
A & B may help each other out when idle. This would cut time required in half or double power-hours assigned (depending on how you look at it).
Good thoughts, jaxjagfan, and thanks for the RE.
If I were to do this method I would have it search for the longest duration task to assign to the next available person in order to avoid it assigning one person the longest running tasks. Meaning, whenever a person completed a task, they would be assigned the next longest duration task. That way, overall, the tasks will come out to be all done at about the same time. Hopefully. Maybe...
I agree that in the example I gave A could help out B or vise versa if their assigned task were complete, however in my actual application this is not the case. Space and equipment considerations prohibit this. (For instance, if the task is loading a pallet using a fork lift, the task wouldn't go any faster with two forklifts.)
I do appreciate the thoughts/ideas though. Thank you.
I'll get back to you on this and see if I can come up with something.
Any additional thoughts, ADezii?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jim Hubbard |
last post by:
Does anyone have any code for doing combinations in VB.net or visual
basic ? I'd like to
be able to get an array of combinations (not permutations) of words from a
base group of words while specifying the number of unique words to be used
in the result set.
For instance if I have the set {cat dog fish snake} I'd like to be able
to get back a result array of all possible combinations (not permutations)
of X number of words from my base...
|
by: rbt |
last post by:
Say I have a list that has 3 letters in it:
I want to print all the possible 4 digit combinations of those 3
letters:
4^3 = 64
aaaa
|
by: Micheal Artindale |
last post by:
I am looking at creating list of letter combinations.
letters a-h
6 letters per combination
letter can repeat its self in the combination, but not next to its self
and, a series of letter can not repeat in the combinations.
So, ababab, abcabc would not be alowed but, abcbca, abcbac... would all be
alowed.
|
by: Thomas Cameron |
last post by:
Hello all,
I have an array of values which I would like to generate all
possible
combinations for. And example would be the values "a,b,c". This would
produce the following:
a
ab
abc
ac
|
by: Thomas Cameron |
last post by:
Hello all,
I have an array of values which I would like to generate all possible
combinations for. And example would be the values "a,b,c". This would
produce the following:
a
ab
abc
ac
b
| |
by: Ryan |
last post by:
I've been trying to find an algorithm that will output all of the possible
combinations of items in an array. I know how to find the number of
combinations for each set using
nCr=n!/(r!(n-r)!)
set being possible combinations of r objects from a group of n objects.
e.g. n=5 r=3 using A,B,C,D,E as items
ABC ABD ABE ACD ACE
|
by: brendan |
last post by:
Hi,
I'm not at all competent in ms-sql, nor vb, as we work in Oracle and Mysql
.... however, we need to port a couple of db queries to ms-access (2000) and
I'm having a heck of a time trying to make it work.
The immediate requirement is obtaining permutations of a relational
recordset
Specifically each record row will produce up to 3 letters and I need to
display all the combinations of L1, L2, L3, NULL for that row and avoid
|
by: The Frog |
last post by:
Hello everyone,
I am trying to find way of writing an SQL query that can produce
missing record combinations across a many to many type setup in
Access.
The three tables used are as follows:
Table1:
Field 1
|
by: Bails |
last post by:
Hi all I have a theory for a lotto system and need help on how to code
it.
I want to create 1 massive database with EVERY combination of numbers
possible in a given lotto system, then remove all the numbers in a
sequence.
For example, in Australia our lotto system has 45 balls and you need
to pick 6 numbers to win the jackpot.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |