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)?
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
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