Totals Query - Return Data Set between Two Dates (1 Viewer)

DaveCollins181 · Jun 9, 2015

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 · Jun 9, 2015

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 · Jun 9, 2015

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 · Jun 9, 2015

So try a HAVING or WHERE clause.

CJ_London · Jun 9, 2015

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 · Jun 9, 2015

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 · Jun 9, 2015

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 · Jun 9, 2015

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

Totals Query - Return Data Set between Two Dates (1 Viewer)

DaveCollins181

Registered User.

Attachments

pr2-eugin

Super Moderator

DaveCollins181

Registered User.

pr2-eugin

Super Moderator

CJ_London

Super Moderator

DaveCollins181

Registered User.

Attachments

CJ_London

Super Moderator

DaveCollins181

Registered User.

Similar threads

Users who are viewing this thread