Retrieving Consecutive records

R

Richard

I have a table that has production information by an employee number. I then
sum[Amount] that production by month to determine if it meets a minimum
amount[Qualify]. So my resulting query looks like this:
Emp Month Year Amount Qualify
123 9 2004 1700 Yes
123 10 2004 1010 Yes
123 11 2004 400 No
123 12 2004 2000 Yes
123 1 2005 2500 Yes
123 2 2005 3000 Yes
777 9 2004 2100 Yes
777 10 2004 1990 Yes
777 11 2004 1400 Yes
777 12 2004 300 No
777 1 2005 3500 Yes
777 2 2005 2000 Yes

I then need a query that will show me those employees that have previous
consecutive months that meet the minimum amounts based on the month I run the
query. So the results would look like this, if I run it in February 2005:
123 12 2004 2000 Yes
123 1 2005 2500 Yes
123 2 2005 3000 Yes
777 1 2005 3500 Yes
777 2 2005 2000 Yes

I have tried using DLookup without any success, does anyone have a suggestion?
 
D

delali

You can write another query using the first query as the table and make the
month a criteria.

select * from [first query] where [first query].[field name] = "yes" and
[first query].[field name] < [month to start from];
 
R

Richard

Delali,

I see how that will return all the "yes" entries but I only the "yes"
entries that are consecutive. So if it hit a "no" then it needs to stop
retrieving for that employee and move on to the next employee. Further more
I'm working from the current month so if January had "yes" and Dec. had "no"
and Nov. and Oct. were "yes"(all for the same employee). I want the query to
stop at Jan. because Dec. was "no" and broke the consecutive months.

delali said:
You can write another query using the first query as the table and make the
month a criteria.

select * from [first query] where [first query].[field name] = "yes" and
[first query].[field name] < [month to start from];



Richard said:
I have a table that has production information by an employee number. I then
sum[Amount] that production by month to determine if it meets a minimum
amount[Qualify]. So my resulting query looks like this:
Emp Month Year Amount Qualify
123 9 2004 1700 Yes
123 10 2004 1010 Yes
123 11 2004 400 No
123 12 2004 2000 Yes
123 1 2005 2500 Yes
123 2 2005 3000 Yes
777 9 2004 2100 Yes
777 10 2004 1990 Yes
777 11 2004 1400 Yes
777 12 2004 300 No
777 1 2005 3500 Yes
777 2 2005 2000 Yes

I then need a query that will show me those employees that have previous
consecutive months that meet the minimum amounts based on the month I run the
query. So the results would look like this, if I run it in February 2005:
123 12 2004 2000 Yes
123 1 2005 2500 Yes
123 2 2005 3000 Yes
777 1 2005 3500 Yes
777 2 2005 2000 Yes

I have tried using DLookup without any success, does anyone have a suggestion?
 

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

Subtotals 4
Group by Non-blank Distinct Values 5
Query Help 9
Date Expressions & Calculations 3
Setting values over multiple records in one Query using Loop statement 1
Grouping query help 3
ranking 0
DMin, Dmax 3

Top