One or many

[pedro8ae]

Hi friends, I am developing a project for an department in a school.
this school has some other sub-campus and each campus has different
rooms. ex.:
School XY:
sub-Campus 1:-Room1.1, Room 1.2, Room 1.3, Room 1.4, Room 1.4
sub-Campus 2:-Room2.1, Room2.2
sub-Campus 3:-Room3.1

I want to create a form that allow the user to select a sub-campus and
then automatically
the form shows the respective room for that campus. Ex.:
In a drop box: sub-campus 1
In another list box: will show the rooms for that specific campus.

Do I have to create for each sub-campus a table?
how can I design a form like this..
I want the result of this selection be saved in a table.

My second crazyness is that :How can I select more than 1 option at the
time in a combo box or list box or option group???

If someone gets this message, please write me back......
Thank you...

[Ron2006]

NO, NO you do NOT want to create a separate table for each campus.

You want something like this:

tblCampus - Campus Table
ID - PK - autonumber
CampusName
other campus specific information

tblRooms Room Table
ID - PK - autonumber
CampusID - FK (The ID number of the Campus that this room is at)
RoomDesc
other room specific information (ie. max desks allowed - room color
- whatever)

With the above you can add a campus at any time and then add rooms to
that campus or any other campus at any time.

Ron

[Tim Marshall]

Ron2006 wrote:

NO, NO you do NOT want to create a separate table for each campus.

In addition to Ron's excellent advice, does each campus have one or more
buildings? In my facilities experience, it would be very odd to list
rooms and not buildings. The logical structure of such a system would
be to also have a buildings table:

tblRooms -tblBuildings -tblCampus

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Ron2006]

Very good point Tim. Not having done one, I did not even think of
that.

The structures now change to be:
tblCampus - Campus Table
ID - PK - autonumber
CampusName
other campus specific information

tblBuilding - Building Table
ID - PK - autonumber
CampusID - FK (The ID number of the Campus where this building is
located.)
BuildingDesc
other building specific information.
Which would then change the tblRooms to be:

tblRooms Room Table
ID - PK - autonumber
BuildingID - FK (The ID number of the Building that this room is
at)
RoomDesc
other room specific information (ie. max desks allowed - room color

- whatever)

I hope you see the structure/concept that we are trying to derive.

Ron

[pedro8ae]

Ron and Tim,, you are genious guys...
yes, for each campus are building and rooms numbers ex.:
campus name: main campus
Building: mmth
room#: 249

so, the total description would be:
main campus
mmth249
*for that specific room.

my problem is how to relate these 3 entities campus - building and room
in a way that a user-database administrator can input this data and
saved in a table (or 3 tables) throught a form AND after this create a
form for a final user be able to pick a campus and get auttomaticaly
the Buildings and Rooms (specific for that campus he just choose).

I hope could explain this ,

Again thank you guys..

Ron2006 wrote:

Very good point Tim. Not having done one, I did not even think of
that.

The structures now change to be:
tblCampus - Campus Table
ID - PK - autonumber
CampusName
other campus specific information

tblBuilding - Building Table
ID - PK - autonumber
CampusID - FK (The ID number of the Campus where this building is
located.)
BuildingDesc
other building specific information.
Which would then change the tblRooms to be:

tblRooms Room Table
ID - PK - autonumber
BuildingID - FK (The ID number of the Building that this room is
at)
RoomDesc
other room specific information (ie. max desks allowed - room color

- whatever)

I hope you see the structure/concept that we are trying to derive.

Ron

[Ron2006]

There are so many ways to skin this cat.

Way ONE.
1) Campus form bound to campus table (single form view)
2) subform on it bount to Building (with parent child relationship)
either single form or continuous form view.
3) Subform on the subform created in step 2 (can be single, continuous,
or datasheet vies) Again it will have parent child relationship)

Way TWO:
1) Unbound form, in single form view.
2) Combo box on that form with query of campus table
3) subform on this main form that is just like the subform in 2 above
except that its record source is a query with criteria of the selection
from the combo box.
4) subform on this subform that is identical to step 3 in Way ONE.

Way THREE:
1) Unbound form, in single form view.
2) Combo box on that form with query of campus table
3) Second combobox on that form with query of building table that is
based on a query that has criteria of combobox one just as step 3 in
Way TWO
4) Subform on this main form that is identical to step 3 of Way ONE
except that it is based on a query that has criteria of second combobox
on this main form.

Way one can allow easy addition of information into any of the tables.

Way two can allow easy addition of information into building and room
tables. This way will requiere the default value for the campus ID of
the buildingtable records to be the combobox of the main form.

Way three can allow addition of information into room table if desired
with special code in the default value of the buildingID field that
points to the value in the combobox 2 on this form.

This is just touching the surface. Hope it gives you some ideas. The
key here is to learn from the methods and let your imagination expand
the methods into other combinations of queries and default values.

Ron