Inner Join and multiple fields

[mrr2]

Can someone please help me?

I am trying to convert daily data into montly, which I have the query to do so (created in design mode), but now I want to combine different time series.

This query does not seem to be doing to trick, can somone please help me modify accordingly?

I would like to pull from another table.field called SP_500_Index.values and inner join to my weekdays table



THank you so much,

SELECT Weekdays.Date, H15T10Y_Index.Yields
FROM Weekdays INNER JOIN H15T10Y_Index ON Weekdays.Date = H15T10Y_Index.Date
WHERE Weekdays.Date in(select DISTINCT max(Weekdays.Date) from H15T10Y_Index GROUP BY YEAR(Weekdays.Date), MONTH(Weekdays.Date))

[jzwp11]

Welcome to the forum!

First, the word "date" is a reserved word in Access so it is best not to use it as a table or field name. You will have to enclose it in square brackets when referencing it in a query: [date].


What are the fields in the table: H15T10Y_Index? What kind of data is in this table? Can you provide an example of the data?

[mrr2]

Hi jzwp11, thanks for replying.

The H15T10Y_Index is number like 6.69 (yield on a 10yr govt bond available from the treasury. Should I rename the date field to something like Period instead?

The weekdays table is all weekdays including holidays in a year.

[mrr2]

Aside: if i have been using date in my fields and in queries, could that be a cause of extremely slow operation?

[jzwp11]

I'm not sure if the field name "date" would cause performance issues, but it may be more due to a lack of indicies in your tables. Indexing the date field could help. The field name period should be OK. Here is a link to a list of reserved words and symbols in Access.

So in H15T10Y_Index you have the following 2 fields:

H15T10Y_Index
-Date
-Yields

Do you have a record for every date and does each record have a value in the yields field?

Now if you want to group by year/month, you have to do something with the yields field I assume: sum, average, max, min

Also, what other pertinent fields are in the weekdays table

Weekdays
-date
-?

[mrr2]

In the H15T10Y_Index I have the following fields:

ID - Access assigned primary key
Series_ID - which is an identifier that I use to append
Period - which is the date
Yields - which is the field name for the numerical value that corresponds to the date for the H15T10Y_Index
TimeStamp - date and time when data was uploaded

And Yes, I do have a record for every field in the H15T10Y_Index

The Weekdays Table is

ID - Primary key from access
Series ID - our input two digit number (same for each row in table)
Period- Date field (here this is all of the weekdays since 1/1/1992) If I do a left join to weekdays for the H15T10Y_Index I will get blanks in my result
Timestamp- date and time data was entered

This is the recent query that seems to work, but I would like to add another index to it, which is named H15T20Y_Index
and has the same fields, i.e. ID, Series_ID, Period, Yields, and Timestamp

SELECT Weekdays.[Period], H15T10Y_Index.Yields
FROM Weekdays LEFT JOIN H15T10Y_Index ON Weekdays.[Period]=H15T10Y_Index.Period
WHERE Weekdays.period in(select DISTINCT max(Weekdays.period) from Weekdays GROUP BY YEAR(Weekdays.period), MONTH(Weekdays.period));

[mrr2]

This seems to do the trick:

SELECT Weekdays.Period, H15T10Y_Index.Yields, H15T20Y_Index.Yields
FROM (Weekdays INNER JOIN H15T10Y_Index ON Weekdays.[Period] = H15T10Y_Index.Period) INNER JOIN H15T20Y_Index ON Weekdays.Period = H15T20Y_Index.Period
WHERE (((Weekdays.Period) In (select DISTINCT max(Weekdays.period) from Weekdays GROUP BY YEAR(Weekdays.period), MONTH(Weekdays.period))));

[jzwp11]

I'm glad you got it worked out, but what is the point of the WHERE clause you have?

SELECT Weekdays.Period, H15T10Y_Index.Yields, H15T20Y_Index.Yields
FROM (Weekdays INNER JOIN H15T10Y_Index ON Weekdays.[Period] = H15T10Y_Index.Period) INNER JOIN H15T20Y_Index ON Weekdays.Period = H15T20Y_Index.Period
WHERE (((Weekdays.Period) In (select DISTINCT max(Weekdays.period) from Weekdays GROUP BY YEAR(Weekdays.period), MONTH(Weekdays.period))));

[mrr2]

That converts daily data to month end data - is there a more efficient way to do it since it seems to be faulty lycnhpin that is causing my query to take forever...
thanks a lot for helping me out on this jzwp11

[jzwp11]

I would modify the first query as follows

SELECT Weekdays.Period, H15T10Y_Index.Yields as H15Yield, H15T20Y_Index.Yields as H20Yield
FROM (Weekdays INNER JOIN H15T10Y_Index ON Weekdays.[Period] = H15T10Y_Index.Period) INNER JOIN H15T20Y_Index ON Weekdays.Period = H15T20Y_Index.Period


Then create a new query using the query above (I'll call that query as query1). You would do your grouping in the second. You will still have to do something with the yield values I assume relative to the year/month. Are you after min or max of the 2 yields for the month perhaps? If so that would look like this:

SELECT year(query1.Period), month(query1.Period), Min(query1.H15Yield), Max(query1.H15Yield), Min(query1.H20Yield), Max(query1.H20Yield)
FROM query1
GROUP by year(query1.Period), month(query1.Period)

[mrr2]

Thanks jzwp11, I will try that out - and see if it is any faster. Right now we are leaning to creating 2 steps, one in which we combine the data into a new table, then work with that for the inner join and grouping.
Seems like it lets access run faster, but at the cost of a couple of steps...

[jzwp11]

Let us know how it works for you.

[mrr2]

Alright, I had had to recreate the database step by step to figure out why it was taking so long
The problem was that last grouping statement:

WHERE (((Weekdays.Period) In (select DISTINCT max(Weekdays.period) from Weekdays GROUP BY YEAR(Weekdays.period), MONTH(Weekdays.period))));

Here is a query that I am working on now that seems to be running faster but isn't quite what I am looking for, as it doesnt return the month end value, could you take a look at it?

px_last is the field with values
I am putting it into a test table for now

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

Thanks a lot for your help, let me know if I am not clear enough

[jzwp11]

So you want the last day of the month as defined by the month and year. So if the month was 3 and the year 2012, you want to return March 31, 2012?

If so, this expression should work:

dateadd("d", -1, dateserial(year(dateadd("m",1,period)),month(datea dd("m",1,period)),1))

The above expression creates the 1st of the next month from the period and then subtracts 1 day yielding the last day for the month/year of the period field.

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

If you are going to group by px_last, you will get several records for a period that show the same month ending date with unique px_last values.

3/31/2012|2.50
3/31/2012|2.51
3/31/2012|3.51


If it so happens that the px_last values are the same within a month for a series of records, the query will return 1 record for that group.

Is that what you are after?

[mrr2]

That unfortunatly didnt work - here is a screenshot of the output and the query that I used, based on your suggestion. What I'd like to do is get the last day of the month from my daily data series and the corresponding px_last number - am I wrongly setting up the query?

THanks for your help with this jzwp11

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;