I need a piece of code that can give me every possible combination for a situation. Here is the situation

A user has on his screen a text box for every hour of the day (24 text boxes). s/he enters into each box the total number of entities they requre on site in each hour.

ie.

00:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 |

1 1 2 2 3 3

I need to place in an array every possible start and finish time to have these entities on site.

ie.

Entity One: 00:00 - 05:00

Entity Two: 02:00 - 04:00

Entity Three: 04:00 - 05:00

Above would be one combination

Another may be to have 12 different entities all there for only one hour each.

Thank you in advance,

Quatro

For a given problem, for example00:00 | 01:00 | 02:00 | 03:00 | 04:00 |

1 1 2 3

The maximum number of entities any solution will have is the sum of the numbers needed (1+1+2+3) = 7, corresponding to the solution with each entity only appearing for one hour.

You can then build a boolean matrix

| 01:00 | 02:00 | 03:00 | 04:00

ent1

ent2

ent3

ent4

ent5

ent6

ent7

where each place in the matrix corresponds to an entity being present or not.

In this case, this would give you 7 (max. number of entities) * 24 (number of hours in the real problem) ^ 2 = 28,224 possible solutions. Quite a lot, but not impossible with Access.

Of these, only the ones where the sum of the column equals the desired amount of entities are feasible solutions.

For example:

| 01:00 | 02:00 | 03:00 | 04:00

ent1 1 0 0 1

ent2 0 1 0 1

ent3 0 0 1 1

ent4 0 0 1 0

ent5 0 0 0 0

ent6 0 0 0 0

ent7 0 0 0 0

--------------------------

1 1 2 3

would be a correct solution,

where

| 01:00 | 02:00 | 03:00 | 04:00

ent1 1 1 1 1

ent2 1 1 1 1

ent3 1 1 1 1

ent4 1 1 1 1

ent5 1 1 1 1

ent6 1 1 1 1

ent7 1 1 1 1

--------------------------

7 7 7 7

is not.

A stupid algorithm would be to generate all possible solutions, and then filter out the correct ones.

You can use backtracking to apply the filter criterium during the generation of the solutions, quiting the work on the current solution if the constraints are violated. The constraint would be when the column sum exceeds the needed amount. If in fact an entity may only be 'alive' once (contigiously), you can add this as a constraint as well. (See Believers question)

I know this is all theoretical talk, but maybe this can help you or some other programmer a little. Like you, I think Access is underestimated :-) I have a 4 GB Access database with 250,000,000 records in one of my tables for my Master's project...