DESPERATE - Count help needed

dmcfarland9 · Aug 3, 2018

This is what I am doing: I have a query with names, term dates, term months, term months and years on it. I would like to have a form with a combo box that lists the years, and a combo box that lists the month that would count only those records in the query and return a number.

For example:
CboYear=2018
#ofTerms in 2018: 1111
CboMonth=June
#ofTerms in June 2018: 2222

The # of terms in 2018 would count the number of records in the term year column that are equal to 2018. The # of terms in June 2018 would count the records that contained June 2018 in the term months and years column.

I know there is an easy solution but I have been working on this for DAYS. SOS.:banghead:

June7 · Aug 3, 2018

How can June have double the terms for the year? Provide sample data and example of desired output. If you want to provide db for analysis follow instructions at bottom of my post.

Ranman256 · Aug 3, 2018

Select year(dateFld), count(terms) from table where year(dateFld)=forms!myForm!cboYr and month(dateFld) = forms!myForm!cboMo

dmcfarland9 · Aug 3, 2018

June7 said:
How can June have double the terms for the year? Provide sample data and example of desired output. If you want to provide db for analysis follow instructions at bottom of my post.

Those numbers were meant to be placeholders. Apologies for the confusion.

Table Name:TermsByYear2018
Field Name: TermMonth
Criteria: Last Month

I would like for a textbox to show a count of the above criteria based on the selection of combobox (cbomonth).

June7 · Aug 3, 2018

And sorry for lack of instructions about posting attachments, was confused with another forum site. Have added instructions below.

A textbox cannot have a SELECT statement. It can have expression with domain aggregate function (DCount, DSum, DAvg, DLookup) - perhaps that is what you need.

Or build a report to show this summary data. Use report Sorting & Grouping features with aggregate calcs. Report allow display of detail as well as summary data.

Galaxiom · Oct 31, 2018

Ranman256 said:
Select year(dateFld), count(terms) from table where year(dateFld)=forms!myForm!cboYr and month(dateFld) = forms!myForm!cboMo

I really do wish people would stop advising this kind of horrifically inefficient SQL.

That query requires two functions to be run against EVERY record before the Select can be applied. The engine cannot use an index on the date field. You won't notice during development with a small number of records but it will progressively become slower as the number of records increases.

It should be constructed like this:

Code:

Select count(terms) 
from table 
WHERE dateFld BETWEEN DateSerial(forms!myForm!cboYr, forms!myForm!cboMo, 1) 
              AND DateSerial(forms!myForm!cboYr, forms!myForm!cboMo + 1, 0)

Understanding the difference between these queries is vitally important.

If you don't see why then research SARGable queries.

June7 · Oct 31, 2018

Would just leaving the Year(dateFld) out of the SELECT be as efficient?

SELECT Count(terms) FROM table WHERE Year(dateFld)=Forms!myForm!cboYr AND Month(dateFld) = Forms!myForm!cboMo

Galaxiom · Oct 31, 2018

June7 said:
Would just leaving the Year(dateFld) out of the SELECT be as efficient?

I left it out because it would require a Group By to include it and the values would be the same anyway.

SELECT Count(terms) FROM table WHERE Year(dateFld)=Forms!myForm!cboYr AND Month(dateFld) = Forms!myForm!cboMo

It avoids the Group but the problem is still only being able to select after applying functions to every record.

Also note this difference in aggregates. The first one is what the Access query designer encourages a novice to build. The second is what the query should be.

Code:

SELECT field1, Sum(field2) 
FROM table 
GROUP BY field1
HAVING field 1 = something

Code:

SELECT field1, Sum(field2) 
FROM table 
WHERE field 1 = something
GROUP BY field1

The difference is that the WHERE is applied before the GROUP BY while the HAVING is applied after. The first query will group all the records then only return the Having. The second query selects only the "something" records and the Group becomes trivial.

Minty · Oct 31, 2018

@Galaxiom or anyone else -

I'm absolutely sure that in the early days of Access the query designer would by default use Query 2 design above, but recently seems to be hell bent on using Query 1 design these days. Has this behaviour changed , or am I imagining it??

isladogs · Nov 1, 2018

@Galaxiom (& everyone else)
I've done some follow up speed tests comparing the relative speed of WHERE vs HAVING. The results are not what I was expecting. See https://www.access-programmers.co.uk/forums/showthread.php?t=302225

@Minty
I don't think its an MS policy here.
The outcome depends on whether you :
a) apply filter criteria before grouping in the query designer => HAVING
b) group then apply filter criteria as new columns => WHERE

Either way, the test results may possibly affect how you decide to do this in future

DESPERATE - Count help needed

dmcfarland9

New member

June7

AWF VIP

Ranman256

Well-known member

dmcfarland9

New member

June7

AWF VIP

Galaxiom

Super Moderator

June7

AWF VIP

Galaxiom

Super Moderator

Minty

AWF VIP

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread