Form Design Question

[wildcat]

I have been toying with this for some time. First - I am fairly new to Access so all information shared will be greatly appreciated!

I have:
tblCamper
AutoNo - PK
CamperID - Generated ID - Linked
Camper Name
etc

tblCampJobs
JobAuto - PK
Job ID
Job

tblCabin
CabinID - PK
CabinName

tblCabinAssign
AuotNo - PK
CabinID
JobID
CamperID

What I am trying to do is create a form that will allow me to fulfill all the jobs and camper assignments in one cabin all on the same form. I need to be able to view all the "tasks" at the same time so that I make sure that everything is completyed and I do not place too many people in one cabin.

I am not sure if I have created my tables correclty to achieve this task or if I am just not thinking correctly to get the form designed right so I can do this.

Again, thanks for all your insight and ideas.

 

[MarkK]

1) I'm not exactly sure what you're trying to do. What is a camp job? Is it closely related to a cabin assignment? Generally, I'd recommend not trying to make any object do too many things. Assigning jobs and assigning cabins will draw data from distinct tables and at first glance I'd expect to develop two interfaces to accomplish these tasks.

2) I've posted a pretty simple solution for dealing with occupancy here http://www.access-programmers.co.uk/forums/showthread.php?t=122491&highlight=occupancy.

3) You seem to duplicate ID fields, for instance, tblCamper doesn't need both 'AutoNo - PK' AND 'CamperID'. One unique identifier per table is sufficient.

4) Your structures don't allow for the assignment of a group to a cabin. Do you always deal with campers as individuals, or can I book a cabin for a group of four people? If so, you might need a 'bookings' table.

 

[wildcat]

Sorry I was not clear on the roles for each table. I hope this answers the questions.

1. the tblCampJobs lists responsibilities for the people in the cabin ie adults in charge, teens in charge, youth work assignments, etc. As far as tblCabinAssignments, that is where I was (will) compile the people that will be in a particular cabin. Each cabin will have several people in it and each will have their own task (Job).
3. I have two unique ID numbers in some of the tables for a couple reasons. 1 - I read numerous postings in the forum where it suggests that I should not use a AutoID for creating my identifier. 2 I have created a unique identifier for all campers - they are based on when and where they are registered as well as the time. (this is based on the event that some of the camps are taken on a first come - basis.
4. The structure for assigning a group to a cabin is where I started to get - confused. I am not real sure how I needed to create the tblCabinAssign to make it so that I could enter (25-30) campers in the one cabin. Should I change it so there are "bunk positions" or something like that to make it so there are more inputs in that table?

Thanks for your help.

 

[MarkK]

1) Ya, so as long as work assignments are always directly related to a cabin then that might work OK, but if you need one person from one cabin for beach clean up, and one person from some other cabin for kitchen help, then your work assignments are NOT ALWAYS related to a cabin. Then you need to look at job assignments as related to Campers, not Cabins.

3) If you need to track when a person registered and where, then store each of those data points in a distinct field. To combine those data to create some duplicate key will only force you to develop logic to both encode and decode that key and that's a make work project. Create one unique, and what's called 'meaningless' key for each table. That is sufficient. Everything else is easiest dealt with as raw data in a distinct field.

4) Only create data distinctions that you need. Will you need your system to tell you who occupies which bunk? Offhand this seems unneccessary for a summer camp type of setting.
- Will you in reality assign a group to a cabin? Like will you assign the Springfield Scout Troop to Cabin 7. If so you need a Groups or Bookings table that deals with that group as a single thing.
- If you are going to assign individuals to cabins then the structures you defined in your first post look sufficient.

- Your suggested structure will fail if 1) one camper might have more than one job, 2) one job might require more than one camper, 3) if you have jobs that are not related to cabins, 3) if you book groups.