Table Allocation

[Muneer Mikel]

Does anybody know of a code that will assign table numbers (10 seats
per table) randomly for people attanding two events based on these
conditions:

1- All related persons in one family sit on the same table
2- People who sit on table (#65 for example) in event 1, shouldn't sit
on the same table in the event 2
3- Families from the same city shouldn't sit together

I don't even know of a way to approch this, not even something to start
from, and I don't know coding in VBA!

Thank you

[jimfortune]

Muneer Mikel wrote:

Does anybody know of a code that will assign table numbers (10 seats
per table) randomly for people attanding two events based on these
conditions:

1- All related persons in one family sit on the same table
2- People who sit on table (#65 for example) in event 1, shouldn't sit on the same table in the event 2
3- Families from the same city shouldn't sit together

I don't even know of a way to approch this, not even something to start from, and I don't know coding in VBA!

Thank you

I have two observations.

The first is that your conditions need more thought before they can be
implemented. For instance, what if eleven people are related? What if
three groups of greater than five related people have to sit at two
tables? Do you need to do optimization? What if the situation where
two families from the same city must sit together arises?

The second observation is that VBA coding will almost certainly be
required to solve these kinds of problems. The conditions you have are
similar to conditions I've run into with tournament and league
software. In a tournament you'd like to keep people from the same team
or league or state from meeting for as long as possible. Generating
the set of optimal solutions gets a little tricky. In a league with
x(j) pool tables available and y(j) teams for site j you want to create
league schedules that involve roughly the same number of home and away
matches. Since most leagues are handicapped thereby giving teams with
a stronger schedule early on an advantage it is also advantageous to
use power rankings to balance the schedule so that the strength of the
schedule does not vary much. For example, you could play the strongest
team one week and then the weakest team the next. Considerations such
as these are usually too much for an SQL-only solution. You'd also
want to determine if the conditions are so tight that no solution
exists. There may be other kinds of constraints also.

James A. Fortune

[Muneer Mikel]

Hi James,
Thank you for your reply.

Each group contains two people (husband and wife), and very few will
have one guest also. For now, lets assume each group will have only two
people. The purpose of seating families from different cities on one
table is to get them to know each other, assuming the families from the
same city know each other already because they belong to the same club.

I agree that I need to code in VBA. I'm a programmer, and I have been
able to manipulate some programs written in VBA to work the way I
wanted. However, I can't start coding in VBA from scratch. I need to
see a code written to solve a situation similar to the one that I have,
and then I could start modifying it and make it work. I can't even
think of a basic way to randomly assign table numbers for people in
general attending an event.

Thank you

[jimfortune]

I'll try to outline a way to attack this problem.

tblIndividuals
IndividualID PK
LastName Text
....

tblEventTables
EventTableID PK
EventTableName Text
EventID Long
SeatsAvailable Integer

1 One 1 10
2 Two 1 10
3 Three 1 10
4 Four 1 10
5 Five 1 10
6 Six 1 10
7 Seven 1 10
8 One 2 10
9 Two 2 10
10 Three 2 10
11 Four 2 10
12 Five 2 10
13 Six 2 10

Note: Table Seven will not be available for event 2. Sorry.

tblGroupsToReserve
GRID PK
IndividualID FK
GroupID FK
CityID FK
EventID FK

1 1 1 1
2 1 1 1
3 2 1 1
4 2 1 1
5 3 1 1
6 3 1 1
7 3 1 1
8 4 2 1
9 4 2 1
10 5 2 1
11 5 2 1
12 5 2 1
13 1 1 2
....

Each time a group is added to tblGroupsToReserve (perhaps using a
subform to list the people in the group) using a form that hopefully
checks for remaining SeatsAvailable, a new record is created in
tblReservations using those lines from tblGroupsToReserve.

tblReservations
ReservationID PK
EventID FK
EventTableID FK
GroupID FK to a non-PK
CityID FK (for info only)
SeatsReserved Integer (for info only)

1 1 x 1 1 2
2 1 x 2 1 2
3 1 x 3 1 3
4 1 x 4 2 2
5 1 x 5 2 3
6 2 x 1 1 2
7 2 x 2 1 2
8 2 x 3 1 3
9 2 x 4 2 2
10 2 x 5 2 3

where x is the EventTableID that gets assigned with the reservation.
When the reservation is made the SeatsAvailable field in tblEventTables
is decremented by the number of people in the group. Note that a group
for event 1 with two people can have guests for event 2 with their own
line in tblGroupsToReserve. If a reservation is cancelled be sure to
add the seats available back into tblEventTables. tblEventTables
records can be generated by a form also if lots of events are
contemplated. You can use this incremental approach to check for
information about who's already at the tables to give you a sense of
the problem before diving into the optimal solution. For the optimal
solution you can fill tblReservations with EventTableID blank (using
total event seats available to check for availability) and have VBA do
the analysis on the final table to assign EventTableID's. For the
incremental approach a way to do randomization would be to find out
which physical tables are available and use the Rnd function (after
using Randomize) to select which physical table to assign for the
reservation. I put this idea together quickly so I could probably do
better than this but it should get you thinking about the problem in
your head where it's really easy to make design changes as opposed to
changing the design after you realize that what you designed and coded
won't handle everything.

James A. Fortune

[jimfortune]

ji********@compumarc.com wrote:

tblGroupsToReserve
GRID PK
IndividualID FK
GroupID FK
CityID FK
EventID FK

1 1 1 1
2 1 1 1
3 2 1 1
4 2 1 1
5 3 1 1
6 3 1 1
7 3 1 1
8 4 2 1
9 4 2 1
10 5 2 1
11 5 2 1
12 5 2 1
13 1 1 2
...

This should be something like:

1 1 1 1 1
2 2 1 1 1
3 3 2 1 1
4 4 2 1 1
5 5 3 1 1
6 6 3 1 1
7 7 3 1 1
8 8 4 2 1
9 9 4 2 1
10 10 5 2 1
11 11 5 2 1
12 12 5 2 1
13 1 1 1 2
14 2 1 1 2
....

James A. Fortune

[jimfortune]

The two answers to your post in microsoft.public.access were
interesting:

1) Sorry, I'm afraid I don't have an answer to that one.

2) The problem you express is not an Access problem. It's a logic ...

James A. Fortune