Normalizing tables

[isaacski]

Hi All,
Sorry for the vague title but I have a vague question. I have been tasked to build a score tracking database. I have read through numerous normalization articles, helps/how tos, etc. Since I am new to this I am having a problem. I think I have my tables organized how I need them (in 3NF) but I can't seem to build the report with the information I need. I have posted my db as I don't really know how to adequately explain what I'm talking about.

I have the queries setup with the information I need for this summary report. As you can see from the db, I need the report to show each analyst's assignments by group and to see a summary of the scores logged for each advocate. I have intentions of building a button on the report to open to the form to track and update the scores in each category. The major part of this report is to see how many evaluations is expected for each advocate and how many has been completed. You can see the number expected is already there however it is inaccurate because the number doesn't include the "relieves" (Relieves are the number that we don't have to do for each analyst due to vacations, time off, project assignements, etc). This can vary from month to month. So in summary on this report we need to see,

Total Number Expected - #Expected-#Relieved
Number Completed - Sum(Score)
Problem is that some of these numbers aren't in qryassignment, they come from the queries in the subreports.

So, I tried referencing the necessary controls on the subreports and it ended up kind of working however when scrolling through the report, the referenced controls would change from the correct number to a #error or #name, which isn't cool.

Next, I tried incorporating the necessary summary data (score count total and Sum of relieves) in the original qryassignment. This didn't work as records would be repeated over and over again for the Manager group because they have both cases and calls. This would be the ideal scenario if I can figure out how to query this properly.

I'm not sure if this is a table structure / data normalization issue or a query issue.

Any guidance or suggestions you could make on how to get this to work as desired would be greatly appreciated!!!

Thanks,
Kim

 

[way2bord]

Hi All,
Sorry for the vague title but I have a vague question. I have been tasked to build a score tracking database. I have read through numerous normalization articles, helps/how tos, etc. Since I am new to this I am having a problem. I think I have my tables organized how I need them (in 3NF) but I can't seem to build the report with the information I need. I have posted my db as I don't really know how to adequately explain what I'm talking about.

I have the queries setup with the information I need for this summary report. As you can see from the db, I need the report to show each analyst's assignments by group and to see a summary of the scores logged for each advocate. I have intentions of building a button on the report to open to the form to track and update the scores in each category. The major part of this report is to see how many evaluations is expected for each advocate and how many has been completed. You can see the number expected is already there however it is inaccurate because the number doesn't include the "relieves" (Relieves are the number that we don't have to do for each analyst due to vacations, time off, project assignements, etc). This can vary from month to month. So in summary on this report we need to see,

Total Number Expected - #Expected-#Relieved
Number Completed - Sum(Score)
Problem is that some of these numbers aren't in qryassignment, they come from the queries in the subreports.

So, I tried referencing the necessary controls on the subreports and it ended up kind of working however when scrolling through the report, the referenced controls would change from the correct number to a #error or #name, which isn't cool.

Next, I tried incorporating the necessary summary data (score count total and Sum of relieves) in the original qryassignment. This didn't work as records would be repeated over and over again for the Manager group because they have both cases and calls. This would be the ideal scenario if I can figure out how to query this properly.

I'm not sure if this is a table structure / data normalization issue or a query issue.

Any guidance or suggestions you could make on how to get this to work as desired would be greatly appreciated!!!

Thanks,
Kim

TLDR; I looked at your db and can't relate it to what you're asking.

Your table relationships are a bit off:
- tblScores should be linked to refAdvocate via AdvocateID.
- refAdvocate AdvocateRoleName should be RoleID - and linked appropriately to the PrimaryKey (PK) from table refRole

I suggest you modify everything in your queries until you get the numbers you want, rather than fiddling with it in the reports.

 

[Rabbie]

I suggest you modify everything in your queries until you get the numbers you want, rather than fiddling with it in the reports.

Excellent advice!. Your reports should be based on queries which have the information you want to show.