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)
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.
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
4)availability to insert the date
using the format function you can format for a quarter
so then you can run query that gets the average rankings of all companies for each quarter
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.
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.
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
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.
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.
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.