Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sorry Chris. I'm kind a little green with access. Let me try to re-explain. Once again thanks very much for your help!!!!

I am trying to develop a query that will take the close price minus the close price of the previous day and run a calculation. I could (and have tried) to do it up by simply using [date]-1. The problem is that markets are not open every day so I need to have it access the previous trading day listed in the database (Which will not necessarily be one calendar day back).

The Date currently looks like this in the table.

Co Date Open High Low Close Volume
HP.to 12/31/2003 3.5 3.52 3.36 3.45 112800
HP.to 12/30/2003 3.5 3.59 3.48 3.48 112000
HP.to 12/29/2003 3.33 3.59 3.26 3.5 199600
HP.to 12/24/2003 3.5 3.57 3.32 3.34 168300
HP.to 12/23/2003 3.42 3.49 3.37 3.49 227700
HP.to 12/22/2003 3.33 3.4 3.26 3.37 122100
HP.to 12/19/2003 3.2 3.35 3.2 3.3 388200
HP.to 12/18/2003 3.04 3.25 3 3.14 227800

All I really want this query to do is take the previous days close and place it in a new column. The way that I would like the data to look like after the query is complete is like the following:

Co Date Open High Low Close Volume PreviousDayClose
HP.to 12/31/2003 3.5 3.52 3.36 3.45 112800 3.48
HP.to 12/30/2003 3.5 3.59 3.48 3.48 112000 3.5
HP.to 12/29/2003 3.33 3.59 3.26 3.5 199600 3.34
HP.to 12/24/2003 3.5 3.57 3.32 3.34 168300 3.49
HP.to 12/23/2003 3.42 3.49 3.37 3.49 227700 3.37
HP.to 12/22/2003 3.33 3.4 3.26 3.37 122100 3.3
HP.to 12/19/2003 3.2 3.35 3.2 3.3 388200 3.14
HP.to 12/18/2003 3.04 3.25 3 3.14 227800
 
Try:
Select *,
(Select TOP 1 Close
From tblA A
Where A.Co = tblA.Co
AND A.Date<tblA.Date
Order By Date) as PrevClose
From tblA;


--
Duane Hookom
MS Access MVP


sean said:
I'm sorry Chris. I'm kind a little green with access. Let me try to
Click to expand...
re-explain. Once again thanks very much for your help!!!!
I am trying to develop a query that will take the close price minus the
Click to expand...
close price of the previous day and run a calculation. I could (and have
tried) to do it up by simply using [date]-1. The problem is that markets are
not open every day so I need to have it access the previous trading day
listed in the database (Which will not necessarily be one calendar day
back).
The Date currently looks like this in the table.

Co Date Open High Low Close Volume
HP.to 12/31/2003 3.5 3.52 3.36 3.45 112800
HP.to 12/30/2003 3.5 3.59 3.48 3.48 112000
HP.to 12/29/2003 3.33 3.59 3.26 3.5 199600
HP.to 12/24/2003 3.5 3.57 3.32 3.34 168300
HP.to 12/23/2003 3.42 3.49 3.37 3.49 227700
HP.to 12/22/2003 3.33 3.4 3.26 3.37 122100
HP.to 12/19/2003 3.2 3.35 3.2 3.3 388200
HP.to 12/18/2003 3.04 3.25 3 3.14 227800

All I really want this query to do is take the previous days close and
Click to expand...
place it in a new column. The way that I would like the data to look like
after the query is complete is like the following:
 
Back
Top