Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Unanswered: My Poorly Designed Query

    G'day Friends,

    I'm working on a database to record and track an employee recognition program. When an employee is witnessed doing a good deed, a manager will give the employee a "check" for one of 5 categories (columns 4-8 in the Champions picture). Once an employee achieves a check in each category they are given a pin. Multiple pins are achievable.

    I have the Champions query set up in a very poor manner and it is hard to deal with. Right now I have 5 separate queries to find the number of checks given to each person based on Subcategory, so one query that finds the counts for Heartfelt, one that finds the count for Innovative, etc. Then I find the total by adding those numbers up. Finally, the pin number is found by taking the minimum of the different Subcategories. It's very messy and I'm having trouble referencing the different fields in my forms and whatnot. Anyone have any ideas on how to more easily find the number of checks an employee have received in a particular subcategory from the table Checks (pic included)?

    Any help would be much appreciated.

    Attached Thumbnails Attached Thumbnails ChampionsDesign.bmp   Champions.bmp   Checks.bmp  

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    redesign the db so it conforms with the principals of normalisation
    you don't need a separate table for each pin/reccommendation type a single table will suffice. you will need a 'parent table which identifies the pin type however

    if in doubt read
    Fundamentals of Relational Database Design --
    The Relational Data Model, Normalisation and effective Database Design

    also Rudy's book Simply SQL is worth a read, but probably not worth it if this is an assignment (not becuase it isn't worth reading, just by the time it arrives you've read it the term will have ended)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2009
    Figured it out....all I needed was a crosstab query. Thanks.

Posting Permissions

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