A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relationships



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2010, 03:01 PM posted to microsoft.public.access.tablesdbdesign
TheDon
external usenet poster
 
Posts: 15
Default Relationships

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

  #2  
Old March 9th, 2010, 07:55 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Relationships

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

"TheDon" wrote in message
...
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

  #3  
Old March 9th, 2010, 10:00 PM posted to microsoft.public.access.tablesdbdesign
TheDon
external usenet poster
 
Posts: 15
Default Relationships

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" wrote:

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

"TheDon" wrote in message
...
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

.

  #4  
Old March 9th, 2010, 10:31 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Relationships

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

"TheDon" wrote in message
...
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.


"Gina Whipp" wrote:

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

"TheDon" wrote in message
...
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

.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.