Search code examples
sqlms-accessjoinms-access-2007cross-join

CROSS JOIN of query and single-row table

I have a big query and a table with a single row (I store some constants in it).

What is the best way to join the row of the table with every row of the query considering that Access doesn't support cross joins with queries?

SELECT * from (subquery), table -- Invalid in Access
Solution

  • Access will accept a cross join between a query named some_query and a table named some_table like this ...

    SELECT *
FROM some_query, some_table;

    With your names, try it this way ...

    SELECT * from [some query], [table]

    IOW, get rid of the parentheses, and enclose the data source names in square brackets because of the space in some query and because table is a reserved word.

    OTOH, if you meant some query to be a placeholder for the text of a SQL statement instead of the name of a saved query, consider this example.

    SELECT *
FROM 
(SELECT * FROM agents) AS sub, Dual;