to get sum between 2 dates

[Andrew Fernandez]

i did but still months are not in order.

 

[Galaxiom]

Order on the original date field. Not the derived field with yyyy-mmm because this will be ordered alphanumerically.

 

[isladogs]

i did but still months are not in order.

It would help if you showed us the query design & the results you got

The fact that the dates aren't in order will be because of the date formatting used
Did you convert dates to mm/dd/yyyy format as instructed by Galaxiom?

 

[Andrew Fernandez]

i intend having a format yyyy-mmm because it is a monthly basis not on a daily basis. So i want it on every heading to appear only the month and the year. if i change it to mm/dd/yyyy which i already did...more columns were added than with fewer columns in yyyy-mmm.

 

[Galaxiom]

So i want it on every heading to appear only the month and the year. if i change it to mm/dd/yyyy which i already did...more columns were added than with fewer columns in yyyy-mmm.

The displayed data and the ordering can be separate expressions.

Order By the date formatted to yyyymm.

As ridders said, it would be a lot easier if you posted SQL text instead of images.

 

[Andrew Fernandez]

here's the SQL text format

View attachment SQLformat.txt

 

[Andrew Fernandez]

The displayed data and the ordering can be separate expressions.

Order By the date formatted to yyyymm.

As ridders said, it would be a lot easier if you posted SQL text instead of images.

i see no problem with the total value displayed per month for each item. for example if there are two values recorded in a month, the figure displayed or the result of the total value for that month is correct.
months is not arranged also when i tried mm/dd/yyy

 

[isladogs]

When you format dates the result is a string.
So if you use the slightly odd format mmm/yyyy the output will be e.g. Feb/2017 then Jan/2017 because F is before J in the alphabet

P.S. thanks for posting the code as a text file this time.
Even better, use the code tags (# button in toolbar) and place directly in the message like this

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum(Backup.pcssent) AS SumOfpcssent
SELECT Backup.matlcodes, Backup.description, Backup.itemunits, Sum(Backup.pcssent) AS Total, Avg(Backup.pcssent) AS Average
FROM Backup
WHERE (((Format([datedeliverd],"mmm/yyyy"))>=[Start Date] And (Format([datedeliverd],"mmm/yyyy"))<=[End Date]))
GROUP BY Backup.matlcodes, Backup.description, Backup.itemunits
ORDER BY Backup.matlcodes, Format([datedeliverd],"mmm/yyyy") DESC 
PIVOT Format([datedeliverd],"mmm/yyyy");

 

[Andrew Fernandez]

yes it is alphabetically arranged. sorry ridders. next time i will use the # button to post my code. So i should rather use mm/dd/yyyy or mm/yyyy?