Combinations in Access

[nickvans]

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!

[ADezii]

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

[nickvans]

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]

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.

[nickvans]

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?

[jaxjagfan]

What about Task, Status, Person?

If you have 5 tasks to be acomplished but not assigned to a particular person

Beginning State:
Task_Status_Pers
01_00_00
02_00_00
03_00_00
04_00_00
05_00_00

In progress:
Task_Status_Pers
01_01_0A
02_01_0B
03_00_00
04_00_00
05_00_00

Completed:
Task_Status_Pers
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.

[nickvans]

What about Task, Status, Person?

If you have 5 tasks to be acomplished but not assigned to a particular person

Beginning State:
Task_Status_Pers
01_00_00
02_00_00
03_00_00
04_00_00
05_00_00

In progress:
Task_Status_Pers
01_01_0A
02_01_0B
03_00_00
04_00_00
05_00_00

Completed:
Task_Status_Pers
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?

[jaxjagfan]

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).

[nickvans]

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.

[nickvans]

I'll get back to you on this and see if I can come up with something.

Any additional thoughts, ADezii?