Emulate subquery without main table in access

Question

I can do it in SQL Server:

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

but in Accessthe same request it will show me the following error:

The query input must contain at least one table or query

So what could be the best way to emulate this? Making a query with any other table looks dirty for me.

EDIT 1:, HR.employees It may not have data, but I want to show the constants ('HERRAMIENTA ELÉCTRICA', '' 0 ') and 0 in the third column, possibly using isnull and this is not a problem.

Answer 1

Why not choose directly:

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

Answer 2

:

with employ as
    (select 2 as col from dual
    minus
    select 2 as col from dual)

:

select 'HERRAM' as tipo,
    0 as deprec,
    coalesce(sum(col), 0) as STOCK
from employ;

coalesce(x, value) , X null

Answer 3

It just doesn't work in Access. You need a FROM clause.

So, you need to have a dummy table with one record, even if you are not using one field from this table.

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

Answer 4

In Access, you can use the system table Valand Nzfor the null value:

SELECT TOP 1
    'HERRAMIENTA ELÉCTRICA' AS TIPO_PRODUCTO,
    0 AS DEPRECIACION,
    Val(Nz((select sum(empid) from HR.employees), 0)) AS STOCK
FROM
    MSysObjects