Collecting statisctics in db with VBA (1 Viewer)

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
[SOLVED]Collecting statisctics in db with VBA

Hi all,

I have another question about the db I'm working on.
This time, it's about collecting and displaying statistics.

I would like to collect several stats (off of a query page?) and have them displayed on a form. And it's all based on two dates - start date and end date - kind of like monthly stats.

Right now, on my Stats page, when I enter the start date and end date, and click the 'Results' button, a report pops up and shows the number of containers examined within the date range selected.

How can I have the stats displayed on a form - the number of containers examined, the type of exams, what terminals, container type, etc....

I tried several ways to use the DCount() function, but am getting nowhere.

Any suggestions would be greatly appreciated. Attached is my sample db.
On the stats page, if you enter Jan.1/20 and Jan. 4/20 as start and end dates, it will display 4 containers on a popup report. It's that info I would like to show it on the stats page.

Thanks, Andrew
 

Attachments

  • AW - test-stats.zip
    178.1 KB · Views: 124
Last edited:

June7

AWF VIP
Local time
Today, 06:18
Joined
Mar 9, 2014
Messages
5,425
Why do you have aggregate query as report RecordSource but don't use any aggregate functions?

What's wrong with DLookup's on form? What does 'getting nowhere' mean - error, wrong result, nothing happens?

Which form, which fields should coincide with report data?
 

vba_php

Forum Troll
Local time
Today, 09:18
Joined
Oct 6, 2019
Messages
2,884
Why do you have aggregate query as report RecordSource but don't use any aggregate functions?
June, I didn't understand that either, but I did a test an ran into the error shown in the image...

Andrew,

you say:
On the stats page, if you enter Jan.1/20 and Jan. 4/20 as start and end dates, it will display 4 containers on a popup report. It's that info I would like to show it on the stats page.
and you are using:
Code:
=DCount("[tblMDEMexams]![File  complete]","tblMDEMexams","[File complete] = No")
in the control source of *Text27* next to *container report*, but your query is filtered on *"[File complete] = Yes"*. furthermore, you are not specifying the date range frrom the 2 textboxes in that DCOUNT() function as criteria either.
 

Attachments

  • maybe_group_by_is_needed.jpg
    maybe_group_by_is_needed.jpg
    94 KB · Views: 103

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
June, I didn't understand that either, but I did a test an ran into the error shown in the image...

Andrew,

you say:and you are using:
Code:
=DCount("[tblMDEMexams]![File  complete]","tblMDEMexams","[File complete] = No")
in the control source of *Text27* next to *container report*, but your query is filtered on *"[File complete] = Yes"*. furthermore, you are not specifying the date range frrom the 2 textboxes in that DCOUNT() function as criteria either.

Hi Adam and June,

Sorry, I didn't leave better notes, it was late at night .......

The whole stats page is based on -qryMDEMexamsStats-
Right now, when I enter the start date and the end date on the 'STATS' tab, a report pops up, based on the date range, showing how many total containers were examined. There are also containers that haven't been examined yet, hence why the -[File completed] = Yes-. I only want to see the completed files.

How do I link the results shown in that report to the various textboxes (using various filters, etc) on the stats page when I click the 'Results' button? Right now, when I link any field to any of the textbox using DCount(), I get either the 'Name???' or '###Error' shown in that textfield. Obviously, I'm not inputting the correct syntax and parameters, being a newb and all ...

What I envisioned was when I enter a date range - ex Jan. 1/2020 to Jan. 31/2020, it would show the total of containers examined, while having that info broken down further, ie: who made the referral, what size and type the containers were, what terminal was the container at, etc ....

Thanks, guys, for your help. I know it can be done somehow, but I have no idea how.

A
 

vba_php

Forum Troll
Local time
Today, 09:18
Joined
Oct 6, 2019
Messages
2,884
The whole stats page is based on -qryMDEMexamsStats-
I don't think so. when clicking on the *stats* tab and checking the recordsource for the subform control that appears, it is listed as *qryMDEMexamsCompleted*. you must be referring to the domain functions you have in the many textboxes on that subform, or the report that pops up after clicking *results*.
I only want to see the completed files.
if that's what you want, then that's what you've currently got! don't records that are returned to you that have the spec [File Completed] = TRUE satisfy that request? :confused:
What I envisioned was when I enter a date range - ex Jan. 1/2020 to Jan. 31/2020, it would show the total of containers examined
to start your journey in that regard the first thing you need to do is eliminate this part of the sql statement in the query that appears at the very end:
Code:
AND ((tblMDEMexams.[File complete])=Yes));
 

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
I don't think so. when clicking on the *stats* tab and checking the recordsource for the subform control that appears, it is listed as *qryMDEMexamsCompleted*. you must be referring to the domain functions you have in the many textboxes on that subform, or the report that pops up after clicking *results*.if that's what you want, then that's what you've currently got! don't records that are returned to you that have the spec [File Completed] = TRUE satisfy that request? :confused: to start your journey in that regard the first thing you need to do is eliminate this part of the sql statement in the query that appears at the very end:
Code:
AND ((tblMDEMexams.[File complete])=Yes));

I'll have to rename the 2 queries - '..Stats' and '...Completed'. I think I'm getting myself so mixed up I'm chasing my own tail :)

And, yes, I only see the completed files. So that part is good. When I press the 'Results' button, a report pops up with all the completed containers.

What I would like to do is eliminate the report altogether (only done at this time to see how things work), and replace it with all the various textboxes being populated with the results of my query. Basically, when I press the 'Results' button (after I input the start date and end date), all the numbers show up on the 'Stats form, not on a report. How do I get the ....DCount()... to show up in the textboxes.
 
Last edited:

vba_php

Forum Troll
Local time
Today, 09:18
Joined
Oct 6, 2019
Messages
2,884
Basically, when I press the 'Results' button (after I input the start date and end date), all the numbers show up on the 'Stats form, not on a report. How do I get the ....DCount()... to show up in the textboxes.
this is what you can do to accomplish that:

  • ditch the *embedded macro* behind the button, and write this instead behind the *click* event of *results*:
    Code:
    Private Sub  cmdResults_Click()
        Me.txtStatsContExam = DCount("id", "qryMDEMexamsStats")
        Me.txtRefByBCEF = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'BCEF'")
        Me.txtRefByTCEF = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'TCEF'")
        Me.txtRefByTCU = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'TSU'")
        Me.Requery
    End Sub
that's just a starter. you can do the rest yourself I would assume. The rest is just replicating what I've done. I don't even know if you need the *requery* line in there, but that's just to show how I obsess over covering all possible problems during the first test run. :)
 

Attachments

  • AW - test-stats - illustration.zip
    199.4 KB · Views: 119

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
this is what you can do to accomplish that:

  • ditch the *embedded macro* behind the button, and write this instead behind the *click* event of *results*:
    Code:
    Private Sub  cmdResults_Click()
        Me.txtStatsContExam = DCount("id", "qryMDEMexamsStats")
        Me.txtRefByBCEF = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'BCEF'")
        Me.txtRefByTCEF = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'TCEF'")
        Me.txtRefByTCU = DCount("id", "qryMDEMexamsStats", "[RefBy] = 'TSU'")
        Me.Requery
    End Sub
that's just a starter. you can do the rest yourself I would assume. The rest is just replicating what I've done. I don't even know if you need the *requery* line in there, but that's just to show how I obsess over covering all possible problems during the first test run. :)

Thank you, Adam. I appreciate your help. I wasn't sure how to populate all the fields with a click of a button. I'm still so new to all this .....

A
 

vba_php

Forum Troll
Local time
Today, 09:18
Joined
Oct 6, 2019
Messages
2,884
Thank you, Adam. I appreciate your help. I wasn't sure how to populate all the fields with a click of a button. I'm still so new to all this .....

A
that's why this place exists! :) I'm about to head out for an interesting engagement with another newbie to the software industry, so you might see me disappear for a while. If you need further assistance, I'm sure the other guys are around.
 

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
that's why this place exists! :) I'm about to head out for an interesting engagement with another newbie to the software industry, so you might see me disappear for a while. If you need further assistance, I'm sure the other guys are around.

