Retrieve the second most recent date data

[Guest]

Hi..I am a newbie....

I am trying to retrieve the second most recent date data from a query....Can
someone please help me.....

Here is the SQL for the most recent date info.....

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus
GROUP BY Focus.CompanyName;

Thank you,

Jay

 

[fredg]

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus
GROUP BY Focus.CompanyName;

Does this work for you?

SELECT Top 2 Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd
Date]
FROM Focus
GROUP BY Focus.CompanyName
ORDER BY Max(Focus.[End Date]) DESC;

 

[Guest]

It still gives me the Max Date Data......

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus
GROUP BY Focus.CompanyName;

Does this work for you?

SELECT Top 2 Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd
Date]
FROM Focus
GROUP BY Focus.CompanyName
ORDER BY Max(Focus.[End Date]) DESC;

 

[John Spencer]

You can call the first query (save it) in a second query.

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus INNER JOIN YourFirstQuery
ON Focus.CompanyName = YourFirstQuery.CompanyName
WHERE Focus.EndDate < YourFirstQuery.[MaxOfEnd Date]
GROUP BY Focus.CompanyName;


You could do that all in one query EXCEPT that your field name End Date has
a space in it. Spaces in field and table names cause problems and it is
best to name fields with just letters, numbers, and underscore characters.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

It still gives me the Max Date Data......

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus
GROUP BY Focus.CompanyName;

Does this work for you?

SELECT Top 2 Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd
Date]
FROM Focus
GROUP BY Focus.CompanyName
ORDER BY Max(Focus.[End Date]) DESC;

I thought you wished to see both the Max and the next to Max records.

Try this to see just the second to the Max record.

Note: if there is a tie for second to the Max, you will see all the
second to Max records.

SELECT TOP 1 Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd
Date]
FROM Focus
GROUP BY Focus.CompanyName
HAVING (((Max(Focus.[End Date]))<(SELECT Max(Focus.[End Date]) AS
[MaxOfEnd Date]
FROM Focus)))
ORDER BY Max(Focus.[End Date]) DESC;

 

[Guest]

Thank you so much guys...it worked......

So now i have two separate query results where one represent most recent
date data and second one represent 2nd most recent date data.

My main goal here is to do a dropdown box in a form where if i pick any of
the companyName it will pull the info from two separate query on two column
next to each other...Is that someone you guys can help me with.

Thank you,

Jay

You can call the first query (save it) in a second query.

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus INNER JOIN YourFirstQuery
ON Focus.CompanyName = YourFirstQuery.CompanyName
WHERE Focus.EndDate < YourFirstQuery.[MaxOfEnd Date]
GROUP BY Focus.CompanyName;


You could do that all in one query EXCEPT that your field name End Date has
a space in it. Spaces in field and table names cause problems and it is
best to name fields with just letters, numbers, and underscore characters.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi..I am a newbie....

I am trying to retrieve the second most recent date data from a
query....Can
someone please help me.....

Here is the SQL for the most recent date info.....

SELECT Focus.CompanyName, Max(Focus.[End Date]) AS [MaxOfEnd Date]
FROM Focus
GROUP BY Focus.CompanyName;

Thank you,

Jay

 

[John Spencer]

Are you saying that you want the combobox to display
CompanyName, MaxEndDate, MaxEndDateLessOne
all in one row of the combobox?

You can modify the query to the following which should

SELECT Focus.CompanyName
, YourFirstQuery.[MaxOfEnd Date] as LastDate
, Max(Focus.[End Date]) AS PenultiimateDate
FROM Focus INNER JOIN YourFirstQuery
ON Focus.CompanyName = YourFirstQuery.CompanyName
WHERE Focus.EndDate < YourFirstQuery.[MaxOfEnd Date]
GROUP BY Focus.CompanyName, YourFirstQuery.[MaxOfEnd Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John,
I have Quarterly filings data for each company. For Example, CompanyA filed
their quarterly earnings for 09/30/2007 as the most recent data and
06/30/2007 as the second most recent data. I want to do a combo box so that
when i do a dropdown combo box and select companyA then it will show me data
for 09/30/2007 in the first column and 06/30/07 data in the second column.

CompanyName
CompnayA 09/30/07 06/30/07
Net capital Net capital
Revenue Revenue

I did not add the Net capital and Revenue data in the query to keep the
query simple to show you guys.

I can't figure out how to link the query result to the combobox....something
like AfterUpdate thingy....

Thank you in advance for your help.

Jay

Are you saying that you want the combobox to display
CompanyName, MaxEndDate, MaxEndDateLessOne
all in one row of the combobox?

You can modify the query to the following which should

SELECT Focus.CompanyName
, YourFirstQuery.[MaxOfEnd Date] as LastDate
, Max(Focus.[End Date]) AS PenultiimateDate
FROM Focus INNER JOIN YourFirstQuery
ON Focus.CompanyName = YourFirstQuery.CompanyName
WHERE Focus.EndDate < YourFirstQuery.[MaxOfEnd Date]
GROUP BY Focus.CompanyName, YourFirstQuery.[MaxOfEnd Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you so much guys...it worked......

So now i have two separate query results where one represent most recent
date data and second one represent 2nd most recent date data.

My main goal here is to do a dropdown box in a form where if i pick any of
the companyName it will pull the info from two separate query on two
column
next to each other...Is that someone you guys can help me with.

Thank you,

Jay