DESPERATE - Count help needed (1 Viewer)

dmcfarland9

New member
Local time
Today, 07:15
Joined
Jul 23, 2018
Messages
6
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

AWF VIP
Local time
Today, 03:15
Joined
Mar 9, 2014
Messages
5,423
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

Well-known member
Local time
Today, 07:15
Joined
Apr 9, 2015
Messages
4,337
Select year(dateFld), count(terms) from table where year(dateFld)=forms!myForm!cboYr and month(dateFld) = forms!myForm!cboMo
 

dmcfarland9

New member
Local time
Today, 07:15
Joined
Jul 23, 2018
Messages
6
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

AWF VIP
Local time
Today, 03:15
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:15
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 03:15
Joined
Mar 9, 2014
Messages
5,423
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

Super Moderator
Staff member
Local time
Today, 22:15
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 11:15
Joined
Jul 26, 2013
Messages
10,354
@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

MVP / VIP
Local time
Today, 11:15
Joined
Jan 14, 2017
Messages
18,186
@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
 

Users who are viewing this thread

Top Bottom