Results 1 to 6 of 6
  1. #1
    abbasou is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2020
    Posts
    3

    Question Scheduling Database with multiple relationships

    Hey all, pretty new to Access and brand new to these forums. I'm the worship leader at my church and I'm trying to create a database to keep track of the music scheduling. So the way I'm approaching it is, I'd have a table for the worship dates/times (called "Services") which would include the Date, Day (Sunday, Wednesday, etc.), the Time (mostly AM/PM), and all the songs used for that service, whether Congregational or Special, as well as a few other details. I would also have a table of all the Songs, with a few details related to them as far as the type of song, the key it's in, what special occasion it's for etc.



    My goal is to be able to look up all the songs used within a date range, as well as be able to see all the dates a particular song was used. So my question is: how do I design my tables in order to create the appropriate relationships and queries to be able to accomplish all this? [bold for readability]

    I've watched a few YouTube tutorials on Access in general and specifically relationships, but I'm not quite sure how to build it all.

    I'm sorry if there are tutorials out there that address. Being so new to Access and databases in general, I'm not quite even sure how to search for this kind of thing specifically. I'd super appreciate any help.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This requires a Many-to-Many table setup. Many songs can be related to a service and a service can be related to many songs.
    You need three tables. The Services, the Songs and a junction table to relate one to the other.
    Check out Many-to-Many on YouTube and come back here if you get stuck.

  3. #3
    abbasou is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2020
    Posts
    3
    Thanks for that. I've watched a couple of videos and am beginning to get an idea of what to do. Attached is a screenshot of tables and relationships so far.Click image for larger version. 

Name:	music_db_tables_relationships.png 
Views:	19 
Size:	42.7 KB 
ID:	43469

    For now ignore the "DatesUsed" field in the SongsT table since I'm pretty sure I can create a Query to see that later. I am curious about the TimesUsed field. Is there a way to create a query to display that kind of data as well?
    With my current table structure, will it be possible to see all the dates a song has been used, and then see all songs used within a date range? Or maybe all the songs of a specific type used during a date range?

    In my head I think I can build queries to do all this, I'm just not sure if there's something I should improve in my database structure before I start adding a ton of data.

    Thanks again for your help.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Yes. Move the DateUsed field to the junction table as well as any other fields that apply to a particular performance.
    As far as TimesUsed, you don't need to record that. Just have a query that counts the occurrences of the songID in the junction table.
    Last edited by davegri; 11-19-2020 at 01:55 PM. Reason: clarif; addl

  5. #5
    abbasou is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Nov 2020
    Posts
    3
    Quote Originally Posted by davegri View Post
    Yes. Move the DateUsed field to the junction table as well as any other fields that apply to a particular performance.
    As far as TimesUsed, you don't need to record that. Just have a query that counts the occurrences of the songID in the junction table.
    Oh boy. I suddenly have a lot of questions. I'm sorry. Thanks for your patience.

    1. When you say any fields that apply to a particular performance, what exactly are you referring to?

    A "Service", for purposes of this database, includes several Congregational Songs (CongSongs, the number can vary), a Special Song, a Scripture reading (CalltoWorship), and a speaker. I'm hoping to track, for each Service, the Date (say 11/15/20), Day (Sunday), Time (AM/PM), Songs (Congregational and Special), Call to Worship, and Speaker.

    2. Since it is possible that, for example, a particular Scripture passage can be used in different services, should those go into their own database like the songs are?
    3. Does the same apply to Speakers?
    4. Should I delete the CongSong# fields from that table altogether?
    5. I'm just starting to discover subforms. Is that how I will populate all the songs for each service?

    I'm sorry to drop all this here. I'm continuing to watch YT videos and learn, so I'll come back and update if some of these questions are answered.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If a Song is performed in 7 different Services, there would be 7 records in the junction table. Reconsidering the DatesUsed, it wouldn't be necessary to store the date in the junction table as it is present in the Services table.
    For a given Service, if there is only one Special Song, one Scripture reading, one Speaker then a field in the Service record would record that. Lookup tables for those fields would be helpful as you mentioned.
    Again, the total number of times a song has been performed can be calculated by a query counting the number of SongID in the junction table. The query could also have a date range criteria.

    You don't need the 4 CongSongs fields in Services. That's a serious design flaw conflicting with DB normalization rules. You can use the MTM setup to assign any number of songs to a service with the below form, each adding a row to the junction table.

    The main form would have the Service as the recordsource and the subform would use the junction table. A combobox in the subform would choose the Songs.

    Don't get too heavy into the data entry before your design is appropriate and tested. Just work with a few records at first. For testing, leave fields blank, try entering inappropriate data, move to another record before the current record is complete. Try to create errors and find a way to deal with them. Easier to do now than later.

    Using spaces or special characters (except for underscore) in data field names is bad practice. Just drop the # from the license number or just call it CCLI_No

    If you get stuck or just want a review, post your DB here for someone to have a look and offer advice.
    Last edited by davegri; 11-20-2020 at 12:04 AM. Reason: added test comments; clarification

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Flight Scheduling Database Possible?
    By Redletter in forum Database Design
    Replies: 3
    Last Post: 10-22-2016, 09:56 PM
  2. Recurring Scheduling/Appointment Database
    By pandor4 in forum Sample Databases
    Replies: 3
    Last Post: 07-16-2014, 04:50 PM
  3. Help designing a Scheduling database
    By DuWayne in forum Database Design
    Replies: 2
    Last Post: 12-31-2011, 09:30 AM
  4. Tutor Scheduling Database
    By prawer in forum Database Design
    Replies: 5
    Last Post: 12-16-2011, 10:18 AM
  5. Scheduling database in Access
    By ACS Newbie in forum Access
    Replies: 4
    Last Post: 01-21-2010, 10:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums