By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,601 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,601 IT Pros & Developers. It's quick & easy.

Query question

P: n/a
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
Sep 4 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Sep 4 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.