X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Creating A Table With Fields Based On Data In Another Table - Scheduling Project, Access 2016    
post Nov 6 2019, 02:42 PM

Posts: 1
Joined: 6-November 19

Hello! First time poster here. I tried to make my title as specific as possible, but there may be a larger scope to the issue I'm trying to solve.

As a preface, I am SO excited to have found these forums! I originally created the project I am working on in excel but my Mum suggested Access would be a better fit for taking it to the next level. Unfortunately, I have little experience with Access and am just now beginning to wrap my head around all its capabilities. I have been watching tutorials on Lynda over the last few weeks and have scoured the internet for some ideas to prompt a solution but I have hit a wall. I have read through all the archived code posts including "appointments" and "schedule" on UtterAccess and have found a lot of really cool resources! I'm still seeking a solution for the roadblock I hit though so I figured I would post and see if anyone can help me conceptualize what I'm trying to do and point me towards a solution. I have read through all the rules and guidelines for posting and I apologize in advanced if I make any mistakes.

The Project: A semi-automated scheduling program capable of editing/storing employee availability, adding/editing/deleting shifts that need to be filled each day of the week, and assigning employees from the availability database to shifts based on user input.

What I have so far:
-The full "employee availability" database piece. I have a table & connected form with fields for first name, last name, availability for each day of the week, and location availability. The user is able to flip through all employee availability records by using two buttons (next & previous), as
allow them to update employee availability as it changes.
-What I am calling a "Shift Builder" for each individual work location- A table & connected form with fields for shift name, start time, end time, and days of the week the shift is utilized- a few other fields as well but none relevant to the main function of the database. Similar to the
employee availability piece, the user is able to flip through entries to update shifts fluidly. Buttons allow the user to delete a shift if necessary and save a shift.

The Vision: I want to be able to take information from the shift builder and create another table & form to allow the user to assign employees from the availability table to shifts from the shift builder. In more words, I want to create a table with a field for every record in the shift builder, for every day that shift is utilized for. The concept for how this would work start to finish is that the user would enter in an employee's availability in the availability form. Then they would enter a shift in to the shift building form, for example for a shift M-F from 10A-6P. Then, they would go to a separate "Assignments" form of sorts (connected to a table), that would have updated based on their input in the shift builder and would now display a shift for Monday - Friday from 10AM-6PM and would have a field to assign an employee to that shift for each day, so in total 5 new fields. The user would then be able to chose an employee to work the shift for each of those days. I hope that made sense, I have been struggling to articulate this, as it's a difficult concept for me to wrap my head around.

My Solution: Based on my experience with doing this project in excel, the way this would work in my head would be a series of functions or macros following these steps:
-Count the number of shifts created
-Set n equal to the number of shifts created
-Run a loop that runs a macro that creates 1 "shift" worth of fields (7 fields - 1 for each day of the week. Some would not be utilized but I was going to try to come up with a solution later down the line for that problem) in the "Assignments" table. The loop would repeat "n" times, thus
creating a field for each day for each shift.
The user would then be left with a form where they could assign an employee from a dropdown menu to each shift, each day. I'm planning long term to have a series of queries that will reference the data in the employee availability table to only allow employees who meet certain criteria to fill each shift, but that will be the next step. I'm not worried about that quite yet. When I called my Mother (resident Microsoft Genius) to tell her about my solution she laughed and said "In theory, yes, but that's not how Access works. There's a better way to do this". That being said, she lives across the country and runs her own company. She told me to do my research then ask the internet for help.

So here I am, asking for help! I have done my very best to articulate what I'm trying to accomplish here and I hope this makes sense. If not, I am happy to answer any questions you might have. I'm coming from a strange mix of programming backgrounds and I've spent the last 2 months pouring my free time in to learning how to use Excel to its fullest extent for this project. The time has come for me to conquer my fear of Access, as this program is more than capable of doing everything I want to and then some. Over the past few weeks, while I have not found any solutions to this problem, I have started to gain a stronger conceptual understanding of databases and how they work, and I've gotten to the point that I am somewhat comfortable with access. I have a lot of free time at work which I've put towards playing around and seeing what I can figure out based on trial and error.

I appreciate in advanced anyone willing to share their experience and problem solving with me. Even without anyone directly answering my questions, this forum has already been a huge help to me and I am grateful to have found it!


Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    6th April 2020 - 10:45 AM