Error in Select from Access

Anonymous — Fri, 10 Jan 2014 06:15:43 GMT

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);

topic Error in Select from Access in QlikView

Error in Select from Access