Problema con consulta de access

[llozada58]

Hola a todos, soy novato en access y tengo un problema con una tabla con los siguientes campos:

Material, Description, Plant, GRVolume, ActUntPrice, StandPrice, %-Var, PrUnit, PurchValue, BookedVar, X-RateVar, AdjPriceVar


Donde Material, es un codigo unico para cierto material, Description, descripcion breve del material, Plant, la planta que solicita dicho material, GRVolume, volumen de dicho material, etc.

El problema esta en que para efectos de resumen, hay materiales que estan en varias plantas y se quiere contabilizar solo una vez, esto se hace mas o menos asi:

y despues del proceso quedaria asi:

lo que se hace es para cada valor repetido de Material tomar el valor maximo de GRVolume y a ese renglon sumar las cantidades de las columnas en amarillo y dejar intactas las de las columnas en azul y solo dejar un renglon por Material.

Es posible hacer esto con consultas de access, las imagenes solo son una porcion de la tabla, hay mas valores repetidos y lo puse asi para explicar, de antemano muchas gracias por la ayuda que me puedan proporcionar.

 

[Greg Truby]

Hola Llozada,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sí, se puede hacer más o menos lo que quiere con Access. Se hace un Query de resumen. No sé cuál versión de Access tiene pero en 2010 después de crear el Query en la lengüita de Query Tools / Design se hace un cliq sobre la Sigma grande. Y en la línea que aparece, se selecciona “agrupar por” para el número de material y la función de resumen para los demás campos. Para los como “description” hay una operadora que saca el primero que sale.<o></o>
Si se ve el Query en forma SQL se ver algo como:<o></o>

SELECT 
tblTest.[Material], First(tblTest.[Desc]) AS FirstOfDesc, 
First(tblTest.[Plant]) AS FirstOfPlant, 
Max(tblTest.[GRVol]) AS MaxOfGRVol, 
Sum(tblTest.[PrecUnit]) AS SumOfPrecUnit, 
Sum(tblTest.[Purch Val]) AS [SumOfPurch Val], 
Sum(tblTest.[Booked Vale]) AS [SumOfBooked Vale]<o:p></o:p>
FROM tblTest<o:p></o:p>
GROUP BY tblTest.[Material];

 

[llozada58]

Muchas gracias por tu respuesta Greg, el problema con ese query es que me sigue mostrando 2 registros para ese valor, el objetivo es que solo muestre un registro por cada material y sume el valor de el volumen, pero que deje el valor de plant que tiene el mayor volumen

 

[Greg Truby]

¿Cuál versión de Excel y Access está usando?

 

[llozada58]

Access 2000 y excel 2007

 

[Greg Truby]

...pero que deje el valor de plant que tiene el mayor volumen

Bueno, eso que dijo no sé cómo hacer con SQL. No digo que no es posible, ni sé. Pero sí le puedo decir cómo hacerlo con Excel. Y favor sepa que hay varios senderos abiertos que llegarán a soluciones correctas. Él que tomaría yo es así:

Es bastante fácil importar datos de Access a Excel 2007. Vaya a la lengüita de “Data” y haga la importación a una hoja nueva, mejor que sea vinculado para poder actualizar los datos fácilmente cuando haya cambios en la base de datos.

Una vez que tenga los datos ya importados, yo haría una tabla dinámica (pivot table) y organizarlo cómo usted quiere con las varias columnas de resumen (las que usted encabezó con amarillo). Al crear la tabla, deje que suma todo y ya una vez que tenga todos los campos sumados, haz un cliq-derecho para los donde se necesita el máximo y cambie la función de resumir a máximo.

Una vez que tenga la tabla dinámica así como quiere con los datos de resumen, a la derecha, usa fórmulas de VLookup() o Index(Match()) para sacar los datos que usted tiene encabezadas con gris. Es cierto que la secuencia no es la misma, pero es mucho más fácil mantener y actualizar vez tras vez rápido y ligero. Otras soluciones, que conservarán la secuencia serían más difíciles actualizar cuando la tabla en la base de datos cambia.

 

[llozada58]

Muchisimas gracias Greg, el problema aqui es que necesito crear una tabla en access para hacer mas calculos con ella, este paso de eliminacion de duplicados es intermedio, muchas gracias por tu ayuda, saludos.

 

[Greg Truby]

Bueno, la cosa es que no tengo Access 2000, solo uso 2007 y 2010. Pero bien, paso por paso entonces. Primeramente, vemos si podemos hacer un Query de resumen sencillo en Access, agrupando por número de material y sumando solo un campo numerico.

En Access vaya a la vista de SQL y haga algo parecido a esto:

SELECT tblTest.[Material], 
Sum(tblTest.[PrecUnit]) AS SumOfPrecUnit
FROM tblTest<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
GROUP BY tblTest.[Material];

Obviamente cambiando los nombres de campos y tabla para los de los suyos.

 

[llozada58]

Ya hice eso, de hecho, ya saque el valor maximo del GR Volume, y la suma del GR Volume en este query:

SELECT TableData_PPV_Monthly.Material, Max(TableData_PPV_Monthly.[GR Volume]) AS [MaxOfGR Volume], Sum(TableData_PPV_Monthly.[GR Volume]) AS [SumOfGR Volume]
FROM TableData_PPV_Monthly
GROUP BY TableData_PPV_Monthly.Material
HAVING (((TableData_PPV_Monthly.Material) Not Like "806*"))
ORDER BY TableData_PPV_Monthly.Material, Max(TableData_PPV_Monthly.[GR Volume]) DESC;

la tabla se llama TableData_PPV_Monthly, se quitaron algunos materiales que no son necesarios y que empiezan con 806.

 

[Greg Truby]

Ahora añade una expresión como:

First(TableData_PPV_Monthly.Material) & "|" & Max(TableData_PPV_Monthly.[GR Volume]) AS Expression1

al Query.

Después haga una segunda query (¿consulta?) parecido al siguiente (no cambié el nombre de la table, pero sí debe de entender).

SELECT tblTest.Plant, [Material] & "|" & [GRVol] AS Expression2
FROM tblTest;

Y ahora usted puede hacer una tercera Query (¿consulta?) uñiendo las dos primeras queries creo...

SELECT qryTest1.*, qryTest2.Plant
FROM qryTest2 INNER JOIN qryTest1 ON qryTest2.Expression2=[qryTest1].Expression1;