On the fly calc, two tables

[firefly2k8]

I have the following query which nicely calculates the standard deviation from my table TSTable where the dates are within a given range and the currency is as defined:

SELECT STDEV(vol)
FROM [SELECT (High+Low)/2 AS vol
FROM TSTable
WHERE (Date_Time Between #6/2/2011# And #6/3/2011#) And Pair="GBPUSD"
]. AS [%$##@_Alias];

I originally wrote:

SELECT STDEV(vol)
FROM (SELECT (High+Low)/2 AS vol
FROM TSTable
WHERE (Date_Time Between #6/2/2011# And #6/3/2011#) And Pair="GBPUSD");

and Access added the alias bit. Not sure why.

Now I have another table, PTable which has amongst other fields the following:
TradeDate, CurrencyPair, ProjectID, vol_result

For each record in PTable I want to run my above query using the TradeDate and TradeDate+1 for the Date_Time between and the CurrencyPair for the Pair. I would like to put output of the query in vol_result.

I am thinking along the lines of UPDATE, INNER JOIN. But basically, how would I do this?

Thank you in advance for any help.

 

[Privateer]

firefly2k8
Your query has a sub query in it, which is why Access added the alias and I can't see why you would need it. Try using just this code:

SELECT STDEV((High+Low)/2) AS StandardDeviation, DateValue([Date_Time]) as TheDate
FROM TSTable
WHERE ((DateValue([Date_Time])=#12/2/2011#) And (Pair="GBPUSD));"

Also, I want to warn you about the date criteria. Because you call your field "Date_Time" I suspect your field has time added to the transaction dates and you won't get the results you are looking for with your current criteria. A date of 6/1/2001 12:15:00 will appear with your criteria and you won't get any 6/3 records. For 6/2 results I prefer > 6/1 and < 6/3. To be clear, if your field is just dates, then your between statement will work. The code I gave you is common practice with me, whenever I have a transaction field with date and time I always create just a date field (TheDate) for use later on.
Putting these results in with a second table should be easy but you are not giving me the common field to each. There should be a field in TSTable that is the same in PTTable so you can draw a relationship between them. Normally, I would guess the date and a ticker symbol. If that is true, you need to remove the time from the transaction date in TSTable so you can relate it to the trade date if PTTable. Try TheDate: DateValue(TSTable.Date_Time). Hope this helps.
Privateer

 

[firefly2k8]

Hi Pivateer, thanks for your reply.

I want to do the join on 'currency pair' and date. Made this effort which was rejected by access:

UPDATE [SE2 Project Table] 
INNER JOIN TimeStampData ON ([SE2 Project Table].[Trade Date]=DateValue(TimeStampData.Date_Time)) AND ([SE2 Project Table].[Currency Pair]=TimeStampData.Pair) 
SET [SE2 Project Table].Number_Field_D = (SELECT stdev((High+Low)/2) AS StandardDeviation FROM TimeStampData WHERE (DateValue(TimeStampData.Date_Time)= [SE2 Project Table].[Trade Date]) And ( [SE2 Project Table].[Currency Pair]=TimeStampData.Pair))
WHERE [Project ID]=233;