Re: Important - Table design and relationships problem
If I've understood you correctly, this is what I would do.
Originally posted by BenjeeNy
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 5 - There are a maximum of five activity periods per day.
Location 1 - Name of location where activity is taking place.
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 5 - Dropdown list with names of activities.
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,
You will probably want to create one table for the bunks and , one for the activities and another that relates the two together. The one that relates the two together will contain the key from the bunks table, the key from the activities table and the location of each bunk/activity combination took place. The relationship to the bunk/activity table will be a many to many relationship. The bunk table and the activity table will each be the "one" side of the relationship and the linking table will be the "many". You won't need the activity 1 through 5 fields.
Look into the Many to Many relationship and see if it doesn't seem to be what you need.
Hope this helps point you in the right direction.
DAO, ADO, SQL, Automation and anything else I can pick up.