Subconsulta anidada en un campo de una consulta que no funciona

¿Por qué no usas una función de dominio (DLast, DLookup, DMax...) en vez de una subconsulta para obtener el dato? Para mí que es mucho más sencillo.

Ya que quieres trabajar con datos de un registro anterior, aquí tienes un sistema para hacerlo:

http://neckkito.xyz/nck/index.php/ejemplos/18-codigo/181-obtener-datos-del-registro-anterior 

Preguntas similares:

Registros con campos del registro anterior

Impedir ingresar una fecha anterior a la del anterior registro en access

Copiar contenido de un campo anterior con VBA

Procedimiento de evento para tomar datos de un registro anterior y traerlos a un registro nuevo

Y probablemente aún haya más respuestas sobre el mismo tema en esta misma web

Hola.

En primer lugar, muchas gracias por las molestias y el tiempo que te haya llevado buscar y copiar aquí distintas posibles respuestas.

Siento decirte que ninguna me valió. Me he mirado todas, pero creo que todas se basaban en la misma premisa: tener un campo que te fuera ordenando los registros. Pero ese no es mi caso. Yo tengo una tabla con las compras y ventas por fechas, y luego saco en una consulta los totales por fechas y trimestres, entonces ahí no tengo manera de ordenar esos registros. Claro que podemos ordenarlos, pues tenemos el año y el trimestre. Y es ahí donde tengo que fijarme para coger el valor del trimestre anterior. No sé si consigo explicarme.

Aún así, he conseguido medio solucionarlo. No sabía que se podía usar la función DBúsq para buscar valores dentro de la misma consulta. Ahora mismo la tengo así (te enseño las fórmulas también, que las saqué previamente en un Excel, y luego las he convertido a Access):

Y la consulta CModelo303Paso1 es esta:

Ahora bien. Tengo dos problemas:

1. En la consulta definitiva, CModelo303Definitiva, cuando voy a ver la hoja de datos, me carga muy lento. Yo entiendo que es por las dos búsquedas (que las he sustituido por suma, pero al final es lo mismo). ¿Eso se puede arreglar de otra manera? ¿O plantear de modo distinto?
2. En la hoja de datos, me cuadra todo salvo un valor. Si te fijas en la imagen que te adjunto a continuación, la Casilla67Previo2 siempre es el valor de la Casilla71 del trimestre anterior. Esa Casilla67Previo2 lo saco buscándolo. El campo Casilla71 es la resta de Casilla66 y Casilla67. Sin embargo, en el cuarto trimestre (el que tengo seleccionado) me saca un valor raro. Analizando por separado, es decir, utilizando la función dbúsq (o dsuma) para buscar cada casilla por separado, me ha dado cuenta de que, solo en ese año y en ese cuarto trimestre, cuando busco la Casilla67 no me saca 0, sino un número que se saca de vete tú a saber dónde. He probado mil y una cosas a ver si daba con el error, pero nada.

Lo tengo casi, casi, sacado. Espero que me puedas dar alguna idea de por dónde seguir.

Mil gracias.

Un saludo.

La verdad, es que me pierdo un poco con tanta consulta y tanta fórmula, pero te voy a intentar responder:

1º/ Que te cargue lento es bastante lógico, ya que para abrir la consulta definitiva tienes que "abrir" 3 consultas previas, cada una con sus cálculos. Y al no tener filtros (que yo vea), tiene que hacer los cálculos para todos los registros (a más registros, más cálculos y mayor tiempo). Una forma de acelerar la ejecución sería filtrar las consultas intermedias para que trabajen con menos registros (pues el WHERE se ejecuta antes que el SELECT).

2º/ En la consulta que muestras, tienes un campo que da #Error, lo que indica que hay algo en alguna de tus fórmulas que no termina de funcionar del todo bien.

Mi suposición es que esta parte de la fórmula: ..." AND Trimestre1= " & [Trimestre]-1 no es correcta:

Si estás en los trimestre 2 a 4 entiendo que sí funciona bien, pero si estás en el trimestre 1, no te va a restar el 4º trimestre del año anterior, sino un "trimestre 0" del año actual, que no existe. Eso lo tendrás que tener en cuenta usando SiInm.

Muchas gracias. ¿Y el problema de que no funcione correctamente el dlookup? 

Lo de las fórmulas ya se que es complicada, pero son las que necesito para poder calcular las casillas. Filtrar las consultas lo mismo tendré que hacer a lo mejor desde el propio informe, según el año, y utilizar un subinforme. 

No sé a qué DLookup te refieres, pero por o que se ve en tu último pantallazo, el error te lo da en el campo calculado Casilla67Previo1, y como solo te lo da en el trimestre 1, el error, con un 99,99% de probabilidad está en lo de trimestre-1 que te comentaba antes.

