Results 1 to 3 of 3

Thread: One-to-many

  1. #1
    Join Date
    Mar 2009

    Unanswered: One-to-many

    Hi fellow Access developers

    Here is my problem.
    I created a database for an organization I volunteer for but Im running into design problems. I want the database to be able to assign preferred days of the week (that the volunteers want to serve) with the flexibility to change them.

    The problem is in order to be able to keep my reporting options flexible. I will have to write a record (on a separate table) for each volunteer for every day they want to serve. A one to many relationship between the volunteer table and preferred days table.

    I have not touched Access in over 6 years and Im very rusty. Before I retired I was fluent in VBA (I used to be good at structuring too, but oh well), query criteria etc.. So whatever you suggest I can accomplish, although it might take me a minute to remember how.

    I hope that made sense.

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    A typical design would include two Tables, one for volunteer information and one for assignment information:

    VolunteerID (Primary Key)


    VolunteerID (Foreign Key)
    AssignmentID (Primary Key)

    You'd then create two Forms for data input, one for each Table. The Volunteer Form would be used as a Main Form and the Assignment Form as a Subform. They would be joined on the VolunteerID Fields in each.

    You'd enter data for a volunteer in the Main Form, then enter assignment data in the Subform. Access will automatically join them on the common, VolunteerID Field. As you move through the volunteer Records, the assignment Records will change appropriately.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009

    one to many

    Thank you so much for responding so quickly.

    Your approach is fine except when you want to produce a schedule of everyone that prefers Monday and Thursday or Tuesday and Wednesday etc..

    It seems to me there has to be a record for each day assigned to each volunteer, a one (volunteer) to many (days) and we still have to factor in the hours preferred.

    So one rpt might break by day and then hours:
    name home_phone cell_Phone

    Am I over thinking this thing? I don't want to maintain some normalization.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts