Difference from Long Term Average

[mrr2]

Hello, I am trying to create a query that will take a series of numbers in a field, and then create a new field that subtracts the number in the record from the average of the whole field (series of numbers). Is this possible at all?

Please let me know if this is not clear, and I will do my best to clarify.



Thanks for any/all help!

[JeroenMioch]

What doe you mean by series of numbers in a field ?

That gives me the idea that you have one field that yields such numbers : 1234 5678 0987 etc etc
If so you have to seperate them, and put them in different fields. After that do some math.

But my guess is that your not explaining yourself good enough

[mrr2]

SecureZIP Attachments.zip

Sorry JeroenMioch,

I have a time series in a field (yields on a bond index on a monthly basis).

I would like to create a new field right next to it showing the difference of each number in the time series from the average of the field (yields on bond index).

Does that help?

I have attached the spreadsheet to be clearer - thanks so much.

[recyan]

Till Jeroen's back, check out if below gives some guidelines :

SELECT 
	myTable.TheDate, 
	myTable.CreditSpread, 
	qryAverageValueA.AvgOfCreditSpread, 
	[CreditSpread]-[AvgOfCreditSpread] AS RelativeHYRiskPremiumtoLongRunAverage
FROM 
	myTable, 
	(
	SELECT 
		Avg(myTable.CreditSpread) AS AvgOfCreditSpread
	FROM 
		myTable
	)
	AS qryAverageValueA;

Thanks

[mrr2]

recyan, thanks for that. I will check it once I get into work on Monday and report back to you on it (as a Contractor they don't let me have access to access () offline.
Thanks so much recyan

[mrr2]

recyan, I set it up like this (in accordance with the names of my tables and fields) based on your template, and It looks like it works! Thanks so much!

SELECT 
 tbl_H0A0_monthly.period, 
 tbl_H0A0_monthly.px_last, 
 qryAverageValueA.AvgOfpx_last, 
 [px_last]-[AvgOfpx_last] AS RelativeHYRiskPremiumtoLongRunAverage
FROM 
 tbl_H0A0_monthly, 
 ( SELECT 
  Avg(tbl_H0A0_monthly.px_last) AS AvgOfpx_last
 FROM 
 tbl_H0A0_monthly )
 AS qryAverageValueA;

[recyan]

Glad you found it helpful.

Thanks

[mrr2]

Hi recyan, I dont mean to be a pest but maybe you can help me figure out how to throw another ball into the mix, i.e. yields

What I'd like to do is to take the difference between HY and Yields, which I have, then take the average of that, and subtract the diference in HY and Yields (spreads) from that average in a new field

Here is the query that I have in the works, please let me know your thoughts if you can, thanks!

SELECT tbl_H0A0_monthly.period, tbl_H0A0_monthly.px_last AS HY, tbl_10YR_monthly.px_last as Yields, [HY]-[yields] AS Spreads, [HY]-[AvgOfHY] AS RelativeHYRiskPremiumtoLongRunAverage, qryAverageValueA.AvgOfHY
FROM (SELECT 
  Avg(tbl_H0A0_monthly.px_last) AS AvgOfHY
 FROM 
 tbl_H0A0_monthly )  AS qryAverageValueA, tbl_H0A0_monthly INNER JOIN tbl_10YR_monthly ON tbl_H0A0_monthly.Period = tbl_10YR_monthly.Period

[recyan]

1) Asking questions doesn't make you a pest, specially, when it is clear, that you are trying.
2) There are n number of guys / gals here who are far far better than me. So avoid specific address. It might perhaps put off some from answering.
3) Have lost touch, with the functionality of what we were doing. If you post some data, the way you have done earlier, it would be easier for some one to help.

Edit :
Alternatively, check out if below gives some guidelines :

SELECT 
    qrySpreadsA.ThePeriod, 
    qrySpreadsA.HY, 
    qrySpreadsA.Yields, 
    qrySpreadsA.Spreads, 
    qryAverageSpreadsA.AvgOfSpreads, 
    [AvgOfSpreads]-[Spreads] AS TheDifference
FROM 
    (
        SELECT 
            tbl_H0A0_monthly.ThePeriod, 
            tbl_H0A0_monthly.px_last AS HY, 
            tbl_10YR_monthly.px_last AS Yields, 
            [HY]-[Yields] AS Spreads
        FROM 
            tbl_H0A0_monthly 
            INNER JOIN 
            tbl_10YR_monthly 
            ON 
            tbl_H0A0_monthly.ThePeriod = tbl_10YR_monthly.ThePeriod
    ) 
    AS qrySpreadsA,
    (
        SELECT 
            Avg([tbl_H0A0_monthly.px_last]-[tbl_10YR_monthly.px_last]) AS AvgOfSpreads
        FROM 
            tbl_H0A0_monthly 
            INNER JOIN 
            tbl_10YR_monthly 
            ON 
            tbl_H0A0_monthly.ThePeriod = tbl_10YR_monthly.ThePeriod
    )
    AS qryAverageSpreadsA;

I have marked the thread as unsolved to get a wider coverage.

Thanks