Reporte Por Grupo de Material con Conexion ADO Excel-Access

Enigma25 · el 23 de abril

Buen día

La presente es para dar un nuevo aporte el cual está creado con la intención de ser usado para motivos de aprendizaje y que de igual manera también será para consultas ya que tengo algunas preguntas sobre "Consultar Dos Tablas Access a la Vez". No obstante, queda a la orden para que lo usen cuando gusten y si tienen alguna idea más que pueda servir, bienvenida sea.

El archivo está diseñado para crear una conexión ADO (activex data objects) con Access.

Ejemplo de Ejecución:

Crea la conexión
Coloca los encabezados por medio de Código
Abre la Tabla Registro Agrupa por Materiales y Descripción que están en la tabla Registro y los pega en la Hoja3 y cierra el Recordset. "Volcar"
Abre Nuevamente la tabla Registro "Open". En este Caso por Rangos de Fechas, el Campo “Field” lo define por el Número de Mes para luego separarlo por las columnas correspondientes (Meses que serian el total de salidas por mes). Suma el total de Entradas de los Materiales y el Resultado los coloca en la Columna “Entrada” en la hoja3 del Archivo Excel 2007.
Luego hace las otras operaciones como; Sumar el total de Salidas por mes y el resultado lo Muestra en la Columna “Salidas”
Cierra el Recordset y la conexión.

Quizás sea un poco engorroso por la cantidad de código empleado, de hecho pensé que si solo se abriera la tabla “Registro” solo una vez y se hicieran todo lo que hace, será excelente pero el problema radica cuando se agrupa por material. Cuando es Así, me sale un error que dice ”Ha intentado Ejecutar una consulta que no incluye la expresión Especifica “Cantidad” como parte de una función de agregado.” Esto es porque incluyo otro campo mas que no está agrupado a mi entender y por ello el mensaje.

En si lo que buscaba es que primero, me agrupara los materiales que se encuentren en la tabla Registro y los pegara en la hoja del libro Excel y con esa información, trabajar con lo demás datos como las entradas y/o salidas. Pero aun no encuentro la forma en como hacerlo. Imagino que podría ser abriendo dos tablas a la vez....

Solo espero encontrar la manera y prometo exponerlo al culminarlo.

Mis respetos para todos.

Record.zip

ioyama · el 25 de abril

Hola Enigma25

Gracias por el aporte

Un saludo desde Vitoria

Enigma25 · el 25 de abril

Buen dia ioyama

Al contrario, mil gracias a usted por su comentario. Eso en verdad motiva y contribuye a seguir estudiando y aportando a esta grandiosa comunidad.

...Y a todos por igual mis respetos.

Antoni · el 25 de abril

Hola Enigma25:

Como veo que has quedado impresionado con ADO y SQL te adjunto una modificación del procedimiento Consulta.

En el adjunto verás que se pueden obtener los resultado de la consulta solamente con una sentencia Select.

Saludos

PruebadeConsulta.zip

Enigma25 · el 26 de abril

Buen día

Señor Macro Antonio

Simplemente Excelente su código e increíble la forma en que dedujo y modificó para hacerlo más eficiente. Es mucho más rápido que el que tengo y estoy seguro que el tema de la velocidad en respuesta es lo que más interesa para una consulta con gran cantidad de datos a requerir.

Solo me quedará estudiarlo con más detenimiento ya que la sintaxis del código es muy distinta a las que estoy acostumbrado a practicar con lo poco que he conocido.

Solo me restaría saber y si está a su disposición, con una breve explicación del cómo funciona el código, porque siendo sincero jejeje, lo que entendí de la estructura es que;

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=1,Cantidad,0))," Suma por condición según el tipo de movimiento y el mes, pero esta parte de : ,Cantidad,0 no la entiendí.
De igual manera estas dos líneas en las que existe una separación como de cuatro caracteres;
sql = sql & " From Registro WHERE Fecha BETWEEN #" & Inicio & "# And #" & Final & "#" & ""
sql = sql & " Group By Codigo,Descripcion"
también era el porqué el código tiene varias fases;

sql = "Select Codigo,Descripcion,"

sql = sql & "Sum(IIf(Movimiento='ENTRADA',Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=1,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=2,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=3,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=4,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=5,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=6,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=7,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=8,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=9,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=10,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=11,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA' and Month(Fecha)=12,Cantidad,0)),"

sql = sql & "Sum(IIf(Movimiento='SALIDA',Cantidad,0))"

sql = sql & " From Registro WHERE Fecha BETWEEN #" & Inicio & "# And #" & Final & "#" & ""

sql = sql & " Group By Codigo,Descripcion"

'Ejecutamos la sentencia select

Rs.Open sql, Cnn, adOpenKeyset, adLockOptimistic, adCmdText

Ofrezco mis disculpas por las preguntas ya que tal como usted lo ha dicho……. si, en verdad estoy muy impresionado con ADO y SQL pues para su servidor, tiene un mundo lleno de posibilidades para el manejo de bases de datos y los códigos son muy dinámicos que me dejan con la boca abierta por así decirlo.

El tema de conexiones es nuevo para mi pero gracias a lo mucho que practiqué con Excel y vba, puedo comprender ADO mucho mejor.

Antes veía los códigos y me parecían de otro planeta jejeje.

P.D.Nunca he trabajado con visual basic.3 o visual basic.4 etc. Todo es por Excel2007 (VBA)

Mis respetos.

Antoni · el 27 de abril

Hola Enigma:

Una sentencia select recupera información por columnas.

Una columna de una sentencia select puede ser cualquier dato, desde una columna de una tabla a un texto fijo, pasando por operaciones aritméticas, condiciones, funciones de cadena, funciones de agregado y cualquier combinación de todas ellas incluso otra sentencia select.

En nuestro caso lo que hacemos es condicionar una función de agregado (Sum) en función del contenido de la columna Movimiento usando la sentencia IIf como lo hacemos en visual basic.

IIf(condición, valor si verdadero, valor si falso), o sea, exactamente igual que la función SI de excel.

Ejemplo de sentencia select válida:

Select 'A', Col1*Col2 / 3, LEFT(Col3, 2), Max(Col4), Min(Col4) From Tabla

De hecho la sentencia select de sql es un lenguaje de programación en si misma.

Como recoger información de 2 tablas unidas por una columna común:

SELECT Material.Codigo, Material.Familia, Material.UMV, Registro.Fecha, Registro.Cantidad

FROM Material INNER JOIN Registro ON Registro.Codigo=Material.Codigo"

Intenta ejecutar esta sentencia sql con tu base de datos.

Fíjate que parte de la información de la tabla Registro es redundante, si ya existe en la tabla Material, para que arrastrarla en la tabla Registro, solo ocupa espacio inútil y perjudica el rendimiento, y de paso, si modificas algún dato de la tabla Material, no queda cambiado en la tabla Registro.

Enigma25 · el 29 de abril

Buen dia

Simplemente Excelente

Mil gracias por el tiempo que se tomó para aclararlo Maestro. Estoy seguro que con la practica e investigar mas, podré realizar mis trabajos de ahora en adelante con access con las mayor seguridad posible y minimizando los códigos irrelevantes y demás.

De hecho estoy pensando seriamente en aprender mas de su código, ya que es muy eficiente y rapido para incluirle los promedios de salidas según la cantidad de meses para que luego y de acuerdo al inventario, pueda estimar los meses de inventario del stock.

Un ejemplo de lo que trato de decirle es representar cada mes con movimiento de salidas como (1) y de acuerdo a ello, promediar el total de salidas por esos meses representados en uno.

Yo ampliaré el reporte ya codificado que aporte anteriormente para que y si está a su disposición, pueda ver lo que trato de proyectar. Claro que este conservará los códigos anteriores por ser los que mas he usado para tal fin.

Mis respetos Señor Macro Antonio.

Enigma25 · el 7 de mayo

Buen día

Señor Macro Antonio

Desafortunadamente no se como agregar un nuevo archivo asi que tuve que modificar el adjunto original del post#1 pero mantiene lo mismo, solo que una modificacion del codigo conservando el codigo que usted gentilmente me proporcionó.

Tal como lo había comentado anteriormente,…Estaba por mostrarle además del aporte como tal, una modificación del mismo en el cual agregaba lo que era el promedio de salidas de materiales por las columnas de Fechas y que de acuerdo a ese promedio estimar con el stock restante, los meses de inventario de cada uno de los materiales.

Todo esto de acuerdo al rango de fecha de la consulta que en si sería una manera de estimar los meses de inventario lo cual sería una ventaja al querer ver el comportamiento de los productos.

Además ofrece un indicador más de stock en nuestro almacén y la necesidad de comprar y/o producir según el tipo de negocio que maneje la persona que tenga el negocio etc.

Por supuesto no es exacto, ya que se emplea un promedio mensual de movimiento (SALIDA) del cual entre los meses puede haber variaciones a considerar como las ventas que se generaron en un mes específico, no obstante, para los reportes, es una gran ayuda y de análisis.

Espero que pueda evaluarlo.

Mis respetos

Enigma25 · el 8 de febrero

Señor Macro Antonio

Como siempre mis respetos para usted. Estoy buscando nuevamente este aporte con la intención de refrescar La idea sobre las consultas internas Inner Joins ya que actualmente estoy trabajando en un proyecto el cual y las circunstancias me lo permite, lo aportaré con la intención de que lo usen, aprendan, Etc. Es un archivo para llevar un control de inventario muy parecido, de hecho muy parecido al que había aportado en Ideas y Aportes llamado Stock.2.0. La razón de ello es emplear el manejador de bases de datos Access el cual es toda una maravilla para gran información.

Este aporte usa algunos códigos que usted gentilmente ha proporcionado como el de crear códigos automáticos en una consulta hecha en el foro así como otros mas.

Es mas, los registros de las operaciones los he reducido en su totalidad basándome en lo dicho por usted y cito:

Por macro Antonio

"Fíjate que parte de la información de la tabla Registro es redundante, si ya existe en la tabla Material, para que arrastrarla en la tabla Registro, solo ocupa espacio inútil y perjudica el rendimiento, y de paso, si modificas algún dato de la tabla Material, no queda cambiado en la tabla Registro."

Pronto expondré mi duda sobre la consulta de Registros que empleo para ver el documento como tal de una operación(Entrada o Salida) tal como aparece en el archivo de Archivo para el Control de Stock.2.0 y seria un honor contar con su conocimientos para conseguir la solución idónea.

Como siempre, mi respetos para usted y mi agradecimiento por su generosa colaboración.