Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Combining multiple DB into 1 (2003/SP3)

    I have been wanting to do this for several years now and it just seems like a good time to do it. I run a Sunday School for a church and I have been using an Access DB to keep track of the students and their families. Every year we have several special events and we have a lot of families that attend that do not, and will not, attend church. In order to keep from getting them confused with our regular Sunday School students, what I have been doing is creating a special events DB and copying the student records from my main DB. The problem is that now I have so many different databases, I also have seperate ones to keep track of volunteers, my information is just not consistent. I am going to try and pull it all into 1 Database and then for each event, assign that to the person as appropriate.

    I am also making a change in the way I keep track of information on the parents. What I had previously was a Family Table, which contained home address, phone number, etc. including Mother and Father. I also had a Student Table, which had the information on the students. I want to have seperate tables for the parents, because sometimes both or maybe one or the other will volunteer to help with an event and I need a way to keep them seperate. I'm not sure if I should have a Mother table and a Father table or a Parents table and a field to check whethere it is Mother or Father. In this table I will store individual information such as cell phone number, work phone number and e-mail address, in addition to name.

    I have used an Autonumber field to assign a FamilyID to each family, as well as a StudentID to each student in the Student Table. I think that each parent will also need a unique ID number, so that when volunteer assignments are given, I can tie them together. They don't currently have an ID, since I am setting them up in a new table. If I use an Autonumber for each of those, I will have 4 sets of numbers that could all be the same, even though they are in unique fields. I don't show these to the end user, of course, but I'm just not sure if that is a good design to be using.

    My plan is to be able to assign parents as a volunteer but I also have some of the students that will be volunteers at our special events, so it is quite possible I will have volunteers that have the same ID number for the same event. Again, that may not be a problem, since they would be in seperate fields (StudentID or ParentID or MotherID, FatherID)

    I was originally planning to have an event table and then connect each family to an event but the more I have thought about it, it seems to make more sense to assign the student to an event, since sometimes not every child in a family attends each event.

    I think if I can get past the ID number scheme for each entity I can figure it out from there. Any help that you can give me, if this makes sense, would really be appreciated. Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combining multiple DB into 1 (2003/SP3)

    I would tend to have the following tables:
    Family Table
    Persons Table
    Event table
    EventParticipants table

    You would describe each family, address phoe etc in the family table
    You would have everyone in the Persons table, there could be a Type field describibg whether they are a Mother, Father, Student of an associated family. This would have a pointer to the Family table.
    An Events table would describe an event.
    An EventParticipants would show who is attending that event, this points to the Persons table and to the Events table.

    Hope these few thoughts help.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Combining multiple DB into 1 (2003/SP3)

    Hi Pat,

    Thanks for the info. I don't think I want to use a Persons table for everyone, because there is different info that I want to keep. For example, for the parents, all I really need is cell phone and e-mail, since address, home phone etc is already stored in the Family Table. For the students I want to record their birthdate, grade level, allergies and special notes (ADHD, Grandparents Names, etc.).

    I have an Events table set up, I'm just not clear how to tie it all together. I wonder if anyone has a similar type of database, (nobody would have exactly what I want, because I just want too much) that I could look at for a sample. I feel like if I can get over the hump of how to put it all together, it should be fairly easy from there, although I'm sure I'll run into a few more bumps in the road along the way.

    Pat, again, thanks for the help.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combining multiple DB into 1 (2003/SP3)

    I would advise against multiple tables for parents, students etc, this will complicate the application, its a lot better to have a FamilyMemberType that signifies Father, Mother, Grandfather, Grandmother, Student, etc.

    To get the Grandparents of a student you just query the persons table linked to the family table for a type of Grandfather and Grandmother, etc etc

    Still it's up to you, these are just thoughts of mine.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining multiple DB into 1 (2003/SP3)

    Unless you have hundreds of fields, there's no objection against collecting partly different information for different groups in a single Persons table.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Combining multiple DB into 1 (2003/SP3)

    The more I think about it, I have to agree with both Hans and Pat. Between the 2 groups of people, adults and students, I will have a total of 5 fields for the adults and 9 for the students. Some will work for both, such as ID, FirstName, LastName(if different from Family Name), while some are unique, such as CellPhone and EMail, although it really wouldn't surprise me if in a few years, 5 year olds have those, BirthDate (I really only need that for the kids) GradeLevel, Allergies and SpecialNotes (which could also be used for the adults). In addition, at one of our events, everyone gets a T-shirt. Before, I had a table for the volunteers and a table for the students and would have to run a union query to get one list of T-shirts. Now, if I just make that a field in the People table, it will be easy to get a report.

    I'm going to dive into this and see where it takes me. Thanks for the advice (and I'm sure I'll continue to use this space as I have more questions). This is such a great resource.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    OK, I am still working on this one. I have it all set up, with the following tables:
    Families
    Persons
    Events
    EventParticipants

    Each table has the following keys
    Families has a FamilyID which ties to the Persons table.
    Persons has a PersonID as a key and FamilyID which connects it to the family
    Events has an EventID
    EventParticipants has a RegistrationID as a key, then has PersonID and EventID fields

    I am just stumped as to how to connect Events and Persons. I know I need to use the EventParticipants to bring them together but I just can't seem to figure out the next step. Do I do this through a query or a SQL statement on a form? I'm just not sure what to do next. Thank you for any help you can provide.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't really need a RegistrationID field in the EventParticipants table (although you can use it if you wish).

    You need to create a unique index on the combination of the PersonID and EventID fields; this could be the primary key (which is unique by definition).

    See this post for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

Posting Permissions

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