x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 440

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
0
c0fee
1 Solution

Commented:
Easiest way would be to create 2 running total fields.
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 )
0

Commented:
That will get the totals but since you used running totals, they can't be used in a graph.

To set this up you need to use a COMMAND as the data source
SQL similar to

SELECT EMPLOYEETABLE.EmpId, EMPLOYEETABLE.Name,
TICKETTABLE.TICKETID , TICKETTABLE.OWNER,  TICKETTABLE.COOWNER
FROM EMPLOYEETABLE INNER JOIN TICKETTABLE
ON  EMPLOYEETABLE.EmpId = TICKETTABLE.OWNER
UNION
SELECT EMPLOYEETABLE.EmpId, EMPLOYEETABLE.Name,
TICKETTABLE.TICKETID , TICKETTABLE.COOWNER,  TICKETTABLE.OWNER
FROM EMPLOYEETABLE INNER JOIN TICKETTABLE
ON  EMPLOYEETABLE.EmpId = TICKETTABLE.COOWNER

If IsNull({TICKETTABLE.COOWNER)}) then
1.0
Else
0.5

You can then use the EmpId and this formula in the graph

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.