July 26, 2005 at 10:01 am
I am writing the below Access Query but get an error when executing it. It says "Syntax error in the FROM clause", but I can't figure it out.
Would anyone please educate me on how to fix this error? Thanks!
SELECT a. client_id FROM [dedup_2004] a
inner join [dedup_2004] b
where a.lname = b.lname
and a.fname = b.fname
and left(a.addr1,2) = left(b.addr1,2)
and left(a.zip,5) = left(b.zip,5)
and a.date_entered_db = case when a.status = "Y" then a.date_entered_db else b.date_entered_db end) ;
Also, I want to write a query with case statement in the select clause. How can I get the below query work? Thanks!
SELECT a. client_id, case when a.date_entered_db >= b.date_entered_db then a.date_entered_db else b.date_entered_db end FROM [dedup_2004] a
inner join [dedup_2004] b
where a.lname = b.lname
and a.fname = b.fname
and left(a.addr1,2) = left(b.addr1,2)
and left(a.zip,5) = left(b.zip,5)
and a.date_entered_db = case when a.status = "Y" then a.date_entered_db else b.date_entered_db end) ;
July 26, 2005 at 10:23 am
Right before the semi-colon there is an extra parentheses.. ??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 26, 2005 at 10:25 am
Thanks...
I've found the cause for the first error... it should have been ON instead of WHERE.
July 27, 2005 at 1:30 am
I use a graphical query builder,
Access has one, try it.
Or if you us SQL Server as back-end create your query with Query Analyzer and then paste it in Access.
This helps me a lot.
July 27, 2005 at 7:41 am
I believe the way the query was written, ON and WHERE could have been used interchangeably. Concerning CASE statements, you have to use IIF in Access.
July 27, 2005 at 8:23 am
When writing a query that joins tables, it runs a lot more efficiently if you use ON instead of doing all the work in the WHERE clause. This is because if you join ON something, the result set is filtered - only information that matches the ON condition makes it into the result set. If you put all those conditions in the where clause, everything in the first table gets joined with everything in the second table, and the where clause has A*B rows to look through -- most of which don't belong together anyway.
Also... I'm not very familiar with Access, but don't multiple conditions in a where clause need to be inside parenthesis?
where (a.lname = b.lname)
and (a.fname = b.fname)
and (etc... ???
Good Luck!
Casey
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply