Query Between Dates but with Count Grouped by Model Number (1 Viewer)

[Adam McReynolds]

I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping. Any help would but great!

 

[pbaldy]

In design view, change Group By to Where on the date field.

 

[Adam McReynolds]

In design view, change Group By to Where on the date field.

Thanks for the response. That didn't work for me though. Let me give more info to help clarify what I am trying to do. I have a completion date field that I want to show query data between 2 dates. the criteria is:

Between [Enter Start Date] And [Enter End Date]

I have a Model Number field that I have selected to Count in the total row. And I have that field again in the query as Group By to show the model number.

When I change Group By to Where on the completion date field it says that it is not part of the aggregate function. If I keep it as Group By it gives the count by still groups it by completion date.

Thanks for the help.

 

[pr2-eugin]

Adam, is it possible for you to show the entire query? This can be done by switching the view to SQL in the bottom right corner..

 

[Adam McReynolds]

Adam, is it possible for you to show the entire query? This can be done by switching the view to SQL in the bottom right corner..

SELECT TBL_REPAIRS.APU_Model_Number, Count(TBL_REPAIRS.APU_Model_Number) AS CountOfAPU_Model_Number, TBL_REPAIRS.CompletionDate
FROM TBL_REPAIRS
GROUP BY TBL_REPAIRS.APU_Model_Number, TBL_REPAIRS.CompletionDate
HAVING (((TBL_REPAIRS.CompletionDate) Between [Enter Start Date] And [Enter End Date]));

If I take out completion date on Group By it gives error message that it is not part of aggregate function.
Thanks.

 

[pbaldy]

You still have the date field in both the SELECT and GROUP BY clauses. Making the change I mentioned should have taken it out of both (unless the field is repeated).

 

[Adam McReynolds]

You still have the date field in both the SELECT and GROUP BY clauses. Making the change I mentioned should have taken it out of both (unless the field is repeated).

OK, I think I got it.

SELECT TBL_REPAIRS.APU_Model_Number, Count(TBL_REPAIRS.APU_Model_Number) AS CountOfAPU_Model_Number
FROM TBL_REPAIRS
WHERE (((TBL_REPAIRS.CompletionDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY TBL_REPAIRS.APU_Model_Number;

Seems to be working fine. Thanks for the help!

 

[pbaldy]

No problem. Using the WHERE clause will also be more efficient than the HAVING clause. Access often gets that wrong.

 

[Brianwarnock]

No problem. Using the WHERE clause will also be more efficient than the HAVING clause. Access often gets that wrong.

I don't think Access gets it wrong it does what it's told, users have to realise that applying criteria after grouping can be different to applying it before the grouping and select the Where/Having as required.

Brian

 

[pbaldy]

I don't think Access gets it wrong it does what it's told, users have to realise that applying criteria after grouping can be different to applying it before the grouping and select the Where/Having as required.

Brian

I disagree. I've often started a query in design view and made a simple "between" criteria as in this thread, and when I look at SQL view Access has made it a HAVING clause instead of a WHERE clause. In design view you have no option to choose either; Access makes its best guess, and in my experience it doesn't always get it right.

 

[Brianwarnock]

Ok my experience is nothing like as great as yours but in that limited experience I have found that if I put the criteria in a column that has other than a Where clause selected Access creates a Having clause, to force a Where clause you drag the field into the Grid again and select Where.

To me that is the user in control and logical.

Brian

 

[pbaldy]

That sounds like what I've experienced as well, but in my view it should only create a HAVING clause if you put the criteria on an aggregated field (Sum, Count, etc). If I want to have a criteria on the date field and group on it as well, it should still be a WHERE clause. Access will create a HAVING clause. Perhaps I haven't thought it out far enough.

 

[Brianwarnock]

Ah! I see, as there can never be a case when it makes sense for a Having clause for a date you want it to treat a date field as a special case and treat it differently to other data types, i will go for that, but a niggle at the back of my mind wonders if the compiler knows that it is a date field at that point or just another dbl field.

Brian

 

[pbaldy]

Maybe it's just the types of data I've worked with, I don't see a difference grouping on a date field vs a numeric field. I've got data with a Long Integer driver field. I'm often grouping on driver but also have a criteria on it. To me that should still be a WHERE clause.

Sorry Adam, we've turned your thread into a philosophical discussion.

 

[Brianwarnock]

Yes sorry Adam but Paul and I are going to continue to disagree, applying criteria after a grouping and before are different and maybe required , a simple example is that you may want to exclude from a grouping records with an account records of less than $10 or you may want to exclude from the final list accounts whose sum is less than $1000, the first requires a Where the second a Having. Access lets you make that distinction.

For anyone reading this who doesn't know how to create the Where you Drag the money into the design grid twice once for the sum and once for the Where, for the second you put the criteria in the same column as the sum and thus create a Having.

Brian