Cosulta a multiples tablas por fecha maxima Access

[wingsmarcus]

Hola,

Últimamente he estado trabajando en una consulta que utilizo en un programa, pero no he podido hacer que me funcione correctamente(no me saca ningún registro), quisiera su ayuda es muy urgente.

Aquí les dejo una imagen con las tablas de mi base de datos:



Lo que quiero prácticamente es hacer esta consulta:

SELECT m.codigo AS Codigo, m.nombre AS Nombre, p.proveedor AS Proveedor, u.unidad_medida AS Unidad, m.existencia AS Existencia, e.ingreso AS Ingreso, s.egreso AS Egreso
FROM materia AS m, proveedores AS p, unidades AS u, mov_entrada AS e, mov_salida AS s
WHERE m.id_proveedores = p.id_proveedor and m.id_unidad = u.id_unidad and m.id_materia = e.id_materia and s.id_materia = m.id_materia and e.fecha = 15/01/2012 and s.fecha = 15/01/2012

Pero no se como mostrar la fecha máxima de las tablas mov_entrada y mov_salida. donde las ultimas fechas las ingreso con un DTPicker de visual basic 6.0... en resumen me tendría que salir algo así:

Codigo | Nombre | Proveedor | Unidad | Existencia | Ingreso | Egreso |
5940 chocolate Willy W. onza/s 90 10 20
5940 Glucosa Mercado onza/s 120 80 30

Así sucesivamente, pero las columnas 'ingreso' y 'egreso' deben dar el valor correspondiente a la ultima fecha en que fueron insertadas. es decir en este ejemplo '15/01/2012'.

Todo esto al final se mostrará en un DataGrid de VB6.0

[quimfv]

Código SQL:

SELECT m.codigo AS Codigo, 
             m.nombre AS Nombre, 
             p.proveedor AS Proveedor, 
             u.unidad_medida AS Unidad, 
             MAX(e.fecha) AS MaxEntrada,
             MAX(s.fecha) AS MaxSalida,
             SUM(e.ingreso) AS TotalIngreso, 
             SUM(s.egreso) AS TotalEgreso,
             m.existencia AS Existencia
FROM (((materia AS m INNER JOIN proveedores AS p 
                 ON m.id_proveedores = p.id_proveedor)
            INNER JOIN unidades AS u ON m.id_unidad = u.id_unidad)
            INNER JOIN mov_entrada ON m.id_materia = me.id_materia)
            INNER JOIN mov_salida AS s ON s.id_materia = m.id_materia
GROUP BY m.codigo, 
             m.nombre, 
             p.proveedor, 
             u.unidad_medida, 
             m.existencia;
 

Esto te da la suma de lo ingresado, la suma de egresado, y la ultima fecha de ingreso y egreso para cada materia, proveedor y unidad de medida...

Era esto?

En mi opinion tienes un error de diseño, puesto que existencia deberia ser el resultado de SUM(e.ingreso)-SUM(s.egreso) AS Existencia y eliminar ese campo de la tabla materias....

Código SQL:

SELECT m.codigo AS Codigo, 
             m.nombre AS Nombre, 
             p.proveedor AS Proveedor, 
             u.unidad_medida AS Unidad, 
             m.existencia AS Existencia,
             MAX(e.fecha) AS MaxEntrada,
             MAX(s.fecha) AS MaxSalida,
             SUM(e.ingreso) AS TotalIngreso, 
             SUM(s.egreso) AS TotalEgreso,
             SUM(e.ingreso) - SUM(s.egreso) AS Existencia
FROM (((materia AS m INNER JOIN proveedores AS p 
                 ON m.id_proveedores = p.id_proveedor)
            INNER JOIN unidades AS u ON m.id_unidad = u.id_unidad)
            INNER JOIN mov_entrada AS e  ON m.id_materia = e.id_materia)
            INNER JOIN mov_salida AS s ON s.id_materia = m.id_materia
GROUP BY m.codigo, 
             m.nombre, 
             p.proveedor, 
             u.unidad_medida, 
             m.existencia;
 

Ultima entrada de cada materia

Código SQL:

SELECT * FROM mov_entrada AS e1
INNER JOIN (SELECT e2.id_materia, MAX(e2.fecha) AS MAXEntrada FROM mov_entrada AS e2 GROUP BY e2.id_materia)
ON e1.id_materia=e2.id_materia AND e1.fecha=e2.MaxEntrada;
 

idem salidas

Código SQL:

SELECT * FROM mov_salida AS s1
INNER JOIN (SELECT s2.id_materia, MAX(s2.fecha) AS MAXSalida FROM mov_salida AS s2 GROUP BY s2.id_materia)
ON s1.id_materia=s2.id_materia AND s1.fecha=s2.MaxSalida;
 

luego

Código SQL:

SELECT m.codigo AS Codigo, 
             m.nombre AS Nombre, 
             p.proveedor AS Proveedor, 
             u.unidad_medida AS Unidad, 
             m.existencia AS Existencia,
             e.ingreso AS Ingreso, 
             s.egreso AS Egreso
FROM (((materia AS m INNER JOIN proveedores AS p 
                 ON m.id_proveedores = p.id_proveedor)
            INNER JOIN unidades AS u 
                        ON m.id_unidad = u.id_unidad)
            INNER JOIN (SELECT * FROM mov_entrada AS e1
                                    INNER JOIN (SELECT e2.id_materia, MAX(e2.fecha) AS MAXEntrada 
                                               FROM mov_entrada AS e2 GROUP BY e2.id_materia) 
                                       ON e1.id_materia=e2.id_materia AND e1.fecha=e2.MaxEntrada) AS e  
                        ON m.id_materia = e.id_materia)
            INNER JOIN (SELECT * FROM mov_salida AS s1
                                    INNER JOIN (SELECT s2.id_materia, MAX(s2.fecha) AS MAXSalida 
                                               FROM mov_salida AS s2 GROUP BY s2.id_materia)
                                        ON s1.id_materia=s2.id_materia AND s1.fecha=s2.MaxSalida) AS s 
                        ON s.id_materia = m.id_materia;
 

ahora ... creo...

Pero insisto el campo existencia te taerá problemas, es un error que incumple las leyes basicas de normalizacion de una base de datos.

[wingsmarcus]

Muchas gracias, me ha funcionado la consulta a la perfeccion, te debo una