Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    29

    Unanswered: How to handle data that now needs to be split up for specific cases?

    I have a small database I use to track some cleanliness issues in our facility. We keep track of the deficiencies through a multi-select combo box.

    Example the break room and bathroom are both dirty so we check those 2 boxes out of the 20 or so options.

    Since that list is a table I run a report against a query based on that table to get the "current inspection form"

    Later I can query and see that the bathroom always shows as dirty.

    Pretty much everything worked just like I needed until now. I have been asked to track the same things but now I need to split up responsibility for some of those items. The split up is always the same; for simplicity sake we can say that the same 4 people all have an interest in each of the areas that are now being split up.

    So on the sections that need to be split up, I now need to be able to provide 4 check boxes instead of the 1 check inside the combo box.

    I realize I can probably manipulate the form in VBA to show what I am asking, but I am really interested in how do I modify the table structure to make this work. I would like to not rewrite the DB. I know this is a good example of why not to use multi-select combo boxes, but I need to move beyond that.

    What I wanted to do was simply add a table to show the responsibility parties split and add a yes/no column to the various inspection items that indicate the split should occur, but I could not figure out how to store the data after that. Also I could not figure out how to make the sub-report appear only when the split box was checked yes.

    Any help you can provide is greatly appreciated.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Without knowing the structure of your database, it's difficult to advise on what you need to change. That said, it sounds like you need to split the required task(s) from the parent incident.

    Can you make a structure-only copy of the database and upload it?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jul 2007
    Posts
    29
    As request I have uploaded a stripped down version. I know that I have unused columns in some of my tables. There were part of a previous experiment that never was cleaned up.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sorry, but I don't think I'm going to be able to help. You haven't enforced referential integrity, and you have multi-value lookup fields in tables. I haven't used the latter (other than within Lotus Notes databases, where there isn't a choice), and my instinct would be to rebuild this.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is it clean or not
    does it need 4 separate boxes for 4 people, if so why
    what happens if a 5,6 or more are added

    define who is responsible for a facility
    make your metrics and record who did the observations

    if as Weejas suggests the db design is a mess Im not gong to open it or look at it.

    instreade fo four check boixes I'd expect a combo/list box that identifies the facility
    a combo box that identifies the person
    both of those should be foreign keys to master tables for facility and person

    if you want to assign responsibility for a facility to persons then do so through an intersection table

    if you need to store similar but not identical attributes then consider using the sub/supertype model.

    the modern Access versions 2010 definitely but possibly 2007 also are less of an issue using the look up wizard. in earlier versions the lookup wizard should be avoided
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •