Date Query

[TheJudge]

Can someone please help?

My access database holds the last 6 months stock market data i.e. close, high, low price for each stock. However, I want to show the results in Excel but only the last three values for each stock (as I have over 1000 stocks) so want to create a query in Access.

For example:

qDate qTicker qOp qHi qLo qCl qVo
08/11/2006 AAT.L 98 100 98 99 2361001
09/11/2006 AAT.L 98 98 98 98 378001
10/11/2006 AAT.L 99 99 97 97 66001

08/11/2006 VOD.L 198 100 198 199 2361020
09/11/2006 VOD.L 198 198 198 198 378020
10/11/2006 VOD.L 199 199 197 197 66010

08/11/2006 ARM.L 298 200 298 299 1236100
09/11/2006 ARM.L 298 298 298 298 317800
10/11/2006 ARM.L 299 299 297 297 66100

Any thoughts?

Thanks!

 

[pbaldy]

This may work for you:

http://support.microsoft.com/kb/210039/en-us

 

[TheJudge]

Thanks for the reply but having problems getting this to work - do you have an example?

 

[pbaldy]

Actually I just used that technique on a production database, but the query is in SQL Server. Perhaps if you posted your db, someone could fix it for you.

 

[TheJudge]

The forum won't let me upload the file - but the field names are above and this is my SQL:

In (Select Top 3 [qTicker] From Quotes Where
[qTicker]=[qDate].[qTicker] Order By [qTicker] Desc)

 

[TheJudge]

picture attached.

 

[TheJudge]

Can anyone please help?

 

[Jon K]

Try this query.

SELECT Quotes.*
FROM Quotes
WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker order by qDate Desc)


Note: Running a subquery in a correlated way is inefficient and will take time if the table is large.
.

 

[TheJudge]

Jon K - thanks very much that worked!

 

[TheJudge]

Hi,

Using this query how do I display records with 'only' today's date?

SELECT Quotes.*
FROM Quotes
WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker order by qDate Desc)

 

[Newbie100]

SELECT Quotes.*
FROM Quotes
WHERE qDate = Date()

 

[TheJudge]

Thanks but I need to include this "WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker order by qDate Desc)" in the SQL query but show only today's records.

Any thoughts?

 

[Rabbie]

Thanks but I need to include this "WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker order by qDate Desc)" in the SQL query but show only today's records.

Any thoughts?

Try

WHERE qDate In (Select Top 3 qDate from Quotes as S where S.qTicker=Quotes.qTicker and qDate = date() )

 

[TheJudge]

This didn't work.

 

[TheJudge]

Can anyone help please?

 

[GUIDO22]

Is qDate a string date or 'Date' data type?

You need to ensure that the dates stored in your table are date strings only
ie. no timepstamp. Then the suggested code may work..... can you confirm no timestamp?