Como hacer una consulta de access que me arroje suma por niveles de cuenta.
Un gusto saludarlos.
Les comento, estoy haciendo un pequeño programa contable para un pequeño negocio que abrí, el asunto es que no se como crear una consulta, que me permita visualizar.
1. La suma por niveles (nivel 1 primer dígito del campo cod_puc, nivel 2 dos primeros dígitos, nivel 3 cuatro primeros dígitos, nivel 4 seis primeros dígitos) de las cuentas contables, tanto del debe como del haber.
2. Que me permita sacar los saldos iniciales y finales según la naturaleza de las cuentas.
Tengo una tabla en access llamada, item_documento_ soporte, y de esa tabla tome los siguientes Campos:
cod_puc, nom_cuenta_puc, dónde cod_puc, es el código de la cuenta y, Nom_cuenta puc, es el nombre de la cuenta, Cod_cliente, Nom_cliente, debe ,haber, Fecha_doc_soporte.
Espero haberme explicado bien, si me pueden echar una mano seria de gran ayuda.
2 respuestas
Si en el generador de consultas se activa la opción de [TOTALES] se podrán crear subconjuntos de datos (por ejemplo los tres primeros caracteres para obtener el tercer nivel), se podrán crear cálculos (nuevos campos calculados) para cada uno de los subconjuntos obtenidos y llevarlos a un formulario u informe.
Analiza si esta opción cumple con las expectativas, en ocasiones se utiliza la alternativa de generar una consulta de agrupación con los campos imprescindibles y utilizarla como filtro para crear una consulta mas completa (añadiendo los datos que imposibilitarían crear la agrupación).
Gracias por tu respuesta, la verdad ya lo intente pero no sale la información que necesito.
Aprecio que te hallas tomado en tiempo para contestar.
Si se simplifica la consulta de totales (utilizando solo los campos mínimos e indispensables) se obtiene una consulta que se puede utilizar a modo de condición sobre el conjunto de 'datos en bruto'.
Solo se precisa una nueva consulta que las asocie a ambas, esto es: se relacionan ambas por los 'datos únicos e indispensables' de la otra (y suele funcionar correctamente).
Intentar resolverlo solo con la consulta de datos agrupados es uno de los típicos problemas que complican, los campos 'no agrupados' generan conflictos que desaparecen al cambiar el método.
Quizás exponiendo un intento infructuoso (con muestras de lo que se desea y lo que se obtiene) pueda permitir aportar soluciones practicas o consejos mas acertados.
Aquí te explico cómo estructurarla.
Consulta de Sumas por Niveles de Cuenta
Deberás crear una sola consulta que te calcule las sumas de Debe y Haber y los códigos de los diferentes niveles.
1. Estructura de la Consulta (Vista Diseño o SQL)
En la vista Diseño de Consulta de Access, agrega la tabla item_documento_soporte. Luego, habilita la fila Totales (el botón Σ en la cinta de opciones) para poder agrupar y sumar.
Campo Tabla Total Criterios
Izquierda([cod_puc]; 1) (Calculado) Agrupar por
Izquierda([cod_puc]; 2) (Calculado) Agrupar por
Izquierda([cod_puc]; 4) (Calculado) Agrupar por
Izquierda([cod_puc]; 6) (Calculado) Agrupar por
debe item_documento_soporte Suma
haber item_documento_soporte Suma
Fecha_doc_soporte item_documento_soporte Donde Si necesitas filtrar por fecha
¡Qué gusto saludarte también! Es una excelente iniciativa que desarrolles tu propio sistema contable. Para crear la consulta en Microsoft Access que necesitas, que sume por niveles de cuenta y te permita manejar saldos, deberás usar funciones de texto como Izquierda (Left) y consultas de resumen (agrupación).
Aquí te explico cómo estructurarla.
Consulta de Sumas por Niveles de Cuenta
Deberás crear una sola consulta que te calcule las sumas de Debe y Haber y los códigos de los diferentes niveles.
1. Estructura de la Consulta (Vista Diseño o SQL)
En la vista Diseño de Consulta de Access, agrega la tabla item_documento_soporte. Luego, habilita la fila Totales (el botón Σ en la cinta de opciones) para poder agrupar y sumar.
Campo Tabla Total Criterios
Izquierda([cod_puc]; 1) (Calculado) Agrupar por
Izquierda([cod_puc]; 2) (Calculado) Agrupar por
Izquierda([cod_puc]; 4) (Calculado) Agrupar por
Izquierda([cod_puc]; 6) (Calculado) Agrupar por
debe item_documento_soporte Suma
haber item_documento_soporte Suma
Fecha_doc_soporte item_documento_soporte Donde Si necesitas filtrar por fecha
Exportar a Hojas de cálculo
2. Campos Calculados para Niveles
Para obtener los códigos por nivel, usa la función Izquierda (Left) de Access, que extrae caracteres desde la izquierda de una cadena de texto.
Nivel Nombre del Campo Calculado Expresión
Nivel 1 Nivel_1: Izquierda([cod_puc]; 1) Primer dígito
Nivel 2 Nivel_2: Izquierda([cod_puc]; 2) Dos primeros dígitos
Nivel 3 Nivel_3: Izquierda([cod_puc]; 4) Cuatro primeros dígitos
Nivel 4 Nivel_4: Izquierda([cod_puc]; 6) Seis primeros dígitos
Importante: En la fila Total de estos campos, debes seleccionar Agrupar por.
¡Qué gusto saludarte también! Es una excelente iniciativa que desarrolles tu propio sistema contable. Para crear la consulta en Microsoft Access que necesitas, que sume por niveles de cuenta y te permita manejar saldos, deberás usar funciones de texto como Izquierda (Left) y consultas de resumen (agrupación).
Aquí te explico cómo estructurarla.
Consulta de Sumas por Niveles de Cuenta
Deberás crear una sola consulta que te calcule las sumas de Debe y Haber y los códigos de los diferentes niveles.
1. Estructura de la Consulta (Vista Diseño o SQL)
En la vista Diseño de Consulta de Access, agrega la tabla item_documento_soporte. Luego, habilita la fila Totales (el botón Σ en la cinta de opciones) para poder agrupar y sumar.
Campo Tabla Total Criterios
Izquierda([cod_puc]; 1) (Calculado) Agrupar por
Izquierda([cod_puc]; 2) (Calculado) Agrupar por
Izquierda([cod_puc]; 4) (Calculado) Agrupar por
Izquierda([cod_puc]; 6) (Calculado) Agrupar por
debe item_documento_soporte Suma
haber item_documento_soporte Suma
Fecha_doc_soporte item_documento_soporte Donde Si necesitas filtrar por fecha
Exportar a Hojas de cálculo
2. Campos Calculados para Niveles
Para obtener los códigos por nivel, usa la función Izquierda (Left) de Access, que extrae caracteres desde la izquierda de una cadena de texto.
Nivel Nombre del Campo Calculado Expresión
Nivel 1 Nivel_1: Izquierda([cod_puc]; 1) Primer dígito
Nivel 2 Nivel_2: Izquierda([cod_puc]; 2) Dos primeros dígitos
Nivel 3 Nivel_3: Izquierda([cod_puc]; 4) Cuatro primeros dígitos
Nivel 4 Nivel_4: Izquierda([cod_puc]; 6) Seis primeros dígitos
Exportar a Hojas de cálculo
Importante: En la fila Total de estos campos, debes seleccionar Agrupar por.
¡Qué gusto saludarte también! Es una excelente iniciativa que desarrolles tu propio sistema contable. Para crear la consulta en Microsoft Access que necesitas, que sume por niveles de cuenta y te permita manejar saldos, deberás usar funciones de texto como Izquierda (Left) y consultas de resumen (agrupación).
Aquí te explico cómo estructurarla.
Consulta de Sumas por Niveles de Cuenta
Deberás crear una sola consulta que te calcule las sumas de Debe y Haber y los códigos de los diferentes niveles.
1. Estructura de la Consulta (Vista Diseño o SQL)
En la vista Diseño de Consulta de Access, agrega la tabla item_documento_soporte. Luego, habilita la fila Totales (el botón Σ en la cinta de opciones) para poder agrupar y sumar.
Campo Tabla Total Criterios
Izquierda([cod_puc]; 1) (Calculado) Agrupar por
Izquierda([cod_puc]; 2) (Calculado) Agrupar por
Izquierda([cod_puc]; 4) (Calculado) Agrupar por
Izquierda([cod_puc]; 6) (Calculado) Agrupar por
debe item_documento_soporte Suma
haber item_documento_soporte Suma
Fecha_doc_soporte item_documento_soporte Donde Si necesitas filtrar por fecha
Exportar a Hojas de cálculo
2. Campos Calculados para Niveles
Para obtener los códigos por nivel, usa la función Izquierda (Left) de Access, que extrae caracteres desde la izquierda de una cadena de texto.
Nivel Nombre del Campo Calculado Expresión
Nivel 1 Nivel_1: Izquierda([cod_puc]; 1) Primer dígito
Nivel 2 Nivel_2: Izquierda([cod_puc]; 2) Dos primeros dígitos
Nivel 3 Nivel_3: Izquierda([cod_puc]; 4) Cuatro primeros dígitos
Nivel 4 Nivel_4: Izquierda([cod_puc]; 6) Seis primeros dígitos
Exportar a Hojas de cálculo
Importante: En la fila Total de estos campos, debes seleccionar Agrupar por.
3. Código SQL Resultante (Ejemplo)
El código SQL de tu consulta se vería similar a esto:
SELECT Left([cod_puc], 1) AS Nivel_1, Left([cod_puc], 2) AS Nivel_2, Left([cod_puc], 4) AS Nivel_3, Left([cod_puc], 6) AS Nivel_4, Sum(item_documento_soporte.debe) AS SumaDebe, Sum(item_documento_soporte.haber) AS SumaHaber FROM item_documento_soporte GROUP BY Left([cod_puc], 1), Left([cod_puc], 2), Left([cod_puc], 4), Left([cod_puc], 6) ORDER BY Left([cod_puc], 1);
Esta consulta te mostrará las sumas del Debe y Haber para cada combinación única de los niveles 1, 2, 3 y 4.
Cálculo de Saldos Iniciales y Finales
Para manejar saldos, necesitas determinar la naturaleza de cada cuenta y una fecha de corte.
1. Determinar la Naturaleza de la Cuenta (Clasificación)
La naturaleza de la cuenta (Débito o Crédito) es esencial para calcular el saldo. Lo ideal es tener una tabla maestra de cuentas (puc) donde se defina la naturaleza (por ejemplo, con un campo llamado Naturaleza).
Naturaleza Débito (Activo, Gasto, Costo): Saldo = Debe - Haber
Naturaleza Crédito (Pasivo, Patrimonio, Ingreso): Saldo = Haber - Debe
Si no tienes una tabla de PUC, puedes inferir la naturaleza a partir del primer dígito del cod_puc (según el plan de cuentas que uses):
Primer Dígito Clase Naturaleza Principal
1 Activo Débito (Saldo = Debe - Haber)
2 Pasivo Crédito (Saldo = Haber - Debe)
3 Patrimonio Crédito (Saldo = Haber - Debe)
4 Ingreso Crédito (Saldo = Haber - Debe)
5 Gastos Débito (Saldo = Debe - Haber)
6 Costos Débito (Saldo = Debe - Haber)
2. Consulta para Saldo Final por Cuenta
Usa la siguiente expresión en tu consulta (o en una nueva basada en la anterior) para calcular el saldo por cuenta, usando la lógica del primer dígito:
Campo Calculado de Saldo:
Saldo: IIf(
Left([cod_puc], 1) In ("1", "5", "6"),
[SumaDebe] - [SumaHaber],
[SumaHaber] - [SumaDebe]
)3. Saldo Inicial y Final (Por Periodo)
Para obtener un Saldo Final y un Saldo Inicial, necesitarás usar dos consultas separadas (o subconsultas), filtradas por la Fecha_doc_soporte:
Consulta de Saldo Inicial: Filtra los registros con Fecha_doc_soporte menor o igual al día anterior a tu fecha de inicio de periodo.
Consulta de Movimiento del Periodo: Filtra los registros con Fecha_doc_soporte entre tu fecha de inicio y fin de periodo.
Saldo Final: El saldo final será: [Saldo Inicial] + [Movimiento del Periodo].
Sugerencia Avanzada:
La forma más eficiente es crear dos consultas de resumen (una para el saldo inicial acumulado hasta la fecha de corte, y otra para el movimiento del periodo) y luego unirlas usando una consulta de combinación (usando JOIN en SQL o la vista diseño de Access para vincularlas por el cod_puc o su nivel).
Aprovecho para invitarlo a que aprenda PostgreSQL donde puedes llegar más lejos sin las limitaciones de Access.
Disculpa que repetí parte del contenido.
Buen día, gracias por responder, seguí los pasos de la consulta que muy amablemente me brindaste, pero no resuelve mi problema de que me sume por niveles de cuentas contables. Haciendo prueba y error realicé una consulta llamada consulta1, y además separé el código del puc, en clase_puc, el cual me almacenaría el primer dígito, en grupo_puc, el cual me almacenará los dos primeros dígitos, cuenta_puc y subcuenta_puc, para los primeros cuatro y seis dígitos respectivamente el código me suma por los niveles de cuenta, sin embargo el problema es que cuando ingreso el resto de Campos necesarios (Nom_cuenta,Nom_cliente,cod_cliente,fecha_soporte)para la consulta que necesito, me sale error. A continuación te comparto el código:
SELECT Left([Clase_puc],1) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Clase_puc],1);
UNION ALL
SELECT Left([Grupo_puc],2) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Grupo_puc],2);
UNION ALL
SELECT Left([Cuenta_puc],4) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Cuenta_puc],4);
UNION ALL SELECT Left([Subcuenta_puc],6) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Subcuenta_puc],6);
Agradezco de antemano la ayuda que me puedas brindar.
Buen día, agradezco tu interés y respuesta en mi dificultad
Seguí los pasos que me proporcionaste en la explicación sin embargo no me hace la sumatoria del dbi haber por niveles de cuentas contables como la necesito.
A prueba y error realiza una consulta llamada consulta1 y además dividí el código puc en clase_puc que me almacena el primer dígito del cod_puc, grupo_puc que me almacena los dos primeros dígitos del cod_puc y cuenta_puc, subcuenta_puc que me almacenaría los 4 primeros dígitos y los 6 primeros dígitos respectivamente, sin embargo cuando deseo agregar el resto de Campos necesarios para la consulta (nom_cuenta,cod_cliente,nom_cliente,fecha_soporte), me genera error.
A continuación te comparto el código que utilizo.
SELECT Left([Clase_puc],1) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Clase_puc],1);
UNION ALL
SELECT Left([Grupo_puc],2) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Grupo_puc],2);
UNION ALL
SELECT Left([Cuenta_puc],4) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Cuenta_puc],4);
UNION ALL SELECT Left([Subcuenta_puc],6) AS Nivel, Sum([Debe]) AS Sumadebe,
Sum([Haber]) AS Sumahaber,
Sum([Debe]-[Haber]) AS Saldo
FROM Consulta1
GROUP BY Left([Subcuenta_puc],6);
Agradezco de antemano la ayuda que me puedas brindar
El error se debe a una restricción fundamental en las consultas que usan el operador UNION y en las consultas con funciones de agregación (SUM, GROUP BY):
Regla de UNION: Todas las consultas combinadas con UNION deben tener el mismo número de columnas y los tipos de datos de las columnas correspondientes deben ser compatibles.
Regla de GROUP BY: Cuando usas una función de agregación (SUM, COUNT, etc.), cualquier campo que no esté agregado debe estar incluido en la cláusula GROUP BY.
Tu consulta actual solo tiene cuatro campos (Nivel, Sumadebe, Sumahaber, Saldo) en cada sección del UNION.
Solución al Problema de Agregación y Detalle
Para incluir los campos de detalle (nom_cuenta, cod_cliente, etc.) junto con los saldos consolidados, debes entender que no pueden ir directamente en la misma consulta UNION con los niveles del PÚC, porque estos campos de detalle no son agregados y varían para cada transacción, mientras que tu consulta principal está agregando los totales.
La forma correcta de manejar esto es:
Asegurarte de que la consulta original (Consulta1) sea la que contenga todos los campos de detalle. (Parece que ya lo tienes).
Realizar la agregación por cada nivel del PÚC (como lo haces ahora) sin los campos de detalle.
Para obtener el detalle por cada nivel, necesitarías una consulta separada o usar una técnica de JOIN o una subconsulta para ligar el nivel agregado con el nombre correspondiente (si es un nombre de cuenta).
2. Código Propuesto para la Estructura PÚC
Asumiendo que Consulta1 es la fuente de datos que contiene todos los campos, el código de tu consulta está bien para obtener solo la estructura de los saldos por nivel.
Consulta Original (Correcta para Saldo Consolidado)
SELECT Left([cod_puc],1) AS Nivel, Sum([Debe]) AS Sumadebe, Sum([Haber]) AS Sumahaber, Sum([Debe]-[Haber]) AS Saldo FROM Consulta1 GROUP BY Left([cod_puc],1) UNION ALL SELECT Left([cod_puc],2) AS Nivel, Sum([Debe]) AS Sumadebe, Sum([Haber]) AS Sumahaber, Sum([Debe]-[Haber]) AS Saldo FROM Consulta1 GROUP BY Left([cod_puc],2) UNION ALL SELECT Left([cod_puc],4) AS Nivel, Sum([Debe]) AS Sumadebe, Sum([Haber]) AS Sumahaber, Sum([Debe]-[Haber]) AS Saldo FROM Consulta1 GROUP BY Left([cod_puc],4) UNION ALL SELECT Left([cod_puc],6) AS Nivel, Sum([Debe]) AS Sumadebe, Sum([Haber]) AS Sumahaber, Sum([Debe]-[Haber]) AS Saldo FROM Consulta1 GROUP BY Left([cod_puc],6);
NOTA IMPORTANTE:
En el código original usas Left([Clase_puc],1), Left([Grupo_puc],2), etc. Si Consulta1 ya contiene los campos Clase_puc, Grupo_puc, Cuenta_puc, y Subcuenta_puc tal cual (por ejemplo, Clase_puc = "1", Grupo_puc = "11", etc.), deberías usar el campo completo cod_puc y la función Left() para garantizar que la agregación sea correcta.
Si insistes en usar tus campos calculados (Clase_puc, etc.), entonces la forma correcta sería simplemente usar el campo sin la función Left() si ya co
SELECT [Clase_puc] AS Nivel, Sum([Debe]) AS Sumadebe, ... GROUP BY [Clase_puc] UNION ALL SELECT [Grupo_puc] AS Nivel, Sum([Debe]) AS Sumadebe, ... GROUP BY [Grupo_puc] -- Etc.
3. Cómo Agregar el Nombre de la Cuenta (nom_cuenta)
Para añadir el nombre de la cuenta, debes hacer un LEFT JOIN de tu consulta unificada (la que calcula los saldos por nivel) con una tabla de cuentas que contenga el código y el nombre.
Asume que tienes una tabla llamada TablaCuentas con los campos cod_puc y nom_cuenta.
Guarda la consulta anterior (la que obtiene los saldos por nivel) como ConsultaSaldosPuc.
Crea una nueva consulta para unir los nombres:ntienen la longitud correcta:
SELECT S.Nivel, T.nom_cuenta, S.Sumadebe, S.Sumahaber, S.Saldo FROM ConsultaSaldosPuc AS S LEFT JOIN TablaCuentas AS T ON S.Nivel = T.cod_puc ORDER BY S.Nivel;
De esta manera, la consulta ConsultaSaldosPuc te da los saldos consolidados.
El LEFT JOIN busca el nombre (nom_cuenta) correspondiente al Nivel agregado.
Para los niveles intermedios (1, 11, 1105), la TablaCuentas debe contener también sus nombres.
Para los campos de cliente (cod_cliente, nom_cliente, fecha_soporte), estos solo tienen sentido en el nivel de detalle de la transacción (la cuenta de 6 o más dígitos), y no pueden mostrarse en el saldo consolidado de la Clase 1 o Grupo 11. Para eso, solo debes consultar Consulta1 directamente, o filtrar ConsultaSaldosPuc por el nivel más bajo (6 dígitos).