How do I use the WITH statement in Access?

Question

I'm using Access and would like to simplify some of my queries by naming SELECT statements. However, I can't use the WITH statement for some reason. I have tried running the following simple example to illustrate the problem:

WITH T1 AS
(
SELECT * FROM [Transactions]
)
SELECT * FROM T1;

Where Transactions is a table of dates and amounts. When I run this query I get the following error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

What am I doing wrong?

Solution

As others have stated in the comments, Access SQL does not support the with keyword the same way that TSQL does. You can accomplish close to the same thing though by writing the first query and saving it. The saved query can then be referenced in your Access SQL as though it was a table (similar to creating a view in TSQL).

Others also noted that VBA can use the with keyword, but for a different purpose.