CODEWITHSUNDEEP
sqlms-access
VeryBasicApplication
VeryBasicApplication

Reputation: 31

Access SQL SELECT Query: Required to use the table alias in WHERE clause?

Current Environment: Split Access Database, Local Access Front End, Access Backend on shared network drive

I'm writing a stupid long SELECT statement. Using table aliases to makes my code more readable.

I was taught to write out full table names in WHERE statements because they are executed before the FROM statement. However, when I did that - Access isn't finding the table / field referenced when I write the full table name out. Strangely, I tried using the alias in the WHERE statement and it works!!?

Does Access execute the statement differently than I was taught? Or am I unknowingly causing it to do it this way?

Shortened version of my SQL code that doesn't work:

SELECT [C].Multi_File_Case_ID,
[C].Case_Report_Number,
[C].Include_In_Casebook,
[C].Case_Status,
FROM Case_Tbl AS [C] 
WHERE [Case_Tbl].Include_In_Casebook = True;

Edit: A word and a capitalization ;)

Upvotes: 2

Views: 3847

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Once you define a table reference as an alias in the FROM, you need to use that alias everywhere in the query. The alias is more than a nickname; it is a name change for the scope of the query.

I would advise you to drop the square braces. Queries are easier to read and write as:

SELECT c.Multi_File_Case_ID, c.Case_Report_Number, c.Include_In_Casebook, c.Case_Status
FROM Case_Tbl AS c
WHERE c.Include_In_Casebook = True;

When you do not define a table alias, the table name itself serves as the alias.

Upvotes: 4

Related Questions