 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Sum and criteria
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 >=Date()-365 OR >=DataAdd("yyyy",-1,Date())  SignatureKARL DEWEY Build a little - Test a little > Hello all: > I have a data base that tracks employee absences (worth 1) and tardiness [quoted text clipped - 6 lines] > > huber57 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 [quoted text clipped - 10 lines] > > > > huber57 Try this ---- SELECT [YourTable].Employee, Sum([YourTable].absences) AS SumOfabsences FROM [YourTable] WHERE ((([YourTable].[YourDate])>=Date()-365)) GROUP BY [YourTable].Employee; SignatureKARL DEWEY Build a little - Test a little > Karl, > [quoted text clipped - 28 lines] > > > > > > huber57 Karl, I will try that. Thanks! > Try this ---- > SELECT [YourTable].Employee, Sum([YourTable].absences) AS SumOfabsences [quoted text clipped - 34 lines] > > > > > > > > huber57 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. > Try this ---- > SELECT [YourTable].Employee, Sum([YourTable].absences) AS SumOfabsences [quoted text clipped - 34 lines] > > > > > > > > huber57 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. SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Karl, > [quoted text clipped - 61 lines] >> > > > >> > > > huber57 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. > First you can't show the StartDate. If you do you will get one line for > each date. [quoted text clipped - 89 lines] > >> > > > > >> > > > huber57 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.