En cuanto a los filtros, filtrar el informe no te va a solucionar nada, porque las consultas se ejecutan completas y luego se filtran los datos a mostrar. A lo que me refiero es que, por lo menos, en la primera consulta, añadas un criterio para filtrar por el año que quieres sacar (entiendo que el año actual y/o el anterior)

De todas formas, si usas funciones VBA propias, puedes mejorar muchísimo la rapidez de respuesta, y sin saber exactamente cómo haces los cálculos, probablemente lo puedas hacer todo con una única consulta.

En CModelo303Paso1 sacas el año, el trimestre y la cuotaIva, que veo que es igual que Casilla66. En esa misma consulta puedes calcular Casilla67 (no tengo claro cómo lo debes hacer) y la Casilla 71 como resta de las dos anteriores.

Si casilla67 depende del valor del registro anterior, puedes crear una función que abra un recordset (ordenado por año y trimestre), se coloque en el año y trimestre que quieras, se mueva un registro hacia atrás y coja el valor (que será lo que devuelva la función)

Otra opción sería hacerlo como explica Mihura aquí (ojo, el código así no te sirve, tendrás que adaptarlo): http://www.accessaplicaciones.com/ejemplos.html#sqf02

Por cierto, aprovechando la web de Mihura, y en relación a la pregunta Hacer referencia al registro anterior de una consulta de Access, si bien es cierto que es una manera de hacerlo, y con pocos registros funciona perfectamente, a nada que empieces a tener muchos registros en tu BD, la consulta se va a hacer cada vez más lenta. Una alternativa mejor sería una función propia, como ésta: http://www.accessaplicaciones.com/ejemplos.html#sq03

Vale. Pues ya voy viendo lo que has mandado para intentar hacerlo. Muchas gracias. 

A ver, he hecho esto:

Public Function CuotaIVA(Año As String, Trimestre As Double) As Variant
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA " _
                & " FROM T06Trimestres INNER JOIN (T04IVA INNER JOIN (((T02Grupos INNER JOIN T02Subgrupos ON T02Grupos.CodigoGrupo = T02Subgrupos.CodigoGrupo) INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta) ON T04IVA.CodigoIVA = T09ComprasVentas.CodigoIVA) ON T06Trimestres.CodigoTrimestre = T09ComprasVentas.CodigoTrimestre" _
                & " GROUP BY Year([Fecha]), T09ComprasVentas.CodigoTrimestre" _
                & " HAVING (((Year([Fecha]))='" & Año & "') AND ((T09ComprasVentas.CodigoTrimestre)=" & Trimestre & "))" _
                & " ORDER BY Year([Fecha]), T09ComprasVentas.CodigoTrimestre"
Set rs = dbs.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
     rs.MoveFirst
     CuotaIVA = rs.Fields.Item("CuotaIVA")
End If
End Function

Con eso, obtenga la cuota de iva de un año y un trimestre en concreto.

¿A esto te referías? Yo no veo mucha diferencia en la carga de la consulta en la cual lo hago. Si no es, ¿podías ponerme un ejemplo? He buscado por internet por no encuentro nada.

Gracias.

A ver, he mejorado el código, sería este:

Public Function CuotaIVA(Año As String, Trimestre As Double) As Variant
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA " _
                & " FROM T06Trimestres INNER JOIN (T04IVA INNER JOIN (((T02Grupos INNER JOIN T02Subgrupos ON T02Grupos.CodigoGrupo = T02Subgrupos.CodigoGrupo) INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta) ON T04IVA.CodigoIVA = T09ComprasVentas.CodigoIVA) ON T06Trimestres.CodigoTrimestre = T09ComprasVentas.CodigoTrimestre" _
                & " WHERE (((Year([Fecha]))='" & Año & "') AND ((T09ComprasVentas.CodigoTrimestre)=" & Trimestre & "))" _
Set rs = dbs.OpenRecordset(strSQL)
If Not (rs.EOF And rs.BOF) Then
     rs.MoveFirst
     CuotaIVA = rs.Fields.Item("CuotaIVA")
End If
End Function

Con esa función, ¿qué calculas (casilla 66 o casilla 67)?. Si calculas el valor de la casilla 66, la función no la necesitas para nada, pues ya lo tienes en tu consulta de totales CModelo303Paso1.

Además, no notas diferencia en la carga, porque seguirás abriendo las consultas sin filtrar.

Yo a lo que me refería era a usar una función que te devolviera el iva del trimestre anterior (¿Casilla 67?), algo así, si la casilla 67 fuera la casilla 66 del trimestre anterior:

Public Function Casilla67(elAño As Integer, elTrimestre As Byte) As Currency
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA, " _
                & " Year([Fecha]) AS Año, T09ComprasVentas.CodigoTrimestre AS Trimestre " _
                & " FROM T06Trimestres INNER JOIN (T04IVA INNER JOIN (((T02Grupos INNER JOIN T02Subgrupos ON T02Grupos.CodigoGrupo = T02Subgrupos.CodigoGrupo) INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta) ON T04IVA.CodigoIVA = T09ComprasVentas.CodigoIVA) ON T06Trimestres.CodigoTrimestre = T09ComprasVentas.CodigoTrimestre" _
                & " WHERE Year([Fecha]) IN (" & elAño & "," & elAño-1 & ") ORDER BY Año,Trimestre""
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.FindFirst "Año=" & elAño & " AND Trimestre=" & elTrimestre
If Rst.NoMatch Then
     Casilla67=0
Else
     rst.MovePrevious
     Casilla67= rst("CuotaIVA")
End If
rst.Close
Set rst=Nothing
End Function

o así:

Public Function Casilla67(elAño As Integer, elTrimestre As Byte) As Currency
Dim rst As DAO.Recordset
Dim strSQL As String
If elTrimestre=1 Then
     strSQL = "SELECT Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA " _
                & " FROM T06Trimestres INNER JOIN (T04IVA INNER JOIN (((T02Grupos INNER JOIN T02Subgrupos ON T02Grupos.CodigoGrupo = T02Subgrupos.CodigoGrupo) INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta) ON T04IVA.CodigoIVA = T09ComprasVentas.CodigoIVA) ON T06Trimestres.CodigoTrimestre = T09ComprasVentas.CodigoTrimestre" _
                & " WHERE Year([Fecha])=" & elAño-1 & "') AND ((T09ComprasVentas.CodigoTrimestre)=4))" 
Else
     strSQL = "SELECT Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA " _
                & " FROM T06Trimestres INNER JOIN (T04IVA INNER JOIN (((T02Grupos INNER JOIN T02Subgrupos ON T02Grupos.CodigoGrupo = T02Subgrupos.CodigoGrupo) INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta) ON T04IVA.CodigoIVA = T09ComprasVentas.CodigoIVA) ON T06Trimestres.CodigoTrimestre = T09ComprasVentas.CodigoTrimestre" _
                & " WHERE Year([Fecha])=" & elAño & "') AND ((T09ComprasVentas.CodigoTrimestre)=" & elTrimestre & "))" 
End If
Set rst = CurrentDb.OpenRecordset(strSQL)rst.FindFirst "Año=" & elAño & " AND Trimestre=" & elTrimestre
If Rst.RecordCount=0 Then
     Casilla67=0
Else
     Casilla67= rst("CuotaIVA")
End If
rst.Close
Set rst=Nothing
End Function

O aplicando la misma lógica, lo podrías hacer en la expresión de la consulta: Si el trimestre es 1, dame la cuota iva del trimestre 4 del año anterior, y si no, dame la cuota iva del trimestre anterior del mismo año. Que es lo que te comentaba para solucionar el error que tienes en los registros del 1º trimestre de tu consulta.

Vale, perfecto. Pues con eso puedo seguir. Muchas gracias.

Hola, mira. Ya tengo el código sacado en VBA.

Option Compare Database
Public Function Casilla66(elAño As Integer, elTrimestre As Byte) As Currency 'Esta casilla es la cuota de IVA del trimestre
Dim rst As DAO.Recordset
Dim strSQL As String
    'Método 1 A través de una consulta, sumo los ingresos del trimestre del año solicitado, y se los restos a los gastos del trimestre del año solicitado
    strSQL = "SELECT Year([Fecha]) AS Año, T09ComprasVentas.CodigoTrimestre, Sum(IIf([T02Subgrupos].[Subgrupo]='Ingresos generales',[CuotaDeIVA],-[CuotaDeIVA])) AS CuotaIVA " _
                & " FROM (T02Subgrupos INNER JOIN T03CuentasContables ON T02Subgrupos.CodigoSubgrupo = T03CuentasContables.CodigoSubgrupo) INNER JOIN T09ComprasVentas ON T03CuentasContables.CodigoCuenta = T09ComprasVentas.CodigoCuenta " _
                & " WHERE (((Year([Fecha]))=" & elAño & ") AND ((T09ComprasVentas.CodigoTrimestre) In (" & elTrimestre & "," & elTrimestre & "))) " _
                & " GROUP BY Year([Fecha]), T09ComprasVentas.CodigoTrimestre"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    If Not (rst.EOF And rst.BOF) Then
         rst.MoveFirst
         Casilla66 = rst("CuotaIVA")
    End If
    rst.Close
    Set rst = Nothing
    'Método 2 Sumo los ingresos del trimestre del año solicitado, y se los restos a los gastos del trimestre del año solicitado
    Casilla66 = DSum("CuotaDeIVA", "T09ComprasVentas", "Year(Fecha)=" & elAño & " And CodigoTrimestre=" & elTrimestre & " And Tipo='Ingreso'") - DSum("CuotaDeIVA", "T09ComprasVentas", "Year(Fecha)=" & elAño & " And CodigoTrimestre=" & elTrimestre & " And Tipo='Gasto'")
End Function
Public Function Casilla67(elAño As Integer, elTrimestre As Byte) As Currency 'Esta casilla es la cuota que se arrastra de trimestres anteriores
Dim Casilla66TrimestreAnterior As Currency
Dim Casilla71TrimestreAnterior As Currency
    If elTrimestre = 1 Then
        Casilla67 = 0
    Else
        Casilla66TrimestreAnterior = Casilla66(elAño, elTrimestre - 1)
        Casilla71TrimestreAnterior = Casilla71(elAño, elTrimestre - 1)
        If Casilla66TrimestreAnterior > 0 Then
            If Casilla67(elAño, elTrimestre - 1) > Casilla66TrimestreAnterior Then
                Casilla67 = Abs(Casilla71TrimestreAnterior)
            Else
                Casilla67 = 0
            End If
        Else
            Casilla67 = Abs(Casilla71TrimestreAnterior)
        End If
    End If
End Function
Public Function Casilla71(elAño As Integer, elTrimestre As Byte) As Currency 'Es la diferencia entre las casillas 67 y 67
    If Casilla67(elAño, elTrimestre) = 0 Then
        Casilla71 = Casilla66(elAño, elTrimestre)
    Else
        Casilla71 = Casilla66(elAño, elTrimestre) - Casilla67(elAño, elTrimestre)
    End If
End Function

Las fórmulas son las que son para obtener esas tres casillas, que te indico comentado lo que significan. Y ya he comprobado que lo calcula todo correctamente.

Ahora el problema que tengo es que tarda muchísimo en cargar. Yo creo que el problema está en el cálculo de la casilla 66 (te he puesto dos métodos, me da igual uno que otro), es decir, la cuota de IVA del trimestre del año que quiero mostrar. Tú date cuenta de que, para calcular esa cuota de IVA, tiene que sumar la cuota de IVA de todos los ingresos del trimestre, y restarlo a la suma de la cuota de IVA de todos los gastos del trimestre. Ese cálculo tiene que hacerlo sí o sí, no una vez, sino dos veces (una para los ingresos, y otra para los gastos, y después restarlos), y son muchos registros. Y, para más inri, en el cálculo de las casillas 67 y 71 tiene que volver a calcularlo- Me da igual hacerlo en una consulta, que con una consulta creada ex profeso mediante VBA, que con un DSum mediante VBA, o cualquier otro sistema que se me pudiera ocurrir (que no son muchos más, ya te lo digo). ¿De qué manera puedo hacer que lo calcule más rápido?

Una posible solución sería que, en la función para calcular la casilla 66 (te repito, la cuota de iva), la consulta te filtrara el trimestre del año que quieres y el anterior (ya lo hace), y te calculase y guardase esos dos valores para el resto de las fórmulas (la casilla 66 del trimestre que estás calculando, y la casilla 66 del trimestre anterior), salvo cuando fuera el primer trimestre, que la casilla 66 del trimestre que quiero y del trimestre anterior serían cero. Esto se me ocurre que se podría hacer en una única función, que te saquase esos tres valores, pero no sé cómo hacerlo.

¡Muchas gracias!

Bueno, en una única función no, porque necesito esos tres valores para luego mostrarlos en el informe. Que los guardara, de tal modo que no tuviera que volver a calcularlos de nuevo.

¡Mil gracias!

Me refiero a declarar una variable para todo el módulo y asignarle el valor que yo quiero, de forma que pueda reutilizarlo en otras funciones. Estoy haciendo pruebas, pero no doy con la tecla.

Solucionado. Estaba probando las funciones que había creado con VBA en una consulta, y como esta tiene que tener alguna tabla para poder funcionar, escogía la de compras y ventas que es de donde tenía que calcular la cuota de IVA. Cuando yo escogía un año y un trimestre, mediante un filtro en la consulta, tenía que recorrer tooooodos los registros de la tabla. Ahí es donde estaba el error. Cuando lo he probado en un formulario, iba correctamente. Y lo acabo de añadir al informe en que quería que calculara esa información, y va fenómeno.

Muchas gracias.