Have a good one :)
I'll be busy myself for a while writing code :)
 

June7

AWF VIP
Local time
Today, 06:18
Joined
Mar 9, 2014
Messages
5,425
Instead of user clicking Results button, make the action automatic with either:

1. expressions in textboxes and code in each date textbox AfterUpdate event to refresh form

2. expressions in a procedure that can be called by each date textbox AfterUpdate event to set values of textboxes

Make the action conditional on both date textboxes having an input and that end date is later than start date.
 
Last edited:

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
Instead of user clicking Results button, make the action automatic with either:

1. expressions in textboxes and code in each date textbox AfterUpdate event to refresh form

2. expressions in a procedure that can be called by each date textbox AfterUpdate event to set values of textboxes

Make the action conditional on both date textboxes having an input and that end date is later than start date.

Hi June7,

vba_php helped me with a starting point.
Being a newb to VBA coding, I like to use the KISS principle, which his example is for me.
I was thinking initially of somehow using the 'AfterUpdate', but it is still all new to me.

Lots of reading up ahead for me :)

A
 

June7

AWF VIP
Local time
Today, 06:18
Joined
Mar 9, 2014
Messages
5,425
I would automate. User could change entered dates and forget to press button and mistakenly reference previous calcs.

The more 'user friendly', the more code.

Turns out if either date is not input, calcs just show 0. Use ValidationRule and ValidationText properties of txtEnd to validate date is later than txtStart.

The expressions don't change if you want in VBA. Build a sub in the form module:

Sub CalcStats()
'place DLookup expressions here
End Sub

Call CalcStats from AfterUpdate procedures:

Sub txtStart_AfterUpdate()
CalcStats
End Sub

Sub txtEnd_AfterUpdate()
CalcStats
End Sub

Or put DLookups in textboxes and each AfterUpdate event would simply have: Me.Refresh

Why bind form to a table/query and not reference its fields? Overuse of domain aggregate functions can slow performance. SQL aggregate functions should be faster. I expect most (if not all) of the specialized queries could be eliminated by using Count() and IIf() expressions in textboxes. So bind form to tblMDEMexams and expressions in textboxes like:

=Count(IIf([File complete]=No And [Terminal]="Centerm",[ID],Null))
=Count(IIf([Terminal]="Centerm",[ID],Null))

However, I am having difficulty getting dynamic date range criteria to work - just shows #Error. Rats!

Also, instead of a bunch of virtually identical specialized reports, build 1 report and apply filter criteria when opening.

A command button on report in PrintPreview will not work. However, since you display reports in ReportReview this may not be an issue.

Advise not to use spaces nor punctuation/special characters in naming convention.

Why do you have 3 tables with identical structure?

Attached is your db with some modifications for you to consider.
 

Attachments

  • AW - test-stats.zip
    105.6 KB · Views: 112
Last edited:

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
I would automate. User could change entered dates and forget to press button and mistakenly reference previous calcs.

The more 'user friendly', the more code.

Turns out if either date is not input, calcs just show 0. Use ValidationRule and ValidationText properties of txtEnd to validate date is later than txtStart.

The expressions don't change if you want in VBA. Build a sub in the form module:

Sub CalcStats()
'place DLookup expressions here
End Sub

Call CalcStats from AfterUpdate procedures:

Sub txtStart_AfterUpdate()
CalcStats
End Sub

Sub txtEnd_AfterUpdate()
CalcStats
End Sub

Or put DLookups in textboxes and each AfterUpdate event would simply have: Me.Refresh

Why bind form to a table/query and not reference its fields? Overuse of domain aggregate functions can slow performance. SQL aggregate functions should be faster. I expect most (if not all) of the specialized queries could be eliminated by using Count() and IIf() expressions in textboxes. So bind form to tblMDEMexams and expressions in textboxes like:

