Results 1 to 7 of 7
  1. #1
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Table design for a dates driven workflow in Access

    Hello, we need to create a workflow in Access which will be published in SharePoint some time in the future. The entire workflow has 20 different tasks items each with a "due by" deadline date. We want to track if a task has been completed as well as the date of completion.



    The 20 tasks items are shared among 5 departments. For starters, I have created 5 tables (each of the 5 departments) and created each field within each table to correspond to the task assigned to that department, for example my largest table, Logisticstbl, has 7 fields that will store the 7 out of 20 tasks items that they are responsible for completing by "dd/mm/yy" deadline.

    The concept sounds simple. Each task will look something like this: Was "xyz" task completed (yes/no)? If so, what is the date of completion (calendar to select date of completion).

    In my table design I'm not sure if I should create yes/no fields as well as dates fields, or should I create a separate dates table? Any suggestions would be appreciated. Thanks.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you got off on the wrong foot. You should have one table for departments where the department field is unique and only one for tasks. If your tasks are currently mapped out in fields, this is wrong too. It would indicate that you are approaching this with a spreadsheet mentality and that you really should research database normalization or you are likely doomed to create a frustrating project. Probably you should research reserved names as well http://allenbrowne.com/AppIssueBadWord.html#D

    The tblDepts table would have a PK (autonumber) and record indicating each dept. tblTasks should be a source of tasks that can be added to in rows as process design changes are made. tblTaskStatus would be where you "assemble" tasks, assign departments, add comments, flag as complete, etc. If comments are involved but are optional, I recommend tblTaskCmnts as well. As I said, very important for you to understand normalization. Google it and check out as many as you have time for, and until you find one/some that you can relate to given the needs of your project.
    Edit: yes/no fields are ok for something like statusing (although a table of task status' would be better) but not for options such as user options.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Micron, I felt that I had a poor design and your suggestions were very good. I've created a tblDepts table and have three fields: DeptID (PK) field, a second field called, Department and a third called, tblTaskID. I also created a second table, tblTask with fields: tblTaskID (PK), Timing, Completedbx, and Date. I'm not sure if I need the tblTaskStatus table. Can you please elaborate on how you would use the tblTaskStatus table? Thanks.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    tblDepts table and have three fields: DeptID (PK) field, a second field called, Department and a third called, tblTaskID.
    Why are you naming fields with a prefix that indicates a table ("tbl")?? If that field is a FK to "tblTask", I would suggest naming the field "TaskID_FK".

    Could get confusing... I'm just saying......

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your names may not be confusing now or ever (to you) but ssanfu's suggestion has merit anyway, because you have to guess whether or not a field is a foreign key or not, or you have to open the relationships window and check there, assuming you have created the relationships. What wasn't commented on is that after cautioning you about reserved names, you have a field called Date. This is so common it's not funny. Being lazy, I like DeptID for a field name, and Dept as the department field name. Point is,if you make up abbreviated field names you will almost never fall into this trap. Make Date field name Dte, or ComplDte for example. The alternative is to further identify how Date relates, such as TaskDate, WoDate. In both cases, notice I employed a convention that switches UpperAndLowerCase between distinctive parts. Helps for when double letters don't look right. You should now be able to guess what ComplDte means. Back to the suggestion: I try not to use the underscore (I said I was lazy) so I might use fkTaskID though to be honest I haven't made identifying foreign keys a priority, which is being too lazy, I guess. Now for the normalization part (I am going to call your field TaskID).

    Is TaskID related to a table of departments? No! So it should not be there. The task is its own entity so it belongs in its own table. Why have tblDepts in the first place? Because if you add a dept or it's name changes, you simply add another row or change the dept name. Since the DeptID does not change, the name matters not because you will be using DeptID everywhere in order to relate, NOT the name. This is how data is supposed to be organized and related.

    tblTask is where DeptID should be for TaskID 1234. Which dept was that assigned to? 5 is what you'd see there, which is whatever department name is on row 5 of tblDepts. What you show in your forms/reports is not 5, but the name that you have included in the query results.

    As for tblTaskStatus, TaskID (PK) Status (e.g. Unapproved, Approved, Fini, Complete, Cancel, etc.) and maybe TaskSort (sort order if situation warrants). What status would appear in any given record for a task in the task table? Complete? No, the PK from the status table (maybe 4 for "Complete"). Understanding normalization a bit better? Note: Sort order could be very valuable here. F'rinstance, if you want to enforce that someone cannot choose "Complete" for a combo box IF the task is not even Approved yet, you'd use some type of validation that does not allow a status if its sort value = 1 AND doesn't allow if less than the current sort value AND doesn't allow if the difference > 1. Try doing that by sorting alphabetically!

    Tip: I often used Excel to map out a project if it had a lot going on, but I don't suppose that's for everyone. The column headers are table properties. Sometimes, it became evident that a previously defined data type/size was not going to work somewhere else, so it was easier to go back, find it, and correct it. Probably was more useful maintaining consistent data properties when FK creating fields in other tables. Here's what one row might have looked like.
    tblName
    Fld Name Desc Caption Data Type PKey FKey Ind Req'd Dupes Fld Size Dflt Zero Length
    courseHdr courseID TXT Y Y Y 15 NULL

    Only then would I start creating tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks for suggestion VIP, I document definition of all keys within my db, no harm on this one. Thanks.

  7. #7
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks Micron, making good progress.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-13-2015, 02:25 PM
  2. Replies: 7
    Last Post: 12-10-2013, 02:45 PM
  3. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  4. Database driven website....new ground for me
    By avarusbrightfyre in forum SQL Server
    Replies: 1
    Last Post: 10-09-2011, 08:19 PM
  5. Problem with parameter driven combo box
    By clydet2 in forum Queries
    Replies: 0
    Last Post: 04-06-2009, 12:19 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