c0fee
asked on
Assign running totals based on two fields in a table
I have a two tables Employees and Tickets. An employee can either be the primary owner of a ticket, be a co-owner of ticket, or be both (owns the ticket entirely as both owner and co-owner or have a null co-owner). If the employee owns the ticket in its entirety, then they get a full ticket count. If they are either the primary or co-owner, then they are to only receive 1/2 credit.
How should I join my tables and graph the total ticket count for each employee?
EMPLOYEE TABLE
EMPID NAME
1111 SallyJ
2222 JohnG
3333 DebraT
TICKET TABLE
TICKETID OWNER CO-OWNER
1 1111 null
2 1111 2222
3 1111 1111
4 2222 2222
5 2222 1111
6 2222 3333
7 3333 3333
With the data above, this should be the result:
SallyJ (1111) - 3
JohnG (2222) - 2.5
DebraT(3333) - 1.5
How should I join my tables and graph the total ticket count for each employee?
EMPLOYEE TABLE
EMPID NAME
1111 SallyJ
2222 JohnG
3333 DebraT
TICKET TABLE
TICKETID OWNER CO-OWNER
1 1111 null
2 1111 2222
3 1111 1111
4 2222 2222
5 2222 1111
6 2222 3333
7 3333 3333
With the data above, this should be the result:
SallyJ (1111) - 3
JohnG (2222) - 2.5
DebraT(3333) - 1.5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1) Calculate all full tickets (name: all_full_tickets)
Field to summarize: TICKETID
Type of summary: Distinct Count
Evaluate -> Use a formula -> isnull(CO-OWNER) or (OWNER = CO-OWNER)
Reset -. On Change of Group - > {TicketTable.Owner}
2) Calculate all half tickets (name: all_half_tickets)
Field to summarize: TICKETID
Type of summary: Distinct Count
Evaluate -> Use a formula -> (not isnull(CO-OWNER)) and (OWNER != CO-OWNER)
Reset -. On Change of Group - > {TicketTable.Owner}
3) Create a formula field "TotalTickets" with the formula all_full_tickets + (all_half_tickets * 0.5 )