Emulate subquery without main table in Access

Question

Emulate subquery without main table in Access

Navigation

#1 by (5 votes)
#2 by (1 votes)

3

I can do this in SQL Server:

SELECT 'HERRAMIENTA ELÉCTRICA' AS TIPO_PRODUCTO,
0 AS DEPRECIACION,
(select sum(empid) from HR.employees) STOCK

But in Access the same query returns the following error:

Query input must contain at least one table or query

So what would be the best option to emulate this? Make a query to a table X that has at least one record and in case of having more limit it sounds dirty to me, following what indicates the error of missing at least one table in the query, so I look for a better way to do it.

PDTA: Question based on a question I have also done in SO in English.

sql ms-access-2010

asked by Juan Ruiz de Castilla 15.12.2015 в 07:53

source

2 answers

Obtain 5 last values of an array in javascript? Is it possible to find a Date value within an array?

score 5 · Answer 1

You can ask the question in the following way:

SELECT
    'HERRAMIENTA ELÉCTRICA' AS TIPO_PRODUCTO,
    0 AS DEPRECIACION,
    COALESCE(SUM(empid), 0) AS STOCK
FROM
    HR.employees;

In this way, you manage to project what you want without making the subquery.

The proof of the concept is here: link

score 1 · Answer 2

As I recall, Access does not support that kind of syntax ... Access's SQL dialect requires queries to always have at least select ... from ... .

Additionally to the solution proposed by drielnox , you can create a "dummy" table (if you like it, it can even be hidden) that contains a single row, to always have a table to use for these circumstances.