Relationships

[TheDon]

I have to figure out a way to reperesent/enter camper activities. It seems
to be a many to many... I have many campers who will be singing up for many
activities. How do I get that to come up on the sub form correctly...

The main form is setup with the TbleCamperInfo as the primary table, with
tabs and subs forms for the other corresponding tables... Let me know if it
looks good so far...

Here is a brief (not every field, but you'll get the idea) of the tables in
question. Most all of the form stuff works well, until I get down to camper
activities. You'll see below:

Tblcamperinfo
Camper ID autonumberPK
camperfirst
Camperlast
Dob
Age
(etc)

Tbleparentinfo
Parent ID PK
Camper ID
Parentfirst
Parentlst
Parentst
Parentcity
Parenzip
Parentemail
(etc)

Tblcmpercurrentyear
CamperID PK
Registrationdate
Session
Waitlist
Cabin
Counselor ID (from staff table)
Counselor Name (from staff table)
Agegroup
(etc)

Tblcamperactivities
This one Is where I may need help
We have a number of activities: canoeing, hiking, climbing, tennis (etc.)
All offered 5 times a day (9:30, 1030, 11:30, 300, 500)
I am trying to find the best way to set up the many to many relationship
here…
Do I create a table of activities, then a table of times, or just one table
of each individual activity time?
Then how does that work into a subform. How do I call that up???
Any help would be great…

thanks a ton everyone!!!

Don

 

[Gina Whipp]

TheDon,

First, I need to fix up the tables...

tblCampers
cCamperID (PK - Autonumber)
cFirstName
cLastName
cDoB (No need to store age as that is a calcualted field anyway, so I
removed it)
....etc.

tblParents
pParentID (PK - Autonumber)
pCamperID (FK - related to tblCampers)
pFirstName
pLastName
pAddress1
pAddress2
pCityName
pPostalCode
peMailAddress
....etc.

tblParentsPhoneNumbers
ppnID (PK - Autonumber)
ppnParentID (FK - relate to tblParents)
ppnPhoneNumber
ppnPhoneNumberTypeID (FK - long - relate to tblPhoneNumberTypes)

tblPhoneNumberTypes
phtID (PK - Autonumber)
phtType (Cell, Vacation, Office, Home, etc...)

tblRegistration
rCamperID (FK - long - relate to tblCampers)
rRegistrationDate (We can get year from here so we don't want to define tbl
as current year)
rSession
rWaitList
rCabin
rStaffID (FK - long - relate to tblStaff - No need to store name so I
removed that because it will be in tblStaff)
Agegroup ****What is this?
....etc.

tblCamperActivities
caCamperID (FK - long - relate to tblCampers)
caActivityID (FK - long - relate to tblActivities)
caTimeSlot
....etc.

tblActivities
aActivityID (PK - Autonumber)
aActivity

....Now, tell me what questions you have and I asked you one because I don't
understand tblRegistration - Agegroup, not sure where that belongs or what
you use it for.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I have to figure out a way to reperesent/enter camper activities. It seems
to be a many to many... I have many campers who will be singing up for many
activities. How do I get that to come up on the sub form correctly...

The main form is setup with the TbleCamperInfo as the primary table, with
tabs and subs forms for the other corresponding tables... Let me know if it
looks good so far...

Here is a brief (not every field, but you'll get the idea) of the tables in
question. Most all of the form stuff works well, until I get down to camper
activities. You'll see below:

Tblcamperinfo
Camper ID autonumberPK
camperfirst
Camperlast
Dob
Age
(etc)

Tbleparentinfo
Parent ID PK
Camper ID
Parentfirst
Parentlst
Parentst
Parentcity
Parenzip
Parentemail
(etc)

Tblcmpercurrentyear
CamperID PK
Registrationdate
Session
Waitlist
Cabin
Counselor ID (from staff table)
Counselor Name (from staff table)
Agegroup
(etc)

Tblcamperactivities
This one Is where I may need help
We have a number of activities: canoeing, hiking, climbing, tennis (etc.)
All offered 5 times a day (9:30, 1030, 11:30, 300, 500)
I am trying to find the best way to set up the many to many relationship
here…
Do I create a table of activities, then a table of times, or just one table
of each individual activity time?
Then how does that work into a subform. How do I call that up???
Any help would be great…

thanks a ton everyone!!!

Don

 

[TheDon]

Wow! Thanks for the help…

OK, first for your answers…
AgeGroup. A lot of organization for the current year is by age group. We
typically have 5. This is entered once they are sorted into their cabins.
So, it’s a 1-5 designation, simply for sorting purposes. It does relate to
an “agegroupname†that I did not put on there. Again, I didn’t put every
single field on there so as to keep the answers simple.

Questions:
1. Your naming convention… I know it’s one of the big ten, so I have tried
to redo that as well. The leading letter is used by you to just give it
relation/ownership to that table? Inotherwords, it tells me what table it
came from in the event of a duplicate? Is that right?

2. Tell me what FK –long is??? I am not familiar with the “long†part…

3. Separating out the phone numbers… Is that just to keep in more
“normalized†since you may have a ton of empty fields?

4. As far a displaying this on a form. I was thinking about creating a
query by tblCampers mush like they do in the access template “Contact
Management†DB. Then add tabs and sub forms for the other relating tables.
Would that make sense or am I all wrong? How would I pull up the activities
in this or how does that form look? That one still confuses me…?

I will clean up these tables. Thanks for all the help.

Don

 

[Gina Whipp]

Don,

No problem, glad to be able to help... On to your answers which are
in-line, see below

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Wow! Thanks for the help…

OK, first for your answers…
AgeGroup. A lot of organization for the current year is by age group. We
typically have 5. This is entered once they are sorted into their cabins.
So, it’s a 1-5 designation, simply for sorting purposes. It does relate to
an “agegroupname†that I did not put on there. Again, I didn’t put every
single field on there so as to keep the answers simple.

***Okay then leave rAgeGroup in that table.

Questions:
1. Your naming convention… I know it’s one of the big ten, so I have tried
to redo that as well. The leading letter is used by you to just give it
relation/ownership to that table? Inotherwords, it tells me what table it
came from in the event of a duplicate? Is that right?

*** That is correct. It comes in handy when I have REread my VBA. However,
you can use your naming convention but be consistent.


2. Tell me what FK –long is??? I am not familiar with the “long†part…

***FK = Foreign Key which is normally related (in Relationships window) to
the PK = Primary Key. When you see FK you know somewhere I have a table
with it's Primary Key. Long = Long Integer which is the data type for the
field and the only one that will *join* to an Autonumber PK.

3. Separating out the phone numbers… Is that just to keep in more
“normalized†since you may have a ton of empty fields?

*** I adopted this practice becuase nowadays folks seem to have more phones
then the *standard* phone number field. You can have as much as 6 + phone
numbers for one person. So rather then add a PhoneNumber1, PhoneNumber2,
etc... to any table... I break it out to it's own table so now I can add as
many phone numbers as I my person has. However, if you like you can combine
it with tblParents and do the PhoneNumber1, PhoneNumber2, etc... thing.

4. As far a displaying this on a form. I was thinking about creating a
query by tblCampers mush like they do in the access template “Contact
Management†DB. Then add tabs and sub forms for the other relating tables.
Would that make sense or am I all wrong? How would I pull up the activities
in this or how does that form look? That one still confuses me…?

***Setting it up with tabs sounds like a good way to go. The tblActivities
is just used to a look-up for the caActivityID field in the
tblCamperActivities. tblCampaerActivities would be the one that is on it's
own seperate tab tied to tblCampers which is what your main form is going to
use.


***Since you didn't post all the fields in all the tables, if you have a
question about whether a field belongs in a certain table, just post back
with the the field names.

 

[TheDon]

Gina,

Thanks again for your help. I have a few questions as this starts to take
form to be sure it is going in the right direction:

1. tblRegistration: In the past all of this information (including
everything from the other tables) was stored on one big table and represented
on one huge form…. Huge… I know, it makes any database person cringe. It
did make it easy to search and extract info from previous years. Like who
came in 2008, but not 2009 for example. How many first year two-weekers
(session) did not come back in 2009… How many kids did we have from charlotte
in 2007? For example, session used to be stored in a field that was added
each year. We would have a field called “2007 session†and add info for each
kid there, then add fields in the next year for 2008 session and so on each
year; this goes back for about 8 years this way . It made for a huge and
slow table, but allowed for infinite stats and search extraction. My
question is…What is the best way to store this information (from
tblregistration) so that I can pull stats for the next five+ years…? Would I
use an update query each year that would add the registration table info to a
table that I might call tbl2010campers? Your thoughts would be great…

2. Other fields that you asked about… Here are some… Let me know what you
think…
I created another table with account information. Again, all of this info
was (is) on the one giant all-inclusive table. Anyhow, it made sense to
break out financial data into it’s own table/tables so here is what I
did/have:

tblAccount
aCamperID fk relate to tblCampers
aAccountID PK auto
aTuition
aStore (a total of items that the racked up in their store account while
here)
aBalance (again, this may not need to be stored, but I had it before)

I also have these fields that look like they could go into some relating
table that would associate with above. … These are line item entries
typiclally for payment.
Amount
Date
CheckNumber
Description
credit/debit

-----------------------------
In tblCampers I had some additional fields I was not sharing with you (I
feel like I’m in therepy). I have listed them below. They are also in
tblCampers currently… I can see you may not like some of these already….

cGrade (what grade are they in school)
cActive (yes/no field telling me whether they are active this year0
cNewsletter (yes/no field for whether we send them a newsletter or not)
cSister (text field for the name of a sister if they have one)
cYoungerBrother (same as above…)
cLastUpdated (just as it sounds, date when it was updated last)
cSchool (what school they attend)

In tblRegistration I also had these fields… Looks like some trouble here
too.

rNew yes/no (are they a first year camper?)
rCancelled yes/no (did they cancel this year?)
rCabinRequest1 (is there a friend they are requesting in their cabin)
rCabinRequest2
rCabinRequest3
rCabinRequest4
rTravelInformationForm yes/no (do we have this form yet) same for all
below…
rHorsebackRidingForm
rInsuranceCardCopy
rTreatmentForm
rMedicalForm
rCabinPlacementForm


The first step of therapy in admitting there is a problem, right???

thanks agian,


Don

 

[Gina Whipp]

Don,

Answers in-line...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

Thanks again for your help. I have a few questions as this starts to take
form to be sure it is going in the right direction:

1. tblRegistration: In the past all of this information (including
everything from the other tables) was stored on one big table and
represented
on one huge form…. Huge… I know, it makes any database person cringe. It
did make it easy to search and extract info from previous years. Like who
came in 2008, but not 2009 for example. How many first year two-weekers
(session) did not come back in 2009… How many kids did we have from
charlotte
in 2007? For example, session used to be stored in a field that was added
each year. We would have a field called “2007 session†and add info for
each
kid there, then add fields in the next year for 2008 session and so on each
year; this goes back for about 8 years this way . It made for a huge and
slow table, but allowed for infinite stats and search extraction. My
question is…What is the best way to store this information (from
tblregistration) so that I can pull stats for the next five+ years…? Would
I
use an update query each year that would add the registration table info to
a
table that I might call tbl2010campers? Your thoughts would be great…

***Instead of a hugh table or a table for seperate years you would use a
query and search on the date field, ie in the query you would use
Year([YourDateField]) Thereby pulling the year from the date which is then
searchable. AND you can accomplish all the above scenarios with your query.
(And yes, you will need to run an Append query to get all that data into the
correct tables.

2. Other fields that you asked about… Here are some… Let me know what you
think…
I created another table with account information. Again, all of this info
was (is) on the one giant all-inclusive table. Anyhow, it made sense to
break out financial data into it’s own table/tables so here is what I
did/have:

tblAccount
aCamperID fk relate to tblCampers
aAccountID PK auto
aTuition
aStore (a total of items that the racked up in their store account while
here)
aBalance (again, this may not need to be stored, but I had it before)

***aStore, does someone many type in how many items or is there a table that
has items purchased? You do not need to store aBalance since it sounds like
a claculation, you can do those on-the-fly.

I also have these fields that look like they could go into some relating
table that would associate with above. … These are line item entries
typiclally for payment.
Amount
Date
CheckNumber
Description
credit/debit

***First, Date is a reserved word. This is another reason to use a naming
convention to avoid using Reserved Words which will be a problem for Access
and eventually cause you problems. For a complete list see...

http://allenbrowne.com/AppIssueBadWord.html

-----------------------------
In tblCampers I had some additional fields I was not sharing with you (I
feel like I’m in therepy). I have listed them below. They are also in
tblCampers currently… I can see you may not like some of these already….

cGrade (what grade are they in school) ***keeper, as they get a new entery
when they register and they will be in a different Grade.

cActive (yes/no field telling me whether they are active this year) ***Not
sure about this, it implies you have to enter a record whether they attend
camp or not. Is that what happens?

cNewsletter (yes/no field for whether we send them a newsletter or not)

***This should probably be broken out into a seperate table, suppose like me
they have two sisters and one brother? Perhaps tblSiblings?
cSister (text field for the name of a sister if they have one)
cYoungerBrother (same as above…)

cLastUpdated (just as it sounds, date when it was updated last)
cSchool (what school they attend)

***Balance of above fields sound okay.


In tblRegistration I also had these fields… Looks like some trouble here
too.

rNew yes/no (are they a first year camper?)
rCancelled yes/no (did they cancel this year?)

***The below belong in a seperate table linked to the registration table.
Rule of *my* thumb and quite a few others. If you have to 1, 2, 3, etc...
after your field names you should place those values in their own table.

rCabinRequest1 (is there a friend they are requesting in their cabin)
rCabinRequest2
rCabinRequest3
rCabinRequest4

***If these are just Yes/No fields then they can stay where they are.
rTravelInformationForm yes/no (do we have this form yet) same for all
below…
rHorsebackRidingForm
rInsuranceCardCopy
rTreatmentForm
rMedicalForm
rCabinPlacementForm


The first step of therapy in admitting there is a problem, right???

***Yes that is the first step! But I'm going to say you are way past the
first step, you have admitted the problem and are now on your way to
recovery! <smile>

thanks agian, ***You're most welcome!


Don