News0 min ago
Access Query Problem
3 Answers
Hello,
I'm trying to do the following in Access 97:
Table = [Reps] rep id, name, team
Table = Grades rep id, month, grade
Table = [Grades] rep id, AveOfGrade
When a rep makes an error, points are allocated against the error, that then translates into a grade at the end of each month.
Assume that the Grades table only holds data for the current year.
I have a query that pulls all the grades & gives an average year to date grade.
This only works for reps who have made an error (therefore allocated a grade).
I want to pull a query that lists every rep, including those with no errors/grade, showing either a blank or a 0 under grade for those & the actual YTD grade for everyone else.
Can it be done & how?
Thanks for any help.
I'm trying to do the following in Access 97:
Table = [Reps] rep id, name, team
Table = Grades rep id, month, grade
Table = [Grades] rep id, AveOfGrade
When a rep makes an error, points are allocated against the error, that then translates into a grade at the end of each month.
Assume that the Grades table only holds data for the current year.
I have a query that pulls all the grades & gives an average year to date grade.
This only works for reps who have made an error (therefore allocated a grade).
I want to pull a query that lists every rep, including those with no errors/grade, showing either a blank or a 0 under grade for those & the actual YTD grade for everyone else.
Can it be done & how?
Thanks for any help.
Answers
1 to 3 of 3
Best Answer
No best answer has yet been selected by tpreece01. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
Firstly, drop the third table as it is redundant. The average can be calculated from the second table on the fly. When you get to a few million Reps you might consider using the third table to save time generating the average if you want to consult the data between matches. ;-)
Also drop spaces from field and table names as it saves typing. [Rep ID] is better as RepID.
In your query you will need a join between the RepID fields of the first two tables. In the Query Design, click and drag this field from one table to the other and a line will join them.
This default join will only show Reps listed in both tables. You need a join that shows all Reps. Right click on the line that joins the tables and select Join Properties.
Three choices will appear. You want the one that says:
"Include alll records from Reps and only those from Grades where the joined fields are equal". This will cause your query to result in a blank (known as Null) for those with no errors.
Drag the fields you want to the grid area or choose them from the drop downs.
To show the average, right click on the grid area at the bottom and turn on Totals. This adds another row called Totals. Existing fields will show "Group By".
Drop down the Group By in the Grade field and select average. This will give you one result per Rep with the grade average.
Also drop spaces from field and table names as it saves typing. [Rep ID] is better as RepID.
In your query you will need a join between the RepID fields of the first two tables. In the Query Design, click and drag this field from one table to the other and a line will join them.
This default join will only show Reps listed in both tables. You need a join that shows all Reps. Right click on the line that joins the tables and select Join Properties.
Three choices will appear. You want the one that says:
"Include alll records from Reps and only those from Grades where the joined fields are equal". This will cause your query to result in a blank (known as Null) for those with no errors.
Drag the fields you want to the grid area or choose them from the drop downs.
To show the average, right click on the grid area at the bottom and turn on Totals. This adds another row called Totals. Existing fields will show "Group By".
Drop down the Group By in the Grade field and select average. This will give you one result per Rep with the grade average.
Ci=ontinued:
However this will give you the average the months where the Rep actually had an error record. Not quite what you want.
Also would it be fair to compare the total number of errors where the reps have played different numbers of matches? You really should enter zero where a Rep has played but made no mistakes. Then what about the length of time they spent on the field?
To get a fair average you would need to divide the number of errors by the time they spent on the field.
This is getting out of hand isn't it? Hope I have helped a little at least.
You are on a steep part of the learning curve. You probably need to think a lot more about what you are really trying to determine. It is dreadfully difficult to help remotely at this point without really knowing what a lot more about the context of the data.
I have worked in Access for years and every day I still learn new stuff.
However this will give you the average the months where the Rep actually had an error record. Not quite what you want.
Also would it be fair to compare the total number of errors where the reps have played different numbers of matches? You really should enter zero where a Rep has played but made no mistakes. Then what about the length of time they spent on the field?
To get a fair average you would need to divide the number of errors by the time they spent on the field.
This is getting out of hand isn't it? Hope I have helped a little at least.
You are on a steep part of the learning curve. You probably need to think a lot more about what you are really trying to determine. It is dreadfully difficult to help remotely at this point without really knowing what a lot more about the context of the data.
I have worked in Access for years and every day I still learn new stuff.
1 to 3 of 3
