Last 2 records Query

[Delindan]

I have a query that I'm trying to run that will select the last 2 records in a job activity table so that I can use the information to compare start and end dates. I did find a thread that I believed would help me

http://www.tek-tips.com/viewthread.cfm?qid=396191

So using their syntax with my table/variables came up with the following:

SELECT [Position Number], [Act Start Date], [End Date]
FROM [Job Activity] AS d
WHERE [Position Number] In
(SELECT TOP 2 [Position Number]
FROM [Job Activity]
WHERE [Position Number] = d.[Position Number]
ORDER BY [Position Number] DESC);

I have test information in the table but I do have 1 position number that is listed 3 times and all 3 of those records are listing in the query when I want only 2 of them to list. Does anyone have any suggestions? Thanks!

 

[PHV]

select the last 2 records in a job activity table
What is the sort criteria to define last ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

I didn't sort it...Is that the problem?

 

[PHV]

So, please define last

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

For each [position number] the last two records added.

 

[dhookom]

You still haven't defined "last". If you were to look at the records randomly, what field or fields would identify "last". These would need to be sorted in ascending or descending order.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Just a guess:

SELECT [Position Number], [Act Start Date], [End Date]
FROM [Job Activity] AS d
WHERE [Act Start Date] In
(SELECT TOP 2 [Act Start Date]
FROM [Job Activity]
WHERE [Position Number] = d.[Position Number]
ORDER BY [Act Start Date] DESC)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

Sorry didn't define enough....the last two records would be defined as last two [Input Date] which is an auto gened date when a record gets entered. Using the syntax used in your example I came up with the following...(and I actually added [Input Date] so I could see what it was listing:

SELECT [Position Number], [Act Start Date], [End Date], [Input Date]
FROM [Job Activity] AS d
WHERE [Input Date] In
(SELECT TOP 2 [Input Date]
FROM [Job Activity]
WHERE [Position Number] = d.[Position Number]
ORDER BY [Input Date] DESC)

I thought it wasn't working...then I realized that two of my job activity records for one position had the same [Input Date]. Once I changed that first date to something else it worked great! I believe the real data (I've been using the test data I input) will not have multiple records with the same input date. Thanks so much for your help!

 

[strongm]

Surely you just need:

SELECT TOP 2 [Position Number], [Act Start Date], [End Date], [Input Date]
FROM [Job Activity]
ORDER BY [Input Date] Desc

 

[Delindan]

this only gives the top 2 out of all of them. I needed the top 2 records for each position number. Thanks for the suggestion.

 

[Delindan]

After thinking even more about this I realize that even though I got this query to work, it is not giving me the results I would like. Let me explain the ultimate results I'm trying to get to. For a given fiscal year starting 9/1/2010, how many open positions do we have. I have a Job Activity table which has the fields Position number, forecasted start date, actual start date and end date. Any one Position number can have multiple records in this table. For a given month DateAdd("m",1,txtstart) I would like to compare the end date and actual start dates for all records of each position number to see if there are any date gaps or open positions. I have no idea how to do that. Initially I thought I could just compare the last two records for each position when I realized that a position could have 3 people move through it in a year (unlikely but it could happen). Is this impossible?

 

[dhookom]

Delindan,
It seems you have trouble describing your requirements. It is often helpfull to provide enough fields and records so that you can adequately identify the significant data and desired output. You don't need to include extra fields if they have no meaning in the solution.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

There so many ways of looking at this...I apologize for dragging you through my thought process. After a period of thinking about this more I decided that the number of open positions is simply the difference between the budgeted positions and the filled positions. I believe this should be an easier problem to tackle. I still need help with the syntax. However, I have a more clear idea of what I want.

I have a table with [position number], [budgeted cc], [budgeted start] and would like to count budgeted number of positions for a fiscal year starting [txtStart] which is a user input.

Position Number Budgeted CC Budgeted Start
1001 SCX76004 14-Jul-08
1002 SCH76006 01-May-11
1003 SIX76000 02-Nov-10
1005 SCX76000 02-Feb-12

For [txtStart] = 9/1/2010 the expected outcome would be

Month 1: 1
Month 2: 1
Month 3: 2
Month 4: 2
Month 5: 2
Month 6: 2
Month 7: 2
Month 8: 2
Month 9: 3
Month 10: 3
Month 11: 3
Month 12: 3

I would like to further break it down by [budgeted cc] if that is possible.

SELECT [Position Number],[budgeted CC]
COUNT([Budgeted Start]) WHERE [Budgeted Start] < DateAdd["m",1,[txtStart]) AS Month1
COUNT([Budgeted Start]) WHERE [Budgeted Start] < DateAdd["m",2,[txtStart]) AS Month2
FROM [Head Count]
GROUP BY [Budgeted CC];

I realize the syntax is wrong...can't get it to run. However maybe it gives you an idea of what I'd like to do. Also, I just started with the first 2 months. Would do them all when I get the correct syntax. Thanks for your help!