I have a table with the fields DATE , CODE , ZONE and QUANTITY . I'm trying to get a query that only shows amounts for each area on the most recent date. The problem is that the date does not have to be the same in all the zones and I cannot mount the query correctly, since I can only display the data of the zone whose date is the most recent.
That is, if I have data from the SILO zone of 10/20 and data from the LOAD zone of 10/25, I can only display the data of the LOAD zone of 10/25... And I need the most recent data for each ZONE created .
This is the query I have set up so far. It is mounted on an Access 2016:
SELECT recuentos.FECHA, materiales.descripcion, materiales.codigo, recuentos.ZONA
FROM recuentos INNER JOIN materiales ON recuentos.CODIGO = materiales.codigo
WHERE (((recuentos.FECHA) In (SELECT max(FECHA) FROM recuentos)));
Any ideas or suggestions?
Thanks.
Edit to add more info:
I am assembling the query with a subquery but I am getting a syntax error in the JOIN operation. I try in Access and the word counts remains underlined, at the beginning of the SELECT:
SELECT Vista.ULT, Vista.ZONA, recuentos.CODIGO, materiales.descripcion, recuentos.CANTIDAD
FROM ((SELECT Max(recuentos.FECHA) AS ULT, recuentos.ZONA FROM recuentos GROUP BY recuentos.ZONA) AS Vista INNER JOIN materiales ON recuentos.CODIGO = materiales.codigo)
GROUP BY Vista.ZONA, recuentos.CODIGO, materiales.descripcion, recuentos.CANTIDAD;
Ok, so I found the solution.
The problem was the first field of the SELECT which had to be: Max(Vista.ULT) AS ULT_FE instead of Vista.ULT .