=Count(IIf([File complete]=No And [Terminal]="Centerm",[ID],Null))
=Count(IIf([Terminal]="Centerm",[ID],Null))

However, I am having difficulty getting dynamic date range criteria to work - just shows #Error. Rats!

Also, instead of a bunch of virtually identical specialized reports, build 1 report and apply filter criteria when opening.

A command button on report in PrintPreview will not work. However, since you display reports in ReportReview this may not be an issue.

Advise not to use spaces nor punctuation/special characters in naming convention.

Why do you have 3 tables with identical structure?

Attached is your db with some modifications for you to consider.

Hi June7,

thanks for your suggestions. I will have a look at the file and learn from it.

Like I said, I'm fairly new to creating a large db, although I've dabbled with Access in the past.
I wish I found out about the naming conventions before I started. Some of the field names are now coming back to bite me in the a#s. I just might rename all the fields and hope I can remember where I used them :)

Same with all the tables and forms and queries. A few times, as I was working on my db, I thought I would need same version of a table or query but with different expressions or parameters. What I know now is that one table/query usually suffices, seeing how one can use different parameters. And I ended up chasing my own tail, trying to remember what each table/query did or didn't do :)

So, in short, I'm learning tons from places like Access World and folks like yourself :) So, thanks for all your help. I really appreciate it :)

A
 

hogdriver53

Registered User.
Local time
Today, 07:18
Joined
Jan 1, 2020
Messages
21
I would automate. User could change entered dates and forget to press button and mistakenly reference previous calcs.

The more 'user friendly', the more code.

Turns out if either date is not input, calcs just show 0. Use ValidationRule and ValidationText properties of txtEnd to validate date is later than txtStart.

The expressions don't change if you want in VBA. Build a sub in the form module:

Sub CalcStats()
'place DLookup expressions here
End Sub

Call CalcStats from AfterUpdate procedures:

Sub txtStart_AfterUpdate()
CalcStats
End Sub

Sub txtEnd_AfterUpdate()
CalcStats
End Sub

Or put DLookups in textboxes and each AfterUpdate event would simply have: Me.Refresh

Why bind form to a table/query and not reference its fields? Overuse of domain aggregate functions can slow performance. SQL aggregate functions should be faster. I expect most (if not all) of the specialized queries could be eliminated by using Count() and IIf() expressions in textboxes. So bind form to tblMDEMexams and expressions in textboxes like:

=Count(IIf([File complete]=No And [Terminal]="Centerm",[ID],Null))
=Count(IIf([Terminal]="Centerm",[ID],Null))

However, I am having difficulty getting dynamic date range criteria to work - just shows #Error. Rats!

Also, instead of a bunch of virtually identical specialized reports, build 1 report and apply filter criteria when opening.

A command button on report in PrintPreview will not work. However, since you display reports in ReportReview this may not be an issue.

Advise not to use spaces nor punctuation/special characters in naming convention.

Why do you have 3 tables with identical structure?

Attached is your db with some modifications for you to consider.

Hi June7,

had a look at the db you kindly sent back for me to check.

The 2 tabs and this part of the db are a part of a much larger database that I'm working on.
It involves entering a lot of data, looking for and updating data, etc. Thus the reason for a bunch of different tables/queries.
The sample I posted was scrubbed off some sensitive data that I didn't want to post for several reasons.

Either way, thanks for looking at my question and replying back. I appreciate you taking time to help. I'll remember your suggestions and will try to use them in the future :)

A
 

vba_php

Forum Troll
Local time
Today, 09:18
Joined
Oct 6, 2019
Messages
2,884
Yah. I know now :)
The biggest one I need to stop doing is the spaces between words in field names.
what you'll find from a lot of professionals is that they often replace spaces (whitespace) with underscores (_). It's also a common practice in SEO operations on websites to replace the spaces in the URL's article name or <h1> (header) value of the webpage with hyphens (-) and use the result as the SEO-based URL address. this is great example of that very technique:

https://cacm.acm.org/news/240626-py...ires-saying-its-been-an-amazing-ride/fulltext
 

Users who are viewing this thread

Top Bottom