Results 1 to 7 of 7
  1. #1
    officeninja is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4

    Database design for office with multiple operations

    Hi all, this is my first post here so please be kind I'll do my best to explain my situation as clearly as possible. I have a framework I'm currently operating from, but I'm seeking input/feedback and external ideas so I can improve what I'm doing. I'm not asking anyone to just design a database for me like I see so many beginners asking. I just need advice from people who know what they are talking about!



    My Access "background": I'm self-taught and still getting my head around Access and relational databases in general. I've built a few small databases to practice and learn so I can get my office away from using massive Excel spreadsheets with dozens of tabs to manage records.

    So here's the story. I work in a student support office for a small university. Our office has three "worlds" within it: general support, a language program, and recruiting and scholarships. Each "world" has its own functions and goals. There is some overlap between worlds, but they are also very distinct in operation. Here's a simplified breakdown:


    • We have 100 students that our office supports. We retain an individual record on each student (Name, birthdate, address, etc).
    • Twenty of the 100 are enrolled in our language program. Eighty are not enrolled. This number changes each semester as old students matriculate and new students come in.
    • We oversee everything within our language program (course information, registration, teachers, tutors, payroll, students, etc).
    • Forty of the 100 come to the university through our office's recruiting/scholarship model. Most of those students are also in our language program, but not all of them.
    • We track all the info for our scholarship program (interviews, language level, admission information, etc.).



    Because I oversee all three worlds, I see the ways they connect and love the idea of one unified database to track all of our information. However, because I am just starting out, I am hesitant to tackle a project of that magnitude...I feel like that takes skills beyond what I have at the moment! So, I have been operating with three separate databases with the hope that they could be combined or reconstructed into a new DB someday.

    So my question is, what is the better approach? Where it gets challenging is that yes, there's certain information I don't want to manually duplicate across three databases (e.g. name, birthdate, etc.) and risk errors that way, but on the other hand not all students will need to be linked into all the other information (e.g. the 80 students who will not be registering for courses in the language program or who don't need to be tracked under our recruiting model). Any advice or ideas would be very welcome! Thank you!!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you post screenshots of the relationship views of your three databases? And how about screenshots of the excel spreadsheets? (you are of course welcome to redact any confidential data)

  3. #3
    officeninja is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Sure, give me a little bit to pull all the info together. I'm working remote on a slow network...

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.......

    Quote Originally Posted by officeninja View Post
    We have 100 students that our office supports. We retain an individual record on each student (Name, birthdate, address, etc)
    Don't use "Name" as a field name - it is a reserved word in Access. "FName" and "LName" or "GivenName" and "Surname" or "GivenName" and "FamilyName" are better.

    Quote Originally Posted by officeninja View Post
    I have been operating with three separate databases
    Maybe you would post the 3 databases you are using? Only need a few example records. Change any sensitive data or make up student info (for example: Daffy Duck, Mickey Mouse, Fred Flintstone)


    How many people are in the dB at the same time?
    Are the databases split?

  5. #5
    officeninja is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Quote Originally Posted by ssanfu View Post
    Don't use "Name" as a field name - it is a reserved word in Access. "FName" and "LName" or "GivenName" and "Surname" or "GivenName" and "FamilyName" are better.
    I was giving examples of the types of info we track for each dB. My actual field names follow a more standard format (LASTNAME, FirstName, FullAddress, etc).


    How many people are in the dB at the same time?
    Are the databases split?
    At the moment, there are 3 users including me (we're a small office). Eventually there would be maybe 6-8 users for those who access the dB for our language program.

    I haven't split the databases yet because I've been learning as I go and wanted to just get the dB's up and running. It's on my list of to-do's, but I can do it sooner if that's more adviseable.

  6. #6
    officeninja is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Okay, here’s what I’m working with. Since I work on a small team and the others don’t really need these DB’s while we’re working remote, I can really dig in and do some serious overhaul on these files. TIA for anyone who is willing to offer feedback

    I realize it’s a beginner’s mess, so please don’t nitpick the details unless something specific will cause large-scale problems down the road. I’m a “trial and error” learner, so I don’t have many paper sketches of my database design (and the ones I do have are stuck at work while I’m home for the statewide COVID-19 quarantine). I just jumped straight in, which I know isn’t the smartest thing. But I’m also the kind of person who is comfortable with scrapping an old design and starting from scratch once I understand more fully how something works. This is true for my job and my personal hobbies :

    Here’s a little bit of extra info that might help interpret my work:


    • The zip file was too large to include all 3 databases, so for my biggest database I exported the main table to Excel and provided a screenshot and relationship report instead; hopefully that’s enough.
    • I needed to remove/change confidential information, but doing so provides very little content to provide useful examples. I also didn’t want to provide too few examples. I tried to leave/change enough so that you can still get a decent idea of what’s going on.
    • The Int’l Database is the one I’ve spent most time on. The other two are drafts.
      • I put all the student info in the MASTER_STUDENT_RECORD (M_S_R) table. It has worked for now, but I’m sure there’s a better way to set this up.
        • For example, instead of a bunch of Lookup fields (like Major, CIP Code, etc) with manually entered drop-down options, taking all related fields out and making them into a separate table, connecting them with a FK. Is that correct?
        • I’m also thinking that the Emergency Contact type of info should be a separate table?

    • The ELI and Scholarship Databases are only “drafts” at this point; my team isn’t using them yet because I'm still working on them.
    • I know my naming standards are sub-par; I’ll iron that out soon.
    • Initially, I tried using the M_S_R in the Int’l DB as the record source for the ELI DB. That way I could have all the same student information between the two, instead of copying/importing the list and having to update two sources.
      • Buuuut, then I run into problems trying to work with only the data that I need.
        • For example, I need to add fields to record info specific to the ELI students, but I don’t need that info on all the other students. I can’t add fields to a query, right? Like, I can’t run a query from the M_S_R to use as a record source for the rest of the ELI DB, because I need to add additional fields. But...maybe it’s more a matter of having the right relationships? I don’t know.
        • I tried going to the original record source (the M_S_R) in the Int’l DB, adding the fields I needed in the ELI DB, and then going back to the ELI DB and working from there. I’m sure I don’t have to tell you how maddening this is.


    There’s definitely more I can explain, but it’s a start. Feel free to ask questions. I did try an Entity-Relationship diagram for the ELI DB, so I included that as well. I know there’s lots of holes in it. And I can also do better with laying out all the attributes I need. I’m gonna keep working on this today in between watching the squirrels outside my window
    Attached Files Attached Files

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I haven't looked over everything (there's a lot to absorb) but see attached for a few comments on your international student db design. I know you've come looking for general advice, but just remember you'll get better help with more specific questions.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 7
    Last Post: 08-02-2017, 02:48 PM
  2. Multiple Operations for a Job Order
    By FlabbyRoach in forum Access
    Replies: 36
    Last Post: 04-26-2017, 08:03 AM
  3. Replies: 5
    Last Post: 02-27-2017, 08:43 AM
  4. Replies: 34
    Last Post: 01-26-2017, 04:47 AM
  5. What is routine database operations?
    By gaosanyong in forum Access
    Replies: 1
    Last Post: 12-13-2012, 02:40 PM

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