1. Create a subquery to get the oldest date value for the employee.
2. Use the subquery in the WHERE clause to eliminate those records that have
that date and the negative point.
The following example assumes a table named tblPoint, with fields:
- EmpID the employee
- PointDate the date field
- PointValue the point value field.
SELECT tblPoint.*
FROM tblPoint
WHERE tblPoint.PointDate <>
(SELECT Min(PointDate) AS MinDate
FROM tblPoint AS Dupe
WHERE Dupe.EmpID = tblPoint.EmpID)
OR tblPoint.Point = 0;
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<mu*************@gmail.comwrote in message
news:42**********************************@s9g2000p rg.googlegroups.com...
Hi all,
I have a table that contains employee IDs, dates and point values (-1,
0 or 1). I want to query the table and exclude the oldest record (by
date) if it has a negative point value. If two records or more records
share the oldest date, I want to exclude all of those that are
negative.
Here is how I'm doing it now...
1) Find the oldest date in the table for each employee
2) Look up all records on that oldest date for that employee
3) Subtract any records that have a negative point value
This currently takes three sequential queries to perform. Can anyone
suggest a more elegant solution? Thanks,
Alex
