Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104

    Question Unanswered: Help with complicated Db idea

    or at least I think it is.....

    Here's what is going on. We audit companies every year (sometimes even more often). The audit tool/questions are always changing and each company has more than one audit related to it. Each question on the audit has a result of what needs to happen if they do not pass that question. Some questions have more than one part or a few bullets for that question. The company could easily pass 3 of 4 bullet points and only have to fix the one problem.

    Anybody have ideas on how to set this up? I thought about creating a separate table for the audit tool because it is always changing, but I don't know how to relate it back to the group with answers....

    HELP! =)

    Well I was playing around and thought the most effective way to do the audit tool was with multiple tables (see attached), but then that makes the answers difficult to set up for each individual audit. Has anyone ever created a survey type situation that I could alter for this purpose?
    Attached Files Attached Files
    Last edited by Jennay; 02-02-05 at 01:33.

  2. #2
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104

    Unhappy

    Still no luck huh?

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's a stab at it from what you've said so far:

    tblQuestion
    idQuestion, auto, PK
    strQuestion, text, whatever the question is

    tblBullet
    idBullet, auto, PK
    idQuestion, FK on tblQuestion
    strBullet, text, whatever the bullet is
    'making life simple - every question MUST have one or more bullet


    tblAsked:
    idAsked, auto, PK
    idBullet, FK on tblBullet
    idAskWho, FK on some table that identifies who got asked this instance of this question(bullet)
    datAsked, date, when you asked this question
    boolOK, y/n, did you get an acceptable response


    tblResponse:
    idResponse, auto, PK
    idAsked, FK on tblAsked
    datResponse, dat, when you got this answer
    strResponse, text, whatever the response was


    does that work?

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Nov 2003
    Location
    Moorpark, CA
    Posts
    104
    It is a tad more complicated there is actually 3 tiers to the question

    I. Question -
    Part A -
    Bullet 1 -
    Bullet 2 -
    Part B -
    Bullet 1 -
    Part C -

    Think of it as kind of an outline. Each question has at least one Part, but not all of the parts have a Bullet point. The bullets would have a yes/no to signify whether that point was passed. The Parts would have a score for that part. Also, eventually the Question itself will have a % correct based on the scores from the parts and the total possible for that question/part. It's not actually a survey where we would ask the questions or anything. It is an audit so it would be us scoring them on their performance on one day at least once a year. And again the questions, parts, and bullets are subject to change. If it is easier to create, I'll come to terms with just changing the tool as needed instead of keeping the correct audit with the correct scores.

    ANY help would definitely be appreciated!

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    This sounds like a dynamic classification system (ie - a query-processing framework that will classify sets based on rules you can change dynamically). The way to process these is to use a rule table.

    I am just beginning to toy with these myself - so I'm not the best person to ask, but the source I'm using for a guide is chapter 2.13 of the Transact-SQL Cookbook (Gennick,Spetic - Oreilly Press).

    Contact me if nobody else is able to help you and you can't find this book. I think I might have a pdf of this chapter that was posted as an excerpt somewhere.
    Kit Lemmonds

Posting Permissions

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