• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Help with Access DB

SagaLore

SagaLore

Elite Member
Per this thread I started:
http://www.dbforums.com/showthread.php?t=1215063

I'm creating a checklist system that my department will be using to track who is monitoring what. I wanted to make sure this was scalable so I broke the tables down as much as I could, like so:

Checklists
- ChecklistKey
- ChecklistDate
- HourKey
- TaskKey
- EmployeeKey

Employees
- EmployeeKey
- EmployeeFirstName
- EmployeeLastName
- EmployeeInitials

Hours
- HourKey
- PerHour

Tasks
- TaskKey
- TaskName
- TaskDescription

The form I wanted to create would have the hours listed along a Y axis, and the Tasks listed along the X axis. If those two tables were updated, it would be reflectected by the form. My goal was to make this checklist easy to change but once I started working on the form I realized I had over-complicated it, and it woudl require some hardcore vbmacros to generate the form and interface the tables appropriately. I also put in some sample data in Checklists, and there was going to be a lot of repetition, like so:

ChecklistKey - ChecklistDate - HourKey - TaskKey - EmployeeKey
1 - 4/3/2006 - 8 - 2 - 1
2 - 4/3/2006 - 9 - 2 - 1
3 - 4/3/2006 - 10 - 2 - 1
4 - 4/3/2006 - 11 - 2 - 1

And so on. I calculated that in 1 year, this table would end up with over 65,000 rows - or 260,000 entries.

So I scrapped that, and decided that a simple linear table was the way to go. Since there is only supposed to be 1 initialed task per hour per day, I could have it layed out like so:

DateKey - Task1_hour1 - Task1_hour2 - Task1_hour3 ... Task6_hour1 - Task6_hour2, etc.

If I had 7 tasks, initialed per hour, this table would be 168 columns, or over 60,000 entries per year. Okay so getting better.

Before I started working on this 2nd design, I'm trying to think of better options. One idea I had, was instead of having each person initial per hour per task, they could put in time ranges. So my Checklists table would consist of the ChecklistKey, DateKey, EmployeeKey, TaskKey, ShiftStart, ShiftEnd, LunchBreak, LunchCover (whoever took care of that hour). If the Tasks were kept in a separate table, then that would be about 7 rows per day. If I created a more linear table and put all the tasks in Checklists, that would be 1 row per day of 42 columns. This last idea would make form creation a lot simpler.

So what do you think? Any other ideas or suggestions?
Click to expand...

Its funny that I can go to a dedicated DB forum and wait forver for an answer, but if I come here I will most likely get an answer in under 5 minutes. 😛 So any thoughts?
 
Are you looking for a different way to structure the DB? Or just a better way to create a form for input?

I can see where the rows in any given table might add up, but you could always "archive" to a text file, or to an Archive table, in order to keep the speed up.

I think I'd stick with your first setup, if I understand it correctly, but simplify the form.

Make the form, so you can select a checklist, display the tasks and the current status for that day. Have the time fill in automatically when the user check the completed box (or something similar) instead of listing out the times.


 
Are the individual tasks completed more than once a day?

For example is CheckList 1, Task 1, completed once per day or multiple times per day?
 
Once per day.

Actually, once per shift per day. But, I wanted to break it down even further to one per hour per day, because I want to track who's covering what during lunch breaks, and sometimes people will work extra hours that don't fit into a full shift.
 
I did a mock up (slow day here lol) here's what I came up with...

CheckLists
- CheckListKey (unique id)
- CheckListName
- EmpID (main person responsible for list) - optional

Tasks
- TaskKey (Unique ID)
- CheckListKey (Link to CheckLists)
- TaskOrderNumber (to keep the task list ordered)
- TaskDetails

Employee (I didn't actually build this table in my mock up 🙂 )
- EmpID (Unique ID)
- Name
- etc

CompletedTasks
- CompletedID (Unique ID)
- TaskKey
- Completed Date (Formated: MM/DD/YYY)
- Completed Time (Formated: HH:MM:SS)
- EmpID


I then created a form with 3 List Boxes...
- First List, lists all Checklists by name
- Second lists all tasks associated with the list selected in the first list
- thrid lists all completed dates/times/EmpIDs associated with the task in the second list
- I created a button to Mark the selected task complete, which basically uses the current date/time to timestamp the record and prompts for the EmpID. It then adds it to the CompletedTasks table.


I realize the CompletedTasks table could get fairly big, but this gives you the detail and flexibility I think you're looking for.

If performance bacame an issue you could offload into an archive, since on a daily basis you wouldn't need the data, but it would be available for reporting. You could even automate this process if desired.

I'm still mulling it over, but this is what I've got so far.
 
Its actually only 1 big Checklist, so no need for the first table. I guess the way you have CompletedTasks set up that could work, but thats not much different than the first setup I had which is going to grow too fast. Plus, when I make the form, I need it to show data for the entire day. I don't want to mess with archiving because it will just make it messier generating past reports.

After writing up my scenario and thinking about it some more, I think the most efficient way to do this is this:

Checklists
- ChecklistKey
- DateCompleted
- TaskKey
- EmployeeKey
- TimeStart
- TimeEnd
- LunchStart
- LunchEnd
- LunchCovered (this will be another EmployeeKey)
- Comments

Employees
- EmployeeKey
- EmployeeName
- EmployeeInitials

Tasks
- TaskKey
- TaskName
- TaskDescription

Then the form will have a dropdown for Names, another for Tasks. Then two fields for the start and end time - and then another two for the lunch break and another dropdown for whoever covered lunch for that person. Below the form will just be an output of all entries for the day. Then what I want to do for Reports, is have it generate a graphical representation of the ranges of who covered what. 🙂

I supposed I could remove the lunch break part, and force everyone to submit a total of three ranges. ShiftStart - LunchStart, LunchEnd - ShiftEnd, and LunchStart - LunchEnd (for the person you covered during their lunch break).

Which way do you think I should go?
 
Does each person only perform one task per day/shift?

I think 3 time ranges would get confusing, plus the way you have it, you'll know who they covered for.

As far as a graphical representation... what are you trying to show... How much time they spent performing each task? How many tasks they performed? I'm having a hard time turning this into a graph in my head... lol
 
Originally posted by: theknight571
Does each person only perform one task per day/shift?

I think 3 time ranges would get confusing, plus the way you have it, you'll know who they covered for.

As far as a graphical representation... what are you trying to show... How much time they spent performing each task? How many tasks they performed? I'm having a hard time turning this into a graph in my head... lol
Click to expand...

This is just a checklist to show that a task was covered all day. The employees typically are responsible for a single task, and that task is only assigned to one employee at any given time (lunch breaks the exception). There be another administrative task to two and multiple people will cover at the same time, but it won't cover an entire shift.

The report I'm thinking of based on the tables I have come up with above, would have the Tasks listed along a Y axis (left side), with one hour increments for 24 hours across the X axis (the top), and solid bars color coded to represent Employees (with a key somewhere on the page). Fun huh?

Or, instead of solid bars, every hour increment in the "grid" will have the Initials of the employee. Considering these reports will be printed to a black toner printer only, that might be the way to go. 😛
 
Would you want to record what task they were covering and not just who they covered for...although you could probably figure it out.

So...any given task (for the most part) would have 2 large time blocks (3 if 3 shifts) and 2 (3) smaller "1 hour" blocks to represent lunch covererage.

Something like....

............... ----------------------------------- ---- ----------------------------------- ---- ----------------------------------- ----
TASK #1 : |............Emp 1.....................| E2 |...............E3........................| E4 | ................. E5....................| E6 |
............... ----------------------------------- ---- ----------------------------------- ---- ----------------------------------- ----
............... ----------------------------------- ---- ----------------------------------- ----
TASK #2 : |............Emp 2.....................| E1 |...............E4........................| E3 |
............... ------------------------------------ ---- ----------------------------------- ----

Indicating that Task #2 was missing some coverage that day, since there are only 16 hours displayed in the chart.

I'll have to dummy up some data and play with it.

I'm leaving for the day.... I'll take a look later if I can, or tomorrow if I can't get to it tonight.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top