Hi
I'm getting the following error in trying to load data from a view in Access:
SQL##f - SqlState: S1000, ErrorCode: 4294964217, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.
SQL SELECT *
FROM TAA5Final
I suspect the error may be because the view accesses another view which accesses a third which accesses a table, but don't know if this is the case because of the "Type Mismatch" nature of the error.
Any help would be appreciated. It may also help to know that I sometines get asked for to enter a value for Date when I open the TAA5Final view in Access, but it doesn't always happen, so perhaps it is this that is causing the error.
PS. The reason for the above is my very limited knowledge of joins etc. There is probably a much better way of doing things for those that have the skills. I essentially have sporadic views that I need to translate to daily views i.e. your view remains unchanged from the date you first have it, until you change it. It would be easy if I just added an end date to you last view when you have another view, but this hasn't been done and I wanted to do it with script.
Here are the tables / views:
TAAViews: This is the table of people's views using the foreign keys to other tables with their names, the names of the markets and the reference to scores.
DailyReturns: This table contains the indices representing the markets for which people will have views, for every single day of the year (including public holidays and weekends).
TAA5: Just a flat table of People's views on the market at various points throughout the year i.e. a person (of which there are many) will have a view on about 13 markets (or segments) at a point in time (about one a month)
SELECT ViewOwner.FullName AS ViewOwner, Indices.IndexCode, ScoreConversion.TAA5 AS Weight, TAAViews.ViewDate AS ReturnDate
FROM ViewOwner INNER JOIN (ScoreConversion INNER JOIN (Indices INNER JOIN TAAViews ON Indices.ID = TAAViews.IndexID) ON ScoreConversion.ID = TAAViews.ScoreID) ON ViewOwner.ID = TAAViews.AppraiserID;
Query2:
SELECT DailyReturns.Date, TAA5.ReturnDate AS StartDate, DailyReturns.IndexCode, TAA5.ViewOwner
FROM DailyReturns INNER JOIN TAA5 ON DailyReturns.IndexCode = TAA5.IndexCode
WHERE DailyReturns.Date >= TAA5.ReturnDate
GROUP BY DailyReturns.Date, TAA5.ReturnDate, DailyReturns.IndexCode, TAA5.ViewOwner;
Query3:
SELECT Query2.ViewOwner, Query2.IndexCode, Query2.Date, max(Query2.StartDate) AS StartDate
FROM Query2
GROUP BY Query2.ViewOwner, Query2.IndexCode, Query2.Date;
TAA5Final:
SELECT Query3.ViewOwner AS Owner, Query3.IndexCode AS IndexCode, Query3.Date AS ReturnDate, TAA5.Weight AS Weight
FROM Query3 INNER JOIN TAA5 ON (Query3.ViewOwner = TAA5.ViewOwner) AND (Query3.IndexCode = TAA5.IndexCode) AND (TAA5.ReturnDate = Query3.StartDate);
