DAvg and DSum Query

[danielle.EWAIG]

Hi,

I am currently creating a form with queries attached to command buttons. The queries are there to populate fields in my underlying table, so I can ultimately pull a final report from all the updated data.

I am using queries to recreate the SUMIFS and AVERAGEIFS functions from Excel, however am having trouble with the code (due to my lack of experience).

My current AVERAGEIFS code (WIP) is as follows:

Private Sub ReportRun_CommandButton_Click()
CurrentDb.Execute (IIf(" MyTable.MonthofCancellationsCount < & 1", " UPDATE MyTable " & "SET AverageEarnedPortion = & 1" & "WHERE MyTable.ProductCode = '" & Me.txtProductCode & "'", " UPDATE MyTable " & "SET AverageEarnedPortion = #" & DAvg(EarnedPortion, MyTable, "MyTable.ProductCode = '" & Me.txtProductCode & "'") & "#" & "WHERE MyTable.ProductCode = '" & Me.txtProductCode & "'"))
 
End Sub

Definitions/Details:
MonthofCancellationsCount = number field in table, will come from SUMIFS translation
AverageEarnedPortion = number field in table, shown as percentage
MyTable = underlying table of data
ProductCode = text field in table
txtProductCode = text box on form, showing user-chosen Product Code (via combo box)
EarnedPortion = number field in table, shown as percentage


I want to be able to Average (and eventually Sum) by Product Code and by Sales Month at the same time. I havent been able to find code example for this so far. I want the end report/query that I run to return all sorts of different data, averaged or totalled by Sales Month, under the specific Product Code chosen by the user on my form.

The 'IIF' part of the above query is there to ensure that when Cancellation Count for the Sales Month equals zero, then the Average Earned Portion returns as "100%".

I may have overcomplicated the above code, and I know I haven't even tackled the 'average by month' part of the query yet. This part stumps me, as I want the Average query to run and return on all records where the Sales Month and Product Code match that of the current record's.

I figure that if I can understand how to build/format the AVERAGEIFS translation, I should be able to work out the SUMIFS function myself.


Thank you for any help/suggestions offered.

 

[MarkK]

Generally I wouldn't store processed data. I think of a database, at the table level anyway, as a historical record of the facts; a record of the raw data. As events occur, you record the dimensions of those events that matter to your purpose.

Then let's say you develop some processes that you use to summarize your data, and then you save your summaries. Here's the problem: How do you know, three months from now, if your previously saved summaries are still accurate? The only way to know for sure is to recalculate them. But if you recalculate them each time you need them, to make sure they are correct, don't save them.

Generally try to write queries that process your data on-demand. Save data raw, process it at retrieval time, immediately, on-demand, using queries. Note that your queries can query other queries, and so you can do very sophisticated and very fast processing of huge amounts of data, without saving anything in intermediate steps.

 

[Bilbo_Baggins_Esq]

Maybe this might help, maybe not, but if you add a reference to Excel in your project, you can call Excel Functions with "Excel.WorkbookFunction"...