Re: [MS_AccessPros] Overall Average on a report

 

Jim-

More like: Huh?  How does making the control Visible enable it?  And why are you using LostFocus?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Apr 20, 2017, at 19:49, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I used the distinct query and it worked. Thank You

But this creates a new issue

There is a parameter on the report source query. When I click the button the report parameter dialogue box appears. I enter the parameter and the report works. But the Average shows an #Error.

So I put a text box on the form for the parameter and it works great. No issues. But there are 10 reports with parameters. So I replicated the fix on several of the reports. But now I am faced with putting multiple parameter text boxes on the form. Well I knew what you were going to say, I can hear it in my head, "Jim, really?" So I put one text box as txtParameter and added Parameter to the Tag property and then tried to enable the buttons on after update for the text box to enable the buttons. But my code is not working to enable the buttons. Below is the code. nothing happens. What is wrong with it?

Thank You

Jim

Private Sub txtEnterParameter_LostFocus()

    Dim ctrl As Control
For Each ctrl In Me.Controls
    If ctrl.Tag = "Parameter" Then
        ctrl.Visible = True
    End If
Next
End Sub

Jim Wagner

---

On Tue Apr 18 2017 09:10:00 GMT-0700 (US Mountain Standard Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim-

Yeah.  What you really need is Count(DISTINCT [EmployeeID]), but Access doesn't support that.  You could try to kludge it with DCount on the underlying table, but if you're filtering your report, you would need to apply the same filter.  Also consider creating a new query that returns the DISTINCT employee IDs, then count that with DCount.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Apr 18, 2017, at 17:22, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I am still getting the 7 employees

Jim Wagner

---

On ‎Monday‎, ‎April‎ ‎17‎, ‎2017‎ ‎10‎:‎47‎:‎54‎ ‎PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim-

Try adding a control in the footer:

=Count([EmployeeID])

(Or use whatever is the unique field that identifies the employee.)

That should give you 5, and you can use that to calculate your average.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Apr 17, 2017, at 19:38, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello all,

I have a salary report that shows the salary for employees by job code. It works great but someone just discovered that the Average, Min, and Max calculations are incorrect. I guess that it has been this way for years.

The salary report has Avg, Min, and max calculations on the footer. So I was able to add a field to the report and get a sum of the [Annl Rt] over all and that is correct, but I am unable to get a correct avg, min or max because there are some employees that are on split accounts, like 75 and 25. The calculations should be calculating by the number of employees. right now there are 5 employees on the report but because of the 2 employees on split accounts it shows as 7 employees, which makes the average way off.

I have a field named txtSumOfAnnlRt that has the control source of =Sum([Annl Rt]) and it has Running sum "Over all" in the property sheet. This is the correct number.

So I tried using the txtSumOfAnnlRt field for the Avg and others but it gives me a value of $0 with running sum over all.

If I use =Avg([Annl Rt]) and Running Sum "Over all" it gives me the avg of the 7 employees which it should.

what do I do to get a group total?

Thank You

Jim Wagner

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___