Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like this has been continued:

    https://www.accessforums.net/showthread.php?t=74156



    so please add any followup there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Missed that...

    Deleted this post and move to other thread. Thanks Paul.

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No worries. Looks like it hid on a new page.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    Sorry for any confusion. I thought since the topic had changed from design to forms I should move the question. I forgot to delete my last entry on this thread.

  5. #20
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    If you use lookup tables in a split database does that mean everything will be slowed down as every time your form tries to access that table it will have to look into the backend?

  6. #21
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    This is the design as it stands right now. Is this build stable or do I still need to modify it?
    Click image for larger version. 

Name:	15.JPG 
Views:	17 
Size:	70.3 KB 
ID:	35888

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by JRodko View Post
    If you use lookup tables in a split database does that mean everything will be slowed down as every time your form tries to access that table it will have to look into the backend?
    You would need an atomic clock to notice the difference unless (maybe) you had issues with network speed. If that were the case, you would already be complaining to someone. The bigger payoff comes from being able to control what users can choose (and even enter if designed properly), plus not having to redesign a form to add an option to a control's list should you otherwise take that approach. In fact, it should be far faster than searching through thousands/millions of rows with a DISTINCT predicate on a combo's row source.

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Admittedly I've not been dissecting all the responses but could share an observation or two. Would you ever need additional "interruption" options besides lunch or break1/2? If so, you definitely have those in the wrong table. What if you needed to add 'meeting attendance' or 'medical visit' or something else? If you think not, then you may be ok with what you have.

    The other observations would be
    a) be consistent with your spelling. You may find that it causes issues when designing/troubleshooting when you forget that exception and associate is spelled differently in just one place.
    b) you would find it easier to design/troubleshoot if you could more easily identify controls from one another, or tables, or queries, etc. I suggest you adopt a more standard naming convention. You can raise errors when Access has to decide what you mean by TaskCode although the issue is not real common. I say this because you don't use any convention with your table names, so I wouldn't expect you'd do so with controls. If I use the form wizard to make a quick form, I go through all of them and rename them according to my convention. Here's some info on naming things (sorry if I've already posted these to you) :

    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

  9. #24
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    You asked if this is ready, and I give you the following points/questions:

    1) In tbl TaskExcpitions you are stating that for a AssociateID will have an ExceptionID, ExceptionCode and a ExceptionDuration. So that means for each different Duration, a new record will be required in that table. Are the ExceptionDuration a standard length, or AssociateID always does it the same length?

    2) In tbl WorkDays, the value of WorkDate will be repeated for each AssociateID that worked that date. Do you see any additional Information require around the WorkDate (weather, staff level (sickness), unusual work occuring (door repair, stock count) that would effect everyone?

    3) In tbl AssociateList will require a new record each different shift that person works (and for each Department/Subdepartment).

    4) Something feels wrong with TaskActivities around TaskListID and TaskCode.

    A good method of checking is to create a Excel workbook with a sheet for each table. Enter some data (1-3 dozen records) trying for different types of things, and then try joining them using Vlookup (or Index/Match). If you see a lot of repeating data you may not be normalized, if you can't get the joins to return the correct data, then the structure may not be correct. Don't worry about grammer or format, just does it work.

    Please note that I use MAY. Like all things there is a BEST, Better, Workable, and Poor ways of doing it. You are the boss, you know the data, the way this will be used, the growth potential.

  10. #25
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    Let me take each one of these at a time.
    1) In tbl TaskExcpitions you are stating that for a AssociateID will have an ExceptionID, ExceptionCode and a ExceptionDuration. So that means for each different Duration, a new record will be required in that table. Are the ExceptionDuration a standard length, or AssociateID always does it the same length?
    Remember I am entering daily task activities for each associate. I am using the AssociateID number to tie each day together since a new one is generated every time I enter in their data. During the day each associate can have multiple exceptions during each task they perform and each one at varying lengths of time. This way I can enter in as many exceptions as I need for the associate for that day and the AssociateID ensures that it is all tied to the same day.

    2) In tbl WorkDays, the value of WorkDate will be repeated for each AssociateID that worked that date. Do you see any additional Information require around the WorkDate (weather, staff level (sickness), unusual work occuring (door repair, stock count) that would effect everyone?
    On the back of the associate daily tracker that they fill out is a table that list all the agailable exceptions they can use during the day and the codes to show which one they are claiming or can claim during the work date.
    Click image for larger version. 

Name:	17.JPG 
Views:	14 
Size:	90.4 KB 
ID:	35906

    3) In tbl AssociateList will require a new record each different shift that person works (and for each Department/Subdepartment).
    This is by design as I will be entering daily activity for each associate. In the end I will build my reports to show performance % from the individual task level. to daily, to weekly, to overall monthly average for each associate.

    4) Something feels wrong with TaskActivities around TaskListID and TaskCode.
    Need to be more specific.

    And lastly, I am recreating this from a very extensive excel workbook that I have been building and perfecting for the last year. What started out as a simple spreadsheet now has mind of it's own almost. Its is close to a smart spreadsheet. There are auto generated individual associate worksheets, reports, and data archives. The spreadsheet can auto populate itself when adding new associates as well as remove any trace of them when they leave the company. And tons more. I am fairly proud of it. I know how the data comes in and how the data flows throughout the system.

    But my greatest accomplishment is also my greatest hindrance. Excel is not very stable and needs to be fixed a lot. I am looking for a better way to work with the data and access is it. I am fighting it though, every step of the way, because I am trying to build a spreadsheet in a database and it's not working. I am hoping you guys here can help beat the excel out of me and show the the way in access.

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suspect you are at the point where you should backup the db as is, then try entering & modifying data and see what you get when you use it in every possible scenario that you can think of. The level of uncertainty on our part as to how things will really work probably isn't helping you a whole lot at this point.

  12. #27
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Great! Its all done. sarcasm off

    I don’t need to know why you are right or correct, but I pointed out areas that I thought you needed put some more thought in. A single table can be made to work.

    I wasn’t saying to convert to excel, but to use it to check both normalization and structure. (What Mircon is saying to)

    Looking at it now I see your field names tripped me up on the first one. The fourth one has the same two fields in both tables.
    (See Mircon’s post on conventions)

    Second and third ones need you think, just not what you have now, but outside the box too. If you are okay with what you have then GREAT.

    There is no right or wrong in access (just like excel), just many ways of doing it, some pretty and some pretty ugly.

Page 2 of 2 FirstFirst 12
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