Need help with groundwork (table design and relationship)

[TheBen]

Hi Everyone,

I'm new here, so if I'm doing anything the wrong way or not providing enough info, please let me know.

Here's the story - I just learned access, or so I thought. I promised a friend of mine I'd make her a semi-professional database for use in her children's camp. Basically, it has to keep track of all the bunks activities (sports, etc) on a daily basis. For example, every day you'd have to be able to track and enter a bunk's name, activity, and the location for the activity.

Now, I'm not sure what I'm doing wrong here - I realize this probably very newbie-ish, but please bear with me. Here are the required fields as I see it:

Date - You need to be able to view all activity by date.
Bunk - Name of bunk
Activity 1- Name of activity
Activity 2
Activity 3
Activity 4
Activity 5 - There are a maximum of five activity periods per day.

Location 1 - Name of location where activity is taking place.
Location 2
Location 3
Location 4
Location 5 - One location for each activity.

Those are the fields I think I need. Now, I want the main form to work like this:

Date field - To be entered automatically.
Bunk - Dropdown list with all names of bunks.
Activity 1
Activity 2
Activity 3
Activity 4
Activity 5 - Dropdown list with names of activities.
Location 1
Location 2
Location 3
Location 4
Location 5 - Dropdown list with names of locations.

First basic problem I have is the relationships between the tables. I can't have five fields (activity1-activity5, for example) all taking information from one table, like the activity table with activityName and activityID fields. It won't let me connect one field to five.

I know this must sound really dumb, but I think I'm overlooking something really basic here. Any help at all, and I'll be really grateful. Also, I'm not sure I explained myself very well, so if you need any clarification, please let me know.

Thanks for your time,

Ben

 

[TheAceMan1]

How are TheBen . . . . .

I see a main form [blue]Date[/blue], with a subform [blue]Bunk[/blue], with a subform [blue]ActLoc[/blue] containing the activities & locations.

The activities & locations can come from [blue]LookUp ComboBoxes[/blue]. That way all ya have to do is make proper selections from the lists. The source for the comboboxes will be the approiate Activity/Location table.

I believe your Idea after data entry, is to be able to go to any Date for any Bunk and view the Activities/Locations.

Hope this helps!

See Ya! . . . . . .

 

[lespaul]

First of all, check out

http://www.r937.com

and find the article 'Fundamentals of Relational Database Design'

after reading that, you'll realize that your tables aren't normalized (1st clue - fields named Something1, SOmething2)

For table design, don't think about what you want your reports or UI to be, think about the data you want to store and how it relates together.

In this example you have kids that you want to store information about - name, address, emergency contact information. You have scheduled activities - when, what and where. You have kids going to activities. So a minimum of three tables:

tblChildren
ChildID
ChildFirstName
ChildLastName

(if more that one emergency contact, then you need another table to show the one to many relationship)

tblEmerContacts
EmergencyContactID
ChildID
EmergencyContactName
EmergencyCOntanctNumber

tblActivities
ActivityID
ActivityName

To assign a kid to an activity:

tblChildActivities
ChildActivityID
ChildID
ActivityID

So, there's some basic structure to base it on. Once you read the article, repost your table structure and we'll check it out before you continue on to UI and reporting. Both of these tasks will be much easier if you spend the time to properly develop your tables.

HTH

Leslie