Totals Query - Return Data Set between Two Dates

[DaveCollins181]

Hi All, I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed. Can anyone offer a solution? Thanks David.

 

[pr2-eugin]

I am not following you. It seems to return exactly what you asked for. Maybe not what you wanted. Could you show the SQL?

 

[DaveCollins181]

Hi Paul, yes the data is showing exactly what I need. However, only want to show the data for certain months EG: from 01-06-2015 to 31-07-2015. I dont want to show all records in the database. Hope this makes sense? Regards David.

 

[pr2-eugin]

So try a HAVING or WHERE clause.

 

[CJ_London]

add another column for duedate, change the 'group by' to 'where' and in the criteria put

between startdate and enddate

replace startdate and enddate with either hard coded dates or reference to form controls

don't forget that access works on us style dates i.e. mm/dd/yyyy

 

[DaveCollins181]

Hi Paul & CJ, I have added the DueDate to the next column and have added the Where clause. This has given the exact result which I am looking for. Thank you both for your help. Regards David.

 

[CJ_London]

you might find your query is more efficient if you change groupby to where for jobactive and move the criteria for jobreference to another 'where' column like duedate.

reason is 'where's are processed first, limiting the amount of data to be grouped. 'Having's (where you have a criteria on the group by) are processed after all data is received and grouped - so you will have grouped all job references and then filtered them (although I expect someone will say the query optimiser recognises this anyway). 'Having's are normally used for non group by columns - e.g. count, sum, etc - so you can find for example where count(duedates)>10

 

[DaveCollins181]

Thanks CJ, I have changed these from Group to Where. Also thanks for the advice on the where clause as I haven't used it before so you explanation will assist when I am making future queries. Regards David