Problem with sub-query in Access

Question

Problem with sub-query in Access

Navigation

#1 by (2 votes)

1

I have a database in Access that has the following tables:

Table Options

Columns:

Id_Option int
Option varchar (50)
Status (bit)

Contains these data:

Table Votes

Columns:

Vote_ID int
Id_Option int
Date Date Time

What I need to build a query that gets the amount of votes that each option has in a date range .

For example, if I select a range of dates between 01/08/2016 and 08/31/2016 , you should get a value similar to the following:

This is the query I use:

SELECT Opc.Opcion, (SELECT COUNT(Vts1.Id_Respuesta)
FROM Votos AS Vts1) AS [Votos],
Vts.Fecha
FROM Opcion AS Opc INNER JOIN Votos AS Vts
ON Opc.Id_Opcion = Vts.Id_Opcion
WHERE Opc.Estado = True
ORDER BY Vts.Fecha;
WHERE Opc.Estado = True AND 
(Vts.Fecha BETWEEN #01/08/2016 00:00# AND #31/08/2016 23:59#)
ORDER BY Vts.Fecha;

But the results do not match what was expected, also, if there are zero votes, sometimes I get this error:

at most one record can be returned by this subquery

How should the query be modified to generate the expected results?

sql ms-access-2010

asked by Mauricio Arias Olave 16.08.2016 в 23:59

source

1 answer

Error parsing a Json bad png image exported to pdf from itext

score 2 · Accepted Answer

You need to make a JOIN of your two tables:

select o.textoOpcionrespuesta as opcion
     , count(v.id) as votos
     , max(v.fechaRespuesta) as fecha_ultimo_voto
from OpcionRespuesta as o
     left join respuestas as v on o.id_opcion_respuesta = v.idOperacionRespuesta
where o.estado = true
  and (v.fechaRespuesta >= #01/08/2016# and v.fechaRespuesta < #01/09/2016#)
order by fecha_ultimo_voto;

By the way, to filter dates, it's a bad idea to use BETWEEN . I recommend read this article (as you see, I modified your condition WHERE to avoid problems).

If you want to do this with subqueries, here is a possible solution:

select opcion, votos, fecha_ultimo_voto
from
    (
    select id_opcion_respuesta as id
         , textoOpcionRespuesta as opcion
    from OpcionRespuesta
    where o.estado = true
    ) as o
    left join (
        select idOperacionRespuesta as id
             , count(v.idOperacionrespuesta) as votos
             , max(fechaRespuesta) as fecha_ultimo_voto
        from respuestas
        where fechaRespuesta >= #01/08/2016# and fechaRespuesta < #01/09/2016#
        group by idOperacionRespuesta
    ) as v on o.id = v.id
order by fecha_ultimo_voto;

Another observation regarding dates: The way in which Access handles the dates when you enclose them in number symbols ( # ) depends on the regional configuration of the machine where you are working. To avoid problems of that type, I recommend using the function DateSerial() :

#01/08/2016# --> DateSerial(2016,8,1)
^^^^^^^^^^^^     ~~~~~~~~~~~~~~~~~~~~
Esto puede       Esto evita el
ser ambiguo      problema de
                 ambigüedad