Hi
I must admit that I'm finding the initial concept of Relationships between tables rather confusing at the moment, and so apologies if I make a glaring mistake / fail to grasp an otherwise simple idea.
I have at present 5 different tables, each containing data on agent performance in relation to several measures (the files are exported from external software into Excel). The data contains the date an event / action / measure took place, an indication of which action was completed, in addition to the agent who performed the action. Therefore, there are repeated dates, and agent names are repeated multiple times within the data.
The tables all have the same headings, but data is too big to fit into one sheet and so tables exist on multiple workbooks.
Example data:
"Email" Table (saved within "Email" tab)
Date | CRM Activity | Agent | Task | Call | Email | Text | QA |
01/07/2021 | Email | Agent 1 | | | 1 | | |
01/07/2021 | Email | Agent 2 | | | 1 | | |
01/07/2021 | Email | Agent 3 | | | 1 | | |
01/07/2021 | Email | Agent 2 | | | 1 | | |
"Text" Table (from "Text" tab)
Date | CRM Activity | Agent | Task | Call | Email | Text | QA |
10/01/2022 | Text Post | Agent 1 | | | | 1 | |
10/01/2022 | Text Post | Agent 1 | | | | 1 | |
07/01/2022 | Text Post | Agent 1 | | | | 1 | |
06/01/2022 | Text Post | Agent 1 | | | | 1 | |
20/12/2021 | Text Post | Agent 1 | | | | 1 | |
"QA" Table (from "QA" tab)
Date | CRM Activity | Agent | Task | Call | Email | Text | QA |
25/11/2021 | QA Score | Agent 5 | | | | | 100 |
26/11/2021 | QA Score | Agent 2 | | | | | 10 |
08/12/2021 | QA Score | Agent 2 | | | | | 10 |
30/12/2021 | QA Score | Agent 12 | | | | | 60 |
My goal is to create a summary of all agent performance for all measures, which can be filtered by date, e.g below:
Agent | Sum Task | Sum Call | Sum Email | Sum Text | Average QA |
Agent 1 | 580 | 843 | 387 | 385 | 90 |
Agent 2 | 658 | 375 | 720 | 759 | 70 |
Agent 3 | 326 | 837 | 274 | 579 | 80 |
Agent 4 | 285 | 298 | 749 | 293 | 87 |
Because Agent names and Dates appear multiple times within each table, I'm having trouble creating relationships between the tables in order to produce this. Please could someone give me some advice and suggestion on how I can create something similar to the final output.
Many thanks in advance!
Becki
