Table Design-Relating Fields

[TNDaffy]

I need to design a table for camp registration use. We have 4 different sessions but within those sessions, theres a possibility of attending one of several different programs, there is also the possiblity that one camper may attend more than one session, how do I create a database that will allow me to pull say 1st session, horse program. I need to be able to pull by session, and by program, please somebody help me this, I'm pretty new to access, and theres just some things I'm nto catching on to real quickly! Any help will be appreciated!

 

[BlackKnight]

Hi,

If I understand you correctly I think this table design should be a good starting point"
Add whichever other fields you need to add for each table.

tblCamper
CamperID <==Autonumber, Primary key
LastName
FirstName

tbCamperSession
CamperSessionID <==Autonumber, Primary key
CamperID <==Long integer, Foreign key
SessionID <==Long integer, Foreign key

tblSession
SessionID <==Autonumber, Primary key
SessionName
ProgramID <==Long integer, Foreign key

tblProgram
ProgramID <==Autonumber, Primary key
ProgramName

You can then create queries to pull the info that you need. You would use the Primary keys to link the tables to get the corresponding records.

Have a good one!
BK

 

[BlackKnight]

Hi,

oops...I experienced some braindrain for a momet... Sorry about that

It shouldbe

tblSession Autonumber, Primary key
SessionID
SessionName

tblSessionProgram Autonumber, Primary key
SessionProgramID Long integer, Foreign Key
SessionIDProgramID Long integer, Foreign Key

 

[TNDaffy]

ok now how am I going to create the query and form from this to be able to produce a form that will allow me to put a camper into more than one session, and also allow me to create reports that will let me query for all sessions and programs attended to be listed or just a certain one, I'm not following this concept, I don't think, I have a really hard time understanding the relational part of database work and how to make it work! Thank you for your help in this!

 

[JerryDennison]

You may wish to modify BK's scenario just a little. I'm going to assume that no camper can be enrolled in the same session more than once. If so, you may wish to make the CamperID and SessionID in tblCamperSession a compound primary key. I would keep the autonumber field CamperSessionID and use it as a pseudo primary key for relationship purposes. The only drawback being you can't enforce referential integrity cascade updates/deletes, but it makes for addressing each record much easier.

 

[TNDaffy]

ok thank you for the additional assistance, now how do I pull from the tables to create a functional form for camper info entry, the first table has all their address, parental, payments, etc. info, and then the tables that y'all have added are just like they look on here, now when I child registers for camp they only send in one registration whether they're going 1 or 3 different sessions, I want to be able to enter all this on the same form and not create multiple entries for the same person, so will it work to some how create say 4 different session entry fields but I would also need programs that related to that same session entry, am I making sense here? I hope you can help me, this whole concept of being able to enter the session info is driving me crazy, LOL! Thanks!

 

[TNDaffy]

How do you create a compound primary key? Never heard of doing that

 

[TNDaffy]

ok I figured out the compound primary key thing, sorry

 

[TNDaffy]

ok heres my fields I need, I'm not getting this to work for me, and I don't know why, please tell me how I need to organize this into my tables to function for me in my reports and form:

Field Names:
First Name Grade Tentmate
Last Name Age New Camper
Address Parent/Guardian Returning Camper - # of years
City M Work # Ethnic
State F Work # Camp Fee
Zip M Cell # Add Session Fee
County F Cell # Total Camp Fees
Home Phone Troop Level Deposit Fee
Early Bird Discount
Second Child Discount
Third Child Discount
Cookie Credit
Financial Aid
Balance Due
Payments Made
Paid In Full

Then the session information
there is 4 sessions with 4 different programs to choose within each one
a girl can choose more than one session and but only one program within a session
I need to be able to fill out one form for each child and pull reports that reflect a listing of girl, program, session for the whole summer and each session individually, thank you for all your assistance in this, I got our Day Camp database set up no problem, but this multiple session program thing is really confusing me, obviously!

 

[TNDaffy]

PLease I need help here somebody, I cna't get thsi to work out!