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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> 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
post Nov 6 2019, 03:39 PM

UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill

Hi Jordan,


It's great that you have a good idea of what you're after.

However, I would like to stress to you that Access is not Excel on steroids!

With Access, it is very important to get your data structure down first, before thinking about forms and nifty functions.

This can be quite difficult when coming from an Excel background/mentality.

Often, how data is best stored in tables will differ from how it is displayed to the user.

Please take some more time to go through our newcomers' reading list

There are many here who will be willing to guide you through the concepts of structuring your tables correctly for your goal.

Also, beware you are tackling a relatively complex project, so it will take a little time to get right - but really rewarding if you go at it properly.





David Marten
Go to the top of the page
post Nov 15 2019, 02:53 PM

Posts: 277
Joined: 27-September 01

The table structure for shifts and availability will depend on what exactly is a shift, and what the possible shifts might be, and whether the times an employee is available match a whole shift, or might overlap. I didn't quite understand it from your description. If "M-F, 10-6" is a shift, and you need an employee who is available "M-F, 10-6", then you just need a list of shifts, with a text field describing the shift, perhaps a numerical order field, and a key. The actual text won't matter to the table structure and queries. But if "M-F 10-6" is a shift, and "M, W, F 2-10" is also a valid shift, and you might need someone W from 4-5, then you need actual numbers and a way to compare them. You would then also probably need functions that would convert display times into numerical values that you could compare. The Date/Time data type might not be useful in this case. The term "shift" implies that workers aren't allowed to choose any old set of hours to be available.

In short, figure out exactly what are the rules and possibilities for shifts and availability, and be able to explain them. The table structure will follow from that, and the means of building everything will follow from that.
Go to the top of the page
post Nov 18 2019, 10:16 AM

UtterAccess VIP
Posts: 14,034
Joined: 6-June 05
From: Dunbar,Scotland

Hi Jordan

Can you upload a screenshot of your relationship diagram?

Hope this helps?


Get happiness out of your work or you may never know what happiness is.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    12th July 2020 - 05:16 PM