Tony:
How you approach it depends on whether you want just the totals, or the
details and the totals together.
1. If you want the sum of 'points' for each distinct value of another
column or set of columns you need to GROUP BY that column or columns, e.g.
SELECT Issue,
SUM(IIf([status]="warranty",5,
IIf([status]="Health & Safety",10,
IIf([status]="Customer Complaint",3,
IIf([status]="Internal Issue",1)))))
AS TotalPoints
FROM Issues
WHERE IssueDate BETWEEN #2009-01-01# AND #2009-31-01#
GROUP BY Issue;
This would give you the total number of points per Issue for last month. If
you want the total points for all issues for the month you don't have to
GROUP BY anything:
SELECT SUM(IIf([status]="warranty",5,
IIf([status]="Health & Safety",10,
IIf([status]="Customer Complaint",3,
IIf([status]="Internal Issue",1)))))
AS TotalPoints
FROM Issues
WHERE IssueDate BETWEEN #2009-01-01# AND #2009-31-01#;
If on the other hand you wanted to see each individual issue value and the
totals as well then you'd need to use a subquery to compute the totals:
SELECT Issue, IssueDate,
(SELECT SUM(IIf([status]="warranty",5,
IIf([status]="Health & Safety",10,
IIf([status]="Customer Complaint",3,
IIf([status]="Internal Issue",1)))))
FROM Issues as I2
WHERE I2.Issue = I1.Issue
AND IssueDate BETWEEN #2009-01-01# AND #2009-31-01#)
AS TotalPoints
FROM Issues AS I1
WHERE IssueDate BETWEEN #2009-01-01# AND #2009-31-01#;
Note how the two instances of the Issues table are differentiated by the
aliases I1 and I2. While you can return both the detail and the aggregated
values in this way in a query, on the whole its easier and better to do it in
a report, where you can simply Sum the points in a group footer.
I'd strongly recommend you also follow Tom's recommendation about including
the points per status value in a Statuses table:
Issue Points
Warranty 5
Customer Complaint 3
Internal Issue 1
Hard coding the points per status value in an expression is not good
practice as in a relational database data should be stored as values at
column positions in rows in tables, and in no other way (its called the
'information principle'). It also makes querying a lot simpler, e.g. with
the last query above:
SELECT Issue, IssueDate
(SELECT (SUM(Points)
FROM Statuses INNER JOIN Issues AS I2
ON Statuses.Status = I2.Status
WHERE I2.Issue = I1.Issue
AND IssueDate BETWEEN #2009-01-01# AND #2009-31-01#)
AS TotalPoints
FROM Issues As I1
WHERE IssueDate BETWEEN #2009-01-01# AND #2009-31-01#;
or with the first:
SELECT Issue,
SUM(Points) AS TotalPoints
FROM Issues INNER JOIN Statuses
ON Statuses.Status = Issues.Status
WHERE IssueDate BETWEEN #2009-01-01# AND #2009-31-01#
GROUP BY Issue;
Ken Sheridan
Stafford, England
blake7 said:
thanks Tom for your help, I did as you said, but it did not add all the
numbers up, it just sorted the status column alphabetically, it did do
something with the numbers but it did not sum the total of the numbers.
Thanks.
Tom van Stiphout said:
On Sat, 31 Jan 2009 10:58:01 -0800, blake7
In query design view, hit the Sigma button and select Sum for the
Points column.
Personally I would rather add a Points column to the Status table.
-Tom.
Microsoft Access MVP
Hi all,
I have the following code in a query, which is showing the results i want it
to.
points: IIf([status]="warranty",5,IIf([status]="Health &
Safety",10,IIf([status]="Customer Complaint",3,IIf([status]="Internal
Issue",1))))
Warranty 5
Warranty 5
Health & Safety 10
Customer Complaint 3
Internal Issue 1
Internal Issue 1
Warranty 5
My question - is it possible to sum total the numbers in the "points" column?
Thanks in advance
Regards Tony.
