to get sum between 2 dates

[Andrew Fernandez]

How am I going to formulate to get the SUM between two dates in a query?
I already created a start date and end date as parameters but the TOTAL within range is incorrect.
Aside from getting the right formula for the sum, I am also trying to get the average?

please check attachments:





Need Help. thanks

 

[ashleedawg]

DateDiff("d",[date1],[date2]) will give you the difference between [date1] and [date2], in Days. Check out other options here.

With that in place you can get averages, sums, etc. For averages, paste this into SQL view:

SELECT Count([date1]) AS CountOfRecords, Avg(DateDiff("d",[date1],[date2])) AS AvgDaysBetween
FROM tblMyTable;

(You'll need to replace the field names & table name with yours.)

 

[Andrew Fernandez]

i think im getting closer to this one. i'll let you know whatever comes out.

 

[Ranman256]

in image1, "" is not a correct format.
format([field],"yymmdd") is

 

[Andrew Fernandez]

Ranman256 it is a total value per month then the overall total value from months within range. so the my format is yyyy-mmm.

 

[isladogs]

Just a thought:
Are you aware that the crosstab query wizard will include the totals by default
For example:

 

[Andrew Fernandez]

i mean my format should be yyyy-mmm.

 

[Andrew Fernandez]

thank you all guys...i'll get you back later once im done here. so good to have all your contributions posted here.

 

[Andrew Fernandez]

i'm back and still i'm stuck! i am attaching 2 images. please help me out with this guys. thanks

 

[Andrew Fernandez]

sorry ridders i wasnt able to attach the images from my first post. i got a new post with images attached now. pls check., thanks

 

[Andrew Fernandez]

Please check images attached...





need help....pls.

 

[Galaxiom]

Format to yyyy-mmm will put the month in a string as an abbreviated name. Greater than, less then and Between will then be processed alphanumerically putting Feb before Jan etc.

 

[Andrew Fernandez]

ok galaxiom...i'll try...

 

[Andrew Fernandez]

i think i need to resolve first in getting the average and sum between dates or within range then if by chance everything's resolved...than i could perhaps arrange the months or date in descending or ascending order. but right now i must put a total and average columns to get the value between dates.

 

[Galaxiom]

i think i need to resolve first in getting the average and sum between dates or within range

Yes. The Where clause is the problem.

Format the start and end dates as #mm/dd/yyyy#. This makes them compatible for comparisons with the raw date values in the table

 

[Andrew Fernandez]

i finally got the sum between dates but im still working how to get the average between dates

 

[Andrew Fernandez]

i already got the sum between two dates but not the average.
please check the image attached.

 

[Galaxiom]

Avg() gives the average of whatever you put in the parameter. You are averaging the number of days between the startdate and enddate. Clearly that is not going to give you a sensible result. Similarly the Count().

 

[Andrew Fernandez]

yup i know. I just tried ashleedawg's post..but it's not working anyway. Well Galaxiom you know what?.... everything's OK now...finally! both Average and Sum all Ok now! but there is one thing.....how can arrange (sort) the date?

 

[isladogs]

In Design view, select Ascending or Descending for your date field.

For future reference, please post query SQL using code tags so others can edit it. We can't alter the SQL in an image!