Crear informe a partir de los totales de 3 consultas relacionados por mes
Os agradecería si me pudierais echar una mano con este problema que tengo, me he colapsado y no le encuentro la solución.
Tengo 3 consultas con totales de compras de 3 tablas distintas que se relacionan por Idnombre:
Consulta A Consulta B Consulta C
IdNombre IdNombre IdNombre
Fechaconsulta A Fechaconsulta B Fechaconsulta C
Totalcompras A Totalcompras B Totalcompras C
Idmes A: mes(Fechaconsulta A) Idmes B:mes(Fechaconsulta B) Idmes C: mes(Fechaconsulta C)
Datos:
- El campo Idnombre es común para las 3 consultas. Puede pasar que un mes tenga una compra de la consulta B y no tenga ninguna de la C o de la A. Es decir, no cada mes tengo una compra de cada consulta.
- El campo Idmes lo he añadido porque creía que si ponía un idmes en todas las tablas me serviría para poder relacionar las consultas por fecha cuando hiciera la consulta global, pero no he sabido.
- Totalcompras A y B salen de una fórmula que multiplica cantidad*importe y Total compras C de una suma de dos campos.
Mi objetivo es crear un informe para obtener una relación de nombres con sus totales por mes, es decir:
Septiembre
Id nombre (1) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Id nombre (2) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Id nombre (3) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Octubre
Id nombre (1) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Id nombre (2) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Id nombre (3) …………………… Totalcompras A + Totalcompras B + Totalcompras C
Etcétera
Aunque puede ser que algún mes no tenga compras de A o de B o de C.
2 respuestas
Vamos a ver si me explico, que eso siempre es lo peor. Vamos a suponer que tengo una consulta A basada en la tabla TablaA, construida como
Le añado los campo Año y Fecha sólo para poder ordenarlos, ya que MesAño al ser texto los ordenaría alfabéticamente y Abril/2020, Agosto/2020 irían antes que Enero/2020 De forma que al abrirla me quedaría
Lo mismo con ConsultaB y ConsultaC.
Con éstas tres consultas hago una consulta de Unión llamada Consulta1
De forma que ya tengo unidas las tres consultas
Con ésta consulta hago una consulta llamada ConsultaTotal, con un diseño
De forma, que los agrupo por mes y si algún "cliente" me hubiera comprado más de una vez en el mismo mes me sume sus "compras". Caso del IdNombre 10 y 15
El 10 tenía dos compras en abril, y el 15 en Marzo, pero ya me pone la suma de lo "comprado".
Con esta consulta hago un informe como, por ejemplo
Tanto Año como mes los pongo como visible=No
De forma que al abrir en vista previa de impresión
¡Gracias!
Buenos días Julián te has explicado perfectamente, eres un crack. Me planteé muchas opciones para conseguirlo, pero nunca pensé en la consulta de unión. Funciona perfectamente. Muchas gracias. Hacéis un gran trabajo
Te comento cómo lo haría yo, tal como tienes construidas las consultas, y luego te diré cómo "mejorarlas":
1º/ Crearía una tabla TMeses, con IdMes y NombreMes, y la rellenaría con los números y nombre de los meses.
2º/ Crearía una nueva consulta, seleccionando la ConsultaA y la tabla TMeses
3º/ Crearía una relación entre IDMes de TMeses e IdmesA de Consulta A. A continuación sacas las propiedades de la relación y seleccionas la opción que diga algo parecido a esto: "Mostrar todos los registros de TMeses y solo aquellos de Consulta A que esten relacionados" (ha de ser la segunda o tercera opción, dependiendo de la posición de las tablas/consultas.
4º/ Seleccionas IDMes de TMeses y los campos que quieras de la consulta. De esta forma en el resultado de tu consulta serán 12 registros (uno por mes), independientemente de que ese mes en A haya o no movimiento (si no hay, te aparecerán los datos en blanco para esos campos)
5º/ Ahora no me queda claro lo del campo común en las tres consultas, y tengo mis dudas de si te será mejor añadir a la consulta del punto 2 las consultas B y C y hacer lo mismo con sus relaciones y añadir los campos que necesites y el campo calculado con la suma, usando la función Nz() para convertir los nulos a 0 y poder sumarlos (Nz([Totalcompras A];0) + Nz([Totalcompras B];0) + Nz([Totalcompras C];0)), o bien crear otras dos consultas siguiendo los puntos 2 a 4 y luego unirlas con una consulta de union: SELECT * FROM Consulta1 UNION ALL SELECT * FROM Consulta2 UNION ALL... y luego hacer una consulta de totales para que te agrupe por mes y te haga las sumas.
Tal como tienes diseñadas las consultas, te puedes encontrar con un problema si tus datos pertenecen a varios años y no filtras previemanete las consultas A, B y C, pues con "mes(Fechaconsulta A)" no estás teniendo en cuenta el año, y en el informe enero de 2020 te saldrá con enero 2021...
Para esto tienes varias soluciones: añadir un campo nuevo Año([Fechaconsulta A]), o en vez de tener los campos mes y año, tenerlo todo en uno, por ejemplo: Format([Fechaconsulta A];"yyyymm") o Año([Fechaconsulta A]) & Mes([Fechaconsulta A]).
¡Gracias!
Buenos días Sveinbjorn El Rojo, el tema del año no me afecta, ya que es una base de datos anual.
Antes de preguntaros como hacerlo, intenté hacer algo parecido a lo que tu me has dicho y siempre me daba unos valores de suma muy altos y no conseguí arreglarlo.
He borrado todo lo que tenia antiguo, he seguido tus consejos y me ha dado el resultado.
Hacéis un gran trabajo. muchísimas gracias
De nada, encantado de haberte ayudado.