First you can't show the StartDate. If you do you will get one line for
each date.
You can show the first and last startdate by using min and max on separate
occurences.
SELECT Employee_Table.FirstName, Employee_Table.LastName,
Sum(Occurrence_Table.OccurrenceType) AS SumOfOccurrenceType
FROM Occurrence_Type_Table INNER JOIN (Employee_Table INNER JOIN
Occurrence_Table ON Employee_Table.EmployeeID = Occurrence_Table.EmployeeID)
ON Occurrence_Type_Table.OccurenceType = Occurrence_Table.OccurrenceType
WHERE Occurrence_Table.StartDate >=Date()-365
GROUP BY Employee_Table.FirstName, Employee_Table.LastName,
Employee_Table.FormerEmployee
HAVING Sum(Occurrence_Table.OccurrenceType)>=3.95
ORDER BY Sum(Occurrence_Table.OccurrenceType) DESC;
Aggregate queries (totals queries) can be filtered in two way.
-- Using a where removes records before the totaling is done
-- Using Having removes records after the totaling is done
In design view to use where, select WHERE in the totals line and put the
criteria in. Notice that you can't use WHERE and show the field.
If you use any other option in the totals line then the records are filtered
(HAVING) after the records are totaled.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Huber57 said:
Karl,
Here is my SQL. Now it no results. I have checked the data to ensure
that
there should be results.
SELECT Employee_Table.FirstName, Employee_Table.LastName,
Sum(Occurrence_Table.OccurrenceType) AS SumOfOccurrenceType,
Occurrence_Table.StartDate
FROM Occurrence_Type_Table INNER JOIN (Employee_Table INNER JOIN
Occurrence_Table ON Employee_Table.EmployeeID =
Occurrence_Table.EmployeeID)
ON Occurrence_Type_Table.OccurenceType = Occurrence_Table.OccurrenceType
GROUP BY Employee_Table.FirstName, Employee_Table.LastName,
Employee_Table.FormerEmployee, Occurrence_Table.StartDate
HAVING (((Sum(Occurrence_Table.OccurrenceType))>=3.95) AND
("AND"=Date()-365))
ORDER BY Sum(Occurrence_Table.OccurrenceType) DESC;
Any thoughts?
Thanks much.
KARL DEWEY said:
Try this ----
SELECT [YourTable].Employee, Sum([YourTable].absences) AS SumOfabsences
FROM [YourTable]
WHERE ((([YourTable].[YourDate])>=Date()-365))
GROUP BY [YourTable].Employee;
--
KARL DEWEY
Build a little - Test a little
:
Karl,
thanks for the quick reply. I used that expression and when I run the
query
it filters out all of the data over 365 days old. What it does not do
is
total all of the occurences. They are listed individually.
For example it lists
John Smith 1 11/12/2007
John Smith .5 10/5/2007
Nancy Jones 1 9/1/2007
Nancy Jones 1 5/5/2007
What I want it to look like is:
John Smith 1.5
Nancy Jones 2
Thoughts?
:
=Date()-365
OR
=DataAdd("yyyy",-1,Date())
--
KARL DEWEY
Build a little - Test a little
:
Hello all:
I have a data base that tracks employee absences (worth 1) and
tardiness
(worth .5). I am having trouble writing a query that sums the
'occurrences'
that are less than one year old.
Help!!??
Thanks much in advance.
huber57
