Complicated expression in a report for a query

  • Thread starter Thread starter ReportTrouble
  • Start date Start date
R

ReportTrouble

Ok so I have a query that displays the entire 'action' of one person... and I
need a total score for each person.. however it is not just a simple sum
because different actions (fields) are worth more than others.
This is the example of the layout of the query hopefull it turns out right.
There are 4 fields and each actions has it's own record.

NAME|LINK ID|CLICK COUNT|REQUESTE FOR INTO
Tom |A |1
|B |2
Bob |C |1
|A |2 |Email


I am not sure if that will turn out pretty so I will also explain it as well
TOM: Has 2 records; LINK ID (A) - CLICK COUNT (1) and LINK ID (B)- CLICK
COUNT (2)

BOB: Has 2 records; LINK ID (C)- CLICK COUNT (1) and LINK ID (A)-CLICK COUNT
(2) with a REQUEST OF (Email)

SCORE:
Tom's score is 3 because each click count is worth 1 ([1+2]x1)=3
Bob's score is 5 because he requested and email which is worth 5 ([1+2]x1+5)=8

Is this possible? and How do I do it
thanks :)

I can explain further if needed
 
Try this --
SELECT NAME, Sum([CLICK COUNT) + Sum(IIF([REQUESTE FOR INTO] ="Email", 5,
0)) AS Score
FROM YourTable
GROUP BY NAME;
 
Back
Top