Posts Topics Forums Images
Search videos from message boards Videos Search messages from microblogs Microblogs Search messages from imdb.com Imdb Search messages from yuku.com Yuku Search messages from lefora.com (free forums) Lefora
My account: Login | Sign Up
Loading... 

Thread: Weighted Average Function by subgroup
Started 1 month, 2 weeks ago by AhrenL
I have VBA weighted average custom functions that work great on entire tables. I'm wondering if there is an object that defines the portion of the table segregated by groups setup in the detail portion of a form? Here's the wtgavg function I use for whole tables. Thanks! Exp = Numbers to average Crit = Weighting Dmn = Table/Query name Code: Public Function WtgAvg(Exp ...
Site: dBforums - Database Support Community  dBforums - Database Support Community - site profile
Forum: Microsoft Access  Microsoft Access - forum profile
Total authors: 5 authors
Total thread posts: 16 posts
Thread activity: no new posts during last week
Domain info for: dbforums.com

Other posts in this thread:

NTC replied 1 month, 2 weeks ago
there is nothing defined as a 'group' within a form. it is within reports. in forms; you would have to add a field to the underlying table and put in a value that identified which group a record is with.....and then modify your code to accommodate this grouping method.
Size: 287 bytes
Customize:  Customize "<b>Reply 1</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

pootle flump replied 1 month, 2 weeks ago
Does this give the same result as your function? Code: SELECT SUM(Exp * Crit) / SUM(Crit) AS WtgAvg FROM myTable Obviously you need to replace your table and column names, and coerce them to doubles if needs be. If this works then getting to what you want is trivial.
Size: 559 bytes
Customize:  Customize "<b>Reply 2</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

AhrenL replied 1 month, 2 weeks ago
NTC - Sorry about the form/report confusion. I'm an excel guy still getting my feet wet with Access. Pootle - I'm a baby when it comes to SQL; so if I put that SQL code into a textbox in a group or detail reports area it will only use the value subset of the group/detail? I'll give it a try. Thanks!
Size: 318 bytes
Customize:  Customize "<b>Reply 3</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

pootle flump replied 1 month, 2 weeks ago
No Open a new query window. Go to SQL view. Paste in the SQL and make the edits you need to suit your table. Run. If the result is correct then we move on the next stages. Ignore forms for now.
Size: 336 bytes
Customize:  Customize "<b>Reply 4</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

AhrenL replied 1 month, 2 weeks ago
Okay. That works for the whole table.. Do we use a GROUP BY statement now? How do you run a SQL statement in a text box? Thanks for the help!
Size: 141 bytes
Customize:  Customize "<b>Reply 5</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

pootle flump replied 1 month, 2 weeks ago
Quote: Originally Posted by AhrenL Do we use a GROUP BY statement now? Spot on - you are one step ahead of me! Quote: Originally Posted by AhrenL How do you run a SQL statement in a text box? But now you are too many steps ahead ;o)...
Size: 1,786 bytes
Customize:  Customize "<b>Reply 6</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

pootle flump gauche replied 1 month, 2 weeks ago
Quote: Originally Posted by AhrenL Do we use a GROUP BY statement now? Spot on - you are one step ahead of me! Quote: Originally Posted by AhrenL How do you run a SQL statement in a text box? But now you are too many steps ahead ;o)...
Size: 1,786 bytes
Customize:  Customize "<b>Reply 7</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

AhrenL replied 1 month, 2 weeks ago
Yes. I can get it to display the wtgavg by Grouping. Code: SELECT SUM([QRM OAD] * [QRM Dirty Market Value]) / SUM( [QRM Dirty Market Value]) AS WtgAvg FROM [Agency CMO Fixed] GROUP BY [Curr Intent], [Collat Generic], [NWAC];
Size: 530 bytes
Customize:  Customize "<b>Reply 8</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

AhrenL Registered User replied 1 month, 2 weeks ago
Yes. I can get it to display the wtgavg by Grouping. Code: SELECT SUM([QRM OAD] * [QRM Dirty Market Value]) / SUM( [QRM Dirty Market Value]) AS WtgAvg FROM [Agency CMO Fixed] GROUP BY [Curr Intent], [Collat Generic], [NWAC];
Size: 530 bytes
Customize:  Customize "<b>Reply 9</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

pootle flump replied 1 month, 2 weeks ago
Good stuff. Now our problem. We can get this information, but putting it in to the form is the tricky bit. Is your form read only or do users edit the data?
Size: 156 bytes
Customize:  Customize "<b>Reply 10</b>: Weighted Average Function by subgroup :: Microsoft Access :: dBforums - Database Support Community"

 

Top contributing authors

Name
Posts
AhrenL
6
user's latest post:
Weighted Average Function by...
Published (2009-11-16 09:44:00)
Sorry.. I really wasn't clear (Though it was totally clear in my head when I replied to NTC). Not a complete waste of time as I've definitely learned more about SQL, and I'll have to do this in a form sooner than later in any case. That's working great however. Sorry for the extra trouble.
pootle flump
5
user's latest post:
Weighted Average Function by...
Published (2009-11-16 03:52:00)
In that case we have wasted our time. Quote: Originally Posted by NTC there is nothing defined as a 'group' within a form. it is within reports. NTC made the very point days ago. This is trivial in reports. All the hoops we have been jumping through are due to the &quot;limitations&quot; of forms. Details like whether or not you are using forms or reports make all the difference in the world. Take the bit out of the select...
pootle flump gauche
2
user's latest post:
Weighted Average Function by...
Published (2009-11-12 09:41:00)
Good stuff. Now our problem. We can get this information, but putting it in to the form is the tricky bit. Is your form read only or do users edit the data?
AhrenL Registered User
2
user's latest post:
Weighted Average Function by...
Published (2009-11-13 11:18:00)
Sorry, never got an email that you replied.. It's actually a report not a form, so that should make it easier. I need the data to appear at the top of each subgroup. Thanks!!!
NTC
1
user's latest post:
Weighted Average Function by...
Published (2009-11-11 09:16:00)
there is nothing defined as a 'group' within a form. it is within reports. in forms; you would have to add a field to the underlying table and put in a value that identified which group a record is with.....and then modify your code to accommodate this grouping method.

Related threads on "dBforums - Database Support Community":

How Calculating Weighted Average In Query  dBforums - Database Support Community - site profile Microsoft Access - forum profile How Calculating Weighted Average In Query
How Calculating Weighted Average In Query

Related threads on other sites:

Weighted Average Function  MrExcel Message Board - site profile Excel Questions - forum profile Weighted Average Function
Weighted Average Function
How come the weighted average of percentage change is...  Education & Reference - Yahoo! Answers - site profile Education & Reference - forum profile How come the weighted average of percentage change is different from change in weighted average? - Yahoo! Answers
How come the weighted average of percentage change is different from change in weighted average? - Yahoo! Answers
Function: weighted average  OpenOffice.org Forum at OOoForum.org - site profile OpenOffice.org Calc - forum profile Function: weighted average
Function: weighted average
Weighted Average - Copy Function  Excel Help Forum - site profile Excel Worksheet Functions - forum profile Weighted Average - Copy Function
Weighted Average - Copy Function
Weighted three year average function  MrExcel Message Board - site profile Excel Questions - forum profile Weighted three year average function
Weighted three year average function
Average or Weighted Average  SHRM - site profile General HR - forum profile Average or Weighted Average
Average or Weighted Average
Average plug-in : weighted average of any number of...  Doom9s Forum - site profile Avisynth Usage - forum profile Average plug-in : weighted average of any number of clips; fast (500 FPS on2 clips)
Average plug-in : weighted average of any number of clips; fast (500 FPS on2 clips)
Community - Calculating Weighted average on Total Cost...  Salesforce.com Community - site profile Custom Formula Field Discussion - forum profile Community - Calculating Weighted average on Total Cost field - Formulas & Validation Rules Discussion - Force.com Discussion Boards
Community - Calculating Weighted average on Total Cost field - Formulas & Validation Rules Discussion - Force.com Discussion Boards
Complex Matrix Subtotals - Straight Average of a Weighted...  MSDN Forums - site profile SQL Server Reporting Services - forum profile Complex Matrix Subtotals - Straight Average of a Weighted Average - MSDN Forums
Complex Matrix Subtotals - Straight Average of a Weighted Average - MSDN Forums
Simple Average+Match to Weighted Average  MrExcel Message Board - site profile Excel Questions - forum profile Simple Average+Match to Weighted Average
Simple Average+Match to Weighted Average
Thread profile page for "Weighted Average Function by subgroup" on http://www.dbforums.com. This report page is a snippet summary view from a single thread "Weighted Average Function by subgroup", located on the Message Board at http://www.dbforums.com. This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
 1   2   Next »