Help with avg() function in Access

[overdog1979]

Hi,

I have the following query

SELECT tblSalesStatistics.strDbName AS [Database], tblSalesStatistics.strPanelCode AS PanelCode, tblDataType.strTypeCode AS Type, [tblSalesStatistics.strMFYear] & "/" & [tblSalesStatistics.strMFMonth] AS TimePeriod, DateDiff("d",[tblPeriodEndDates.dtmPeriodEndDate],[tblSalesStatistics.dtmEstLiveDate]) AS [Planned DAP], DateDiff("d",[tblPeriodEndDates.dtmPeriodEndDate],[tblSalesStatistics.dtmLiveDate]) AS [Actual DAP], [Actual DAP]-[Planned DAP] AS DaysLate, IIf([DaysLate]<0,0,[DaysLate]) AS DaysLateNorm, Left([txtCauseCode],3) AS Cause
FROM tblPeriodEndDates INNER JOIN (tblDataType INNER JOIN tblSalesStatistics ON tblDataType.intDSDatatype = tblSalesStatistics.intDSDatatype) ON (tblPeriodEndDates.strMFMonth = tblSalesStatistics.strMFMonth) AND (tblPeriodEndDates.strMFYear = tblSalesStatistics.strMFYear) AND (tblPeriodEndDates.strDbName = tblSalesStatistics.strDbName)
ORDER BY tblSalesStatistics.strDbName, tblSalesStatistics.strPanelCode, tblDataType.strTypeCode, [tblSalesStatistics.strMFYear] & "/" & [tblSalesStatistics.strMFMonth] DESC , [tblSalesStatistics.strMFYear] & "/" & [tblSalesStatistics.strMFMonth] DESC;

I want to be able to add and avergae column grouped by panel code and type. Below an example of what I would like my output to look like, the column I want to create is the far right

Database Name Panel Code Data Type TimePeriod Planned DAP Actual DAP DaysLate DaysLateNorm Cause Average DaysLateNorm
MIDAS A MDS 2008/12 26 26 0 0 LOF 0.75
MIDAS A MDS 2008/09 27 24 -3 0 0.75
MIDAS A MDS 2008/06 25 23 -2 0 0.75
MIDAS A MDS 2008/03 25 28 3 3 0.75
MIDAS A PHM 2008/12 19 19 0 0 1
MIDAS A PHM 2008/09 20 20 0 0 1
MIDAS A PHM 2008/06 21 21 0 0 1
MIDAS A PHM 2008/03 21 25 4 4 1
MIDAS AH PHM 2008/12 33 36 3 3 DSG 2.5
MIDAS AH PHM 2008/09 31 38 7 7 LOF 2.5
MIDAS AH PHM 2008/06 42 30 -12 0 2.5
MIDAS AH PHM 2008/03 32 32 0 0 2.5
MIDAS AUH PHM 2008/12 21 21 0 0 0.5
MIDAS AUH PHM 2008/09 22 22 0 0 0.5
MIDAS AUH PHM 2008/06 21 23 2 2 DSG 0.5
MIDAS AUH PHM 2008/03 25 23 -2 0 0.5


Thanks in advance

 

[HarleyQuinn]

I might be missing something very obvious here, but how have you calculated the averages in your example?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[overdog1979]

Sorry, my first post probably wan't that clear

I haven't actually done these in access, this is just to show what i want.

For each unique panel ane and dataype, I want an average of all of DaysLateNorm. For example, Panel code A dataype MDS, the values for daylate norm are, 0, 0, 0, 3. I want the average of these(0.75 on this case) to be dispalyed for each row with panel code A and data type MDS....

TimePeriod DaysLateNorm Average
2008/12 0 0.75
2008/09 0 0.75
2008/06 0 0.75
2008/03 3 0.75

I hope that makes sense.

Thanks

 

[HarleyQuinn]

How about creating a sub query something like:

Select PanelCode, DataType, Avg(DaysLateNorm) as AverageDaysLateNorm
From YourTable
--Add any where criteria you need
Group By PanelCode, DataType

Then linking to that query (as if it were a table) on PanelCode and Datatype then displaying AverageDaysLateNorm from that in your main query?

Hope that helps (and makes sense!)

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.