Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2007
    Posts
    7

    Unanswered: Beginner Help with Database Design

    I am creating a database to help handle the following task.

    Every quarter every employee submits rankings (5 categories) for multiple companies that we work with. You can imagine a spreadsheet for each employee with the companies going down and the rankings for each of those companies across. Then you would have duplicates for each quarter. Each employee would have set of rankings per broker per quarter.

    What I want to be able to do is 1) collect the information via webpage or excel or ?? - i.e. send out a link to page where the employee would then select rankings for each company which would then dump into database. The employees would also have to have the option of adding a new company that they worked with but we didn't know about.
    2) Take that information and create average rankings for each company based on period and be able to compare...so Company A had average ranking of 2.5 in Q1 but 3.7 in Q2


    So far I have done the following (* denotes primary key)

    Ranking
    Employee*
    Quarter*
    Company*
    RankingCategory1
    RankingCategory2
    RankingCategory3
    ...
    ...

    Periods
    Quarter*

    Employees
    Employee*

    Companies
    Company*

    Relationships are
    Periods:Quarter -> Ranking:Quarter
    Employees:Employee -> Ranking:Employee
    Companies:Company -> Ranking:Company

    Does this make sense? Any advice? I have not been able to come up with a form that would allow each employee to go down the list of brokers and enter ranking without having to enter in his name in each record and the quarter in each record.

    Help!

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    create an employee table with an employee id
    create a ranking table that has all the possible rankings
    create a company table with a company Id
    create a data input table that
    allows the user to select from combo boxes
    1)the employee
    2)the company
    3)the ranking
    4)availability to insert the date

    using the format function you can format for a quarter
    for instance
    format(10/29/2007,"Q") returns:
    4
    so then you can run query that gets the average rankings of all companies for each quarter
    Dale Houston, TX

  3. #3
    Join Date
    Oct 2007
    Posts
    7
    Would the ranking table then contain 6 Fields for various ranking criteria AND Fields for EmployeeID and CompanyID?

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    one field for ranking
    one entry - one ranking
    Dale Houston, TX

  5. #5
    Join Date
    Oct 2007
    Posts
    7
    I am not sure I understand.

    The rankings are 6 categories. Each employee must enter a ranking of 1-5 for each of the 6 categories. Why not just have all of those categories as separate fields under a Ranking Table?

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    here is why
    6 fields are harder to maintain and collect data from than 1 field

    when totalling or averaging numbers it is easy to tally and calculate numbers coming from one field than to have to take into account 6 fields
    use a combo box on the form to select the ranking this combo box's data source is the ranking field - on field 6 choices is easire than 6 fields on choice each.
    Dale Houston, TX

  7. #7
    Join Date
    Oct 2007
    Posts
    7
    sorry to be a pest. But I don't know how I can implement that. And to be clear - each ranking category is separate from the other. I only want the averages for each category. So for instance...each employee must rank each company based on Responsiveness, Quality of Information, Customer Service, etc.

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    right. Each one of those fields can receive a ranking of 1 -6, and of course you can implement that - you are the designer

    the way you want to do this is a 6 fields for each item that is ranked which equals 6 x number of ranked fields. The way I am proposing only calls for the number of ranked fields - far easier to maintain.

    The way you are approaching this is called a "flat file" (excel is an example)seems easy now , but gets difficult to maintain

    company Employee Ranking Date Responsiveness Customer Service
    mycompany Bob 10/29/2007 5 3
    mycompany Joe 10/29/2007 3 2
    mycompany Sam 09/12/2007 4 4

    doesn't this seem easier to maintain?
    Dale Houston, TX

  9. #9
    Join Date
    Oct 2007
    Posts
    7
    Thanks Dale,

    I think we may actually be thinking the same way if your last example was how I should be doing it. What I have is a table that has

    Company | Employee | Date | Responsiveness | Customer Service | Cat 3
    company1 | Bob | 2Q07 | 3 4 5
    company2 | Bob | 2Q07 | 2 1 1
    company1 | Jen | 2Q07 | 1 2 3
    company2 | Jen | 2Q07 | 2 3 4
    company1 | Bob | 1Q07 | 2 1 3

    I then created a query that takes the period as the parameter and the output is the average ranking for each category during a particular period.

    My next problem is creating a form that an employee would use to enter the ranking data on all the companies. I want to be able to have a subform with all the above data except keeping the employee and period static based on selections made at the top of the form. Rather than having to enter all of that data for each company the employee ranks.

  10. #10
    Join Date
    Oct 2007
    Posts
    7
    Dale,

    I think I found the light. I think I understand what you were saying now. Especially since not every employee will rank every company on all categories I realized that there was a lot of empty space. Please tell me if this is what you were trying to get me to understand.

    Now I have a seperate table for the various ranking categories.

    The new rankings table now just has the employee, company being ranked, the date, categoryID and the rank.

    So now each entry in the table relates to ONE ranking.

    rather than each entry having all the rankings for every category.

    am I on the right track?

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    postberg,

    What Dale is talking about without saying the word is Normalization. Please follow the link in my signature titled Access Design Tips. On the linked page, follow the first link there. This is an EXCELLENT paper on normalization. Read and study it, as normalization is the foundation of all well written database designs. Whatever time you spend learning normalization will be more than saved later in your database work.

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    that is correct - good luck
    Dale Houston, TX

Posting Permissions

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