Inner Join and multiple fields

[jzwp11]

You want the record that has a date value equal to the last day of the month and the associated px_last value in that particular record?

[jzwp11]

BTW, the query you showed was incorrect

you had this:

SELECT DateAdd("d",1,DateSerial(Year([Period]),Month([Period]),1))-1 AS MonthEnd, px_last as Price
FROM tbl_H15T10Y
GROUP BY DateAdd("d",1,DateSerial(Year([Period]),Month([Period]),1))-1, px_last;

It should be this

SELECT dateadd("d", -1, dateserial(year(dateadd("m",1,period)),month(datea dd("m",1,period)),1)) as MonthEND, px_last as Price
FROM tbl_H15T10Y

[mrr2]

Interestingly - this Query which takes a monthly average works perfectly:

SELECT DateAdd("m",1,DateSerial(Year([Period]),Month([Period]),1))-1 AS MonthEnd, AVG(px_last) AS Monthly_Avg
FROM tbl_vix
GROUP BY DateAdd("m",1,DateSerial(Year([Period]),Month([Period]),1))-1;

It perplexes me that the query can find the month start and end values the take the average, but I am having so much trouble funding the month end number only.

[mrr2]

You want the record that has a date value equal to the last day of the month and the associated px_last value in that particular record?

That is exactly right

[jzwp11]

To find the price at the end of each month, you have to find when the period date = the end of the month date. So how about the following query. Note that there is no GROUP BY clause in the following query since you have no need to group records--you are trying to find specific records. This assumes that you have a record for the ending date of every month, if not the query will ignore that day

SELECT tbl_H15T10Y.Period, dateadd("d", -1, dateserial(year(dateadd("m",1,period)),month(datea dd("m",1,period)),1)) as MonthEND, px_last as Price
FROM tbl_H15T10Y
WHERE tbl_H15T10Y.Period=dateadd("d", -1, dateserial(year(dateadd("m",1,period)),month(datea dd("m",1,period)),1))

[mrr2]

This almost works, thanks jzwp11 - However if there is no datapoint for the last day of the month (if the last day of the month is a weekend) it ignores that month as you mentioned, can it find the last most value in that case, I am wondering if the max would work?

[jzwp11]

That will take a little more effort, so here goes

Create a query that breaks out the year and month for each date in the table

query name: qryPeriod
SELECT Year(period) AS periodyear, Month(period) AS periodmonth, tbl_H15T10Y.period
FROM tbl_H15T10Y;

Next create an aggregate query using the query above

query name: qryDateMaxByYearMonth

SELECT qryPeriod.periodyear, qryPeriod.periodmonth, Max(qryPeriod.period) AS MaxOfperiod
FROM qryPeriod
GROUP BY qryPeriod.periodyear, qryPeriod.periodmonth;

Now create a final query that joins the above qryDateMaxByYearMonth back to your original table

query name: qryFinal

SELECT tbl_H15T10Y.period, tbl_H15T10Y.px_last
FROM tbl_H15T10Y INNER JOIN qryDateMaxByYearMonth ON tbl_H15T10Y.period = qryDateMaxByYearMonth.MaxOfperiod;

[mrr2]

How do I run queries after the previous - should i be creating tables each time?
(sorry newbie)

[jzwp11]

Build and save each query as indicated. You only need to run the qryFinal; Access runs the others as it needs to when it runs qryFinal

[mrr2]

Thanks jzwp11 - that worked. I appreciate your help with this

I am going to see if I can do some joining next, i.e. H15T10Y and H15T20Y and will keep you posted on how it turns out.

Thanks again!

[jzwp11]

You're welcome; glad you got this part worked out.