Sum and criteria

G

Guest

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
 
G

Guest

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?
 
G

Guest

Try this ----
SELECT [YourTable].Employee, Sum([YourTable].absences) AS SumOfabsences
FROM [YourTable]
WHERE ((([YourTable].[YourDate])>=Date()-365))
GROUP BY [YourTable].Employee;
 
G

Guest

Karl,

I will try that. Thanks!



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


Huber57 said:
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?
Click to expand...
Click to expand...
 
G

Guest

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


Huber57 said:
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?
Click to expand...
Click to expand...
 
J

John Spencer

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


Huber57 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,

YOU ARE THE MAN! Thanks much for the solution and the explanation.

It worked perfectly. I even got sexy and made it greater than 3.95 and less
than 6.

I appreciate the help.

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Interesting HR Database - Queries 15
Delete Query Not Working 2
querys: Getting two collumns of summed totals based off a third 2
total points then print those that are over a threshold 4
Need to select record based on date 5
inverting criteria for query 6
Not like query criteria 4
Crosstab Query Issue 1

Top