Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142

    Design using lookup tables or no lookup tables

    Ok, Lets start over from scratch. I have been attempting to build a data base and everyone keeps telling me to normalize and use lookup tables. I have normalized as best as i could but do not really see the benefit to lookup tables but i',m open minded.

    I manage performance in a warehouse. the associates fill out a daily tracker that tells me what they do, when they do it, how many trips or cycles they did in the time they were doing the job and what exceptions or "problems" they had to stop productivity to deal with.

    Here is an example of a tracker.

    Click image for larger version. 

Name:	11.jpg 
Views:	41 
Size:	81.5 KB 
ID:	35860

    From this I was going to create three tables
    Click image for larger version. 

Name:	12.jpg 
Views:	38 
Size:	86.6 KB 
ID:	35861
    Black box - Associate information
    Red box - daily task (can be up to 15)
    Green box - Exceptions (can be up to 12 per task)

    my issue is that not all associate do the same jobs or the same amount of work. one might do 1 job all day while another ( like a truck loader) might do 15. My thoughts were to create fields for all 15 jobs even though most might be "place holders" if they are not being used by that particular associate that day. I have been told this is not normalization and very inefficient and that I should use lookup tables.

    I do not see how lookup tables will help but I don't know. How do i design this thing so that if one guy needs 1 task i'm not mucking up the DB with 14 fields that are not being used but I can still pull them out when i need them?

    I have been looking all over the interwebs and can not find an example of anything like this that I can look at. I am ready to trash access and just go back to excell which I know.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There is no example attached.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah, you edited the post and added them. The advice to normalize is I think appropriate. Your picture actually lays it out that way. You'd use subforms for the activities and exceptions.

    An example of the lookup tables would be for the task codes and exception codes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    That's not going to limit the number of spaces I'm going to need on my task table. I might have a lookup table with 50 different jobs and job codes on them but on my daily activities table I'm still going to need 15 spaces for potential jobs that the associates may or may not do during the day. so it's not going to limit the number of null spaces I'm going to have cuz I know there's going to be some that I'm not going to use. and we all know that access doesn't like null
    Fields when it comes to calculations and doing queries

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In a normalized app, the activities table would have a record for each activity performed by the person that day. It could be 1 or 100. Your idea to have 15 task fields would mean you also need 15 start fields, 15 end fields, 15 door# fields...you see where this is going? The table should look like your picture, with a record for each task performed with its related data.

    The lookup tables would help ensure people don't enter invalid tasks, and contain other related info, like the task description, etc.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    So your saying each task should be it's own record instead of how I was thinking and having each day be a record? If I do it by the day I will need 15 starts, 15 stops and so on....I didn't think to do each record by task. Will it be hard to create a daily report for each associate with all their task?

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Shouldn't be hard to create a report; it would (could) look exactly like your picture. The top part would be bound to the table with their daily record, the two bottom parts would be subreports bound to the tables containing activities and exceptions. I would expect the links to be either to an autonumber field the daily record, or the combination of employee and date.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    Okay that's good for the activities but what about the exceptions. I'm still going to have to have a table with 12 spaces for each daily tasks for the possibility of up to 12 exceptions a re nt i? That's a lot of null Fields again. How would I handle that?

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Task Sheet "Normalizated"

    My quick cut of this would be Click image for larger version. 

Name:	TaskSheetERD.png 
Views:	34 
Size:	43.4 KB 
ID:	35862 . Note tblTaskHeader and tblTaskSheet can be one table for what I show.

    Things to think about
    1) No Repeating Data eg Task Code is a seperate table.
    2) No Numbered Fields in the Table (1 to 15)
    3) I have NOT added any additional fields (eg Employee Number)
    4) I have NOT place T#in Expections part

    This is a example only. You know your data MUCH better than I

    Have Fun

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You basically never want repeating fields like that. You have the same situation, but the exceptions are related to the task instead of (or in addition to) the person/date. 1 record per exception. As I think about it, they probably have the same relationship as tasks, but have a field to denote which task they relate to.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    On my tracker Form that's what the number on the far left side of the gray field is for. if it's number one that means it belongs to task number one if it's a number two it belongs to task number two At the end of the day everything that belongs to task number one are added up and subtracted from the reported time that the associate said it took them to do the job. So if they say that they took an hour to do the job and they have 20 minutes worth of exceptions it would be 60 minutes minus 20 minutes which means it actually took them 40 minutes to do the job. So in order for me to be able to tie everything together I'm going to have to have some kind of a key, something rather than Auto number that I can assign for each task or each associate or something of that nature wouldn't I?

  12. #12
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Chriswrcg your task number is a link between tblActivities and tblExceptions and it looks like a M:M, meaning it should be tblActivityExceptions. I have done enough for now.


    pbaldy, I didn't mean to cut you off please carry on as I think you are much better at this than I.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @chriswrcg
    (and all others) PMFJI but I was probably one of those who mentioned lookup tables. Here, you wrote
    I do not see how lookup tables will help but I don't know.
    Think of a lookup table as a table that provides a LIST of values (could be one field or more) that you feed into a combo box as its row source. If you set properties on this combo to prevent people from typing in anything they want, you control what can go into the table field that the combo is bound to (its control source). That is pretty much the simplistic explanation for having lookup tables.

    Now for normalization. If you had to add a field to a table because a new task/function/activity was created, then it is 99.95% certain that your table structure is not normalized. You have to forget what you know about arranging data in columns like we do in Excel. I won't agree or disagree with any comments here regarding whether or not a task/activity/function should be treated this way or that, because I think there's a danger of causing confusion by interchanging those words when they don't necessarily mean the same thing to you in the course of all these posts.

    It's unfortunate but understandable that you're having trouble with Access but if it means anything, at one time I was very proficient in Excel and real Access newbie but would now have to say that the opposite is true. Hopefully my peers here will cut me some slack on that. If I may, I'd recommend to you (if I haven't elsewhere) that when you think you grasp normalization post some kind of pic of your design idea. That could be a scan from paper or as involved as a pic of the Access relationships screen after you've set up relationships. I'd keep it simple and go with paper because you run the risk of designing tables and setting relationships that might not be correct. Also, if you don't fully grasp what you've read on relationships so far, look at other sources until you find one that works for you.

    Access and Excel are different tools, just like a hammer and wrench. You wouldn't try to turn a bolt with a hammer, nor does Excel make a good database. Based on your task at hand, that's what you really need. Hope some of that helps.
    Last edited by Micron; 10-17-2018 at 07:00 PM. Reason: spelin and gramur

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,
    This thread is a continuation of a previous thread https://www.accessforums.net/showthread.php?t=74109

    I requested the dB and the spreadsheet, but the OP only posted the dB. Looking at the images in this post, nothing about a door num was in the db posted in the previous post.


    Based on the dB the OP posted, this is the structure/relationships (using Gina Whipp's suggestion from Post #7 plus my mods) I came up with:

    Click image for larger version. 

Name:	Structure1.png 
Views:	23 
Size:	91.9 KB 
ID:	35877
    (edited field names)
    Now that I see the images in this post #1, it would/should be easy to create a form for data entry as indicated.
    Last edited by ssanfu; 10-18-2018 at 06:14 PM.

  15. #15
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    This is what I have so far. What do you guys think?
    Click image for larger version. 

Name:	8.jpg 
Views:	68 
Size:	278.2 KB 
ID:	35874Click image for larger version. 

Name:	07.JPG 
Views:	78 
Size:	63.0 KB 
ID:	35875
    Should I try and create one data entry form or three? Associate information, Task information, Exception information?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with lookup tables
    By Denlkap in forum Access
    Replies: 3
    Last Post: 09-23-2017, 08:18 AM
  2. Lookup Tables - how to use without linking in table design
    By grapevine in forum Database Design
    Replies: 15
    Last Post: 08-29-2017, 01:50 PM
  3. D lookup between two tables
    By punna111 in forum Access
    Replies: 5
    Last Post: 03-26-2015, 09:19 AM
  4. Lookup Tables design help
    By Carouser in forum Access
    Replies: 11
    Last Post: 07-26-2012, 03:40 PM
  5. Lookup Tables
    By JoeBio in forum Database Design
    Replies: 1
    Last Post: 09-01-2006, 07:34 AM

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