Total acumulado de una columna en función de otra en Access

Problema

Queremos crear un total acumulado de una columna en función de otra. En nuestro ejemplo calcularemos un total acumulado de los precios en función de categoría. Es decir empezará a sumar desde cero al cambiar de categoría. Por tanto el total al iniciar la categoría es igual al precio de esa fila.

Solución

Empleamos una consulta que incluye una subconsulta.

SELECT T1.IdCategoría, 
 T1.NombreProducto, 
 T1.IdProducto, 
 T1.PrecioUnidad, 
 (SELECT Sum(T2.PrecioUnidad) 
  FROM Productos AS T2
  WHERE  T2.IdCategoría = T1.IdCategoría 
  AND T2.IdProducto <= T1.IdProducto) 
  AS Total_Categoría
 FROM Productos AS T1 

Resultado

El resultado saldrá desordenado.

Será necesario ordenar el resultado de la consulta primero por el Total_Categoría y luego por Categoría. Otra opción es utilizar el siguiente código.

SELECT *
FROM (SELECT T1.IdCategoría, 
T1.NombreProducto, 
T1.IdProducto, 
T1.PrecioUnidad, 
(SELECT Sum(Productos.PrecioUnidad) AS Total   
FROM Productos   
WHERE  Productos.IdCategoría = T1.IdCategoría 
 AND Productos.IdProducto <= T1.IdProducto ) AS Total 
FROM Productos AS T1) AS T2
ORDER BY T2.IdCategoría, Total;

Porcentaje del total de la columna mediante subconsulta en Ms Access

Problema

Queremos calcular el porcentaje del total de la columna mediante subconsulta. Anteriormente vimos como calcular el porcentaje del total de la columna mediante la función DSuma.

Partimos de la siguiente tabla sacada de la anterior entrada.

Solución

En SQL

SELECT Tabla1.x, Tabla1.freq, [freq]/(SELECT Sum(Tabla1.freq) FROM Tabla1) AS prob
FROM Tabla1
GROUP BY Tabla1.x, Tabla1.freq;

En Access

Creamos la siguiente consulta.

1. Añadimos los dos campos x y freq
2. En la ficha Diseño, en el grupo Mostrar u ocultar, clic en Totales (símbolo del sumatorio, sigma).
3. Campo calculado prob con la expresión en la que introducimos la subconsulta:

prob: [freq]/(SELECT Sum(Tabla1.freq) FROM Tabla1)

Resultado

Para mostrar el formato anterior, estándar con 4 decimales, en la hoja de propiedades del campo prob seleccionamos las propiedades mencionadas.

Referencias

• Porcentaje del total de la columna en una consulta de Access
• Ley de Benford en Ms Access
• Calcular el porcentaje del total de la columna en R

Ley de Benford en Ms Access

La ley de Benford, también conocida como la ley del primer dígito, se refiere a la frecuencia de distribución del primer dígito en muchos de los números que aparecen en la vida real. En esta distribución, el 1 aparece con una frecuencia aproximada del 30% mientras que el 9 aparece con una frecuencia menor del 5%. Por primer dígito se refiere al primer dígito no nulo o significativo.

Esta ley se puede aplicar a una gran variedad de fuentes de datos: facturas de electricidad, direcciones de calles, precios de acciones, cifras de población, tasas de mortalidad, longitud de los ríos o constantes físicas y matemáticas. Se ha aplicado en la detección de fraudes en contabilidad, resultados electorales y científicos.

Ejemplo

Queremos calcular la frecuencia relativa del campo Cargo de la tabla Pedidos de la base de datos Neptuno. El resultado final es una tabla con los dígitos, su frecuencia absoluta, el total de registros y la frecuencia porcentual.

Solución 1

En dos pasos:

1. Consulta intermedia (qry_Total): calcula el total de registros de la tabla cuyo último dígito del campo Cargo es mayor que cero.


SELECT COUNT(Pedidos.Cargo) AS Total
FROM Pedidos
WHERE (((Mid([Cargo],1,1))>0));

2. Consulta final (qry_BenfordLaw) que calcula la frecuencia relativa usando el total de la consulta anterior.

SELECT Mid([Cargo],1,1) AS Dígito, 
COUNT(Pedidos.Cargo) AS Frecuencia, 
qry_Total.Total, 
COUNT([Pedidos]![Cargo])/[Total] AS Porcentaje
FROM Pedidos, qry_Total
GROUP BY Mid([Cargo],1,1), qry_Total.Total
HAVING (((Mid([Cargo],1,1))>0));

Solución 2

Creando una subconsulta evitando la consulta intermedia.

SELECT Mid([Cargo],1,1) AS Dígito, 
COUNT(Pedidos.Cargo) AS Frecuencia, 
(select count([Cargo]) FROM Pedidos WHERE (((Mid([Cargo],1,1))>0))) AS Total, 
Count([Pedidos]![Cargo])/[Total] AS Porcentaje
FROM Pedidos
GROUP BY Mid([Cargo],1,1)
HAVING (((Mid([Cargo],1,1))>0));

La subconsulta es idéntica a la consulta intermedia (qry_Total) que realizamos en la solución 1.

(SELECT COUNT([Cargo]) FROM Pedidos WHERE (((Mid([Cargo],1,1))>0))) AS Total

Referencias

• Ley de Benford en R
• Ley de Benford en Excel
• Distribución de frecuencias en Ms Excel