Sort totals by month

[Michael]

I need to have a query that sorts by products received by months for a
report.

I have the following sql and this gives me a total of all the records in the
table by product and then by weight. I want to add the field [del] which is
the date of delivery which is formatted as a short date in the table.

All the help files I have seen speak of using a separate month and year
field to sort by month. Is there a way to search within this query using the
existing data in the [del] field?

thank you
Michael

SELECT [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
Sum([Registro Entrate].PesolordoKG) AS SumOfPesolordoKG
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Fornitore, [Registro Entrate].Prodotto
ORDER BY [Registro Entrate].Prodotto, Sum([Registro Entrate].PesolordoKG);

I tried a few ways and all sort by month, but give me a line for each
delivery instead of a total for each product for the month as in the above


I tried to use =Month([del]) as below but that returned no records.

SELECT [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
Sum([Registro Entrate].PesolordoKG) AS SumOfPesolordoKG, [Registro
Entrate].Del
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
[Registro Entrate].Del
HAVING ((([Registro Entrate].Del)=Month([del])))
ORDER BY [Registro Entrate].Prodotto, Sum([Registro Entrate].PesolordoKG);

 

[Marshall Barton]

Use Format(datefield, "yyyy") instead your datefield
everywhere in the query.

 

[Michael]

Thank you Marshall
Michael

Use Format(datefield, "yyyy") instead your datefield
everywhere in the query.
--
Marsh
MVP [MS Access]

I need to have a query that sorts by products received by months for a
report.

I have the following sql and this gives me a total of all the records in
the
table by product and then by weight. I want to add the field [del] which
is
the date of delivery which is formatted as a short date in the table.

All the help files I have seen speak of using a separate month and year
field to sort by month. Is there a way to search within this query using
the
existing data in the [del] field?


SELECT [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
Sum([Registro Entrate].PesolordoKG) AS SumOfPesolordoKG
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Fornitore, [Registro Entrate].Prodotto
ORDER BY [Registro Entrate].Prodotto, Sum([Registro Entrate].PesolordoKG);

I tried a few ways and all sort by month, but give me a line for each
delivery instead of a total for each product for the month as in the above


I tried to use =Month([del]) as below but that returned no records.

SELECT [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
Sum([Registro Entrate].PesolordoKG) AS SumOfPesolordoKG, [Registro
Entrate].Del
FROM [Registro Entrate]
GROUP BY [Registro Entrate].Fornitore, [Registro Entrate].Prodotto,
[Registro Entrate].Del
HAVING ((([Registro Entrate].Del)=Month([del])))
ORDER BY [Registro Entrate].Prodotto, Sum([Registro Entrate].PesolordoKG);