Averaging Totals of several adjacent fields

[Skip Bisconer]

I am have difficulty in developing an average of seven months of transactions in a query based on three tables involving inventory. Inv Master Table, Invoice Item Qty History Table and WarehouseLocation Qty table.

The plan is to use the last seven months of transactions my inventory line and take an average which will be use with other caluclations to determine if the line item needs to be ordered during this review cycle or whether that the item is at or over recommended levels.

The fields to be averaged are formatted as Number long integer in the originating table. But I cannot not get a result from any way I have tried to caluclate or average. I tried to get the fields to sum up to a total but when I open the query it asks me to define each field defined in that calculation.

I have forced a General Number format on every field I am trying to use. I can't seem to find any soluting in help under averaging. I am trying not to go to Excel as I have to pass this solution along to Non computer people.

I appreciate any suggestions you are able to pass on to me.

 

[raskew]

Hi -

Would you please post your query SQL.

Thanks - Bob

 

[neileg]

There is an aggregate function for average. Create an aggregate query (alos known as a totals query) and select average.

 

[Skip Bisconer]

Thanks for responding.

Here is my SQL. In this particular one I just tried to total the 7 fields ending in B101-B107.

SELECT [IN Part Master].INPM_PART, [IN Part Master].INPM_DESC, [IN Whse Quantities].INPM_BWID, [IN Part Master].INPM_SNAM, [IN Part Master].INPM_ASK1, [IN Part Master].INPM_ASK2, [IN Part Master].INPM_PCLS, [IN Part Master].INPM_ACIN, [IN Part Master].INPM_1QTY, [IN Part Master].[INPM_1U/M], [IN Part Master].INPM_MQTY, [IN Part Master].INPM_MVID, [IN Part Master].INPM_MVUM, [IN Part Master].INPM_MVOQ, [IN Part Master].INPM_MRBC, [IN Part Master].INPM_MB0Q, [IN Part Master].INPM_MB0A, [IN Part Master].INPM_MB0C, [IN Part Master].INPM_MB1Q, [IN Part Master].INPM_MB1A, [IN Part Master].INPM_MB1C, [IN Part Master].INPM_MBPR, [IN Part Master].INPM_MBCS, [IN Whse Qty Invoiced History].INWH_BI01, [IN Whse Qty Invoiced History].INWH_BI02, [IN Whse Qty Invoiced History].INWH_BI03, [IN Whse Qty Invoiced History].INWH_BI04, [IN Whse Qty Invoiced History].INWH_BI05, [IN Whse Qty Invoiced History].INWH_BI06, [IN Whse Qty Invoiced History].INWH_BI07, [IN Whse Quantities].INPM_BQMN, [IN Whse Quantities].INPM_BQMX, [IN Whse Quantities].INPM_BMVS, [IN Whse Quantities].INPM_BQOH, ([INPM_MBPR]-[INPM_MBCS])/[INPM_MBPR] AS ListMargin, [IN Whse Qty Invoiced History].[INWH_B101]+[IN Whse Qty Invoiced History].[INWH_B102]+[IN Whse Qty Invoiced History].[INWH_B103]+[IN Whse Qty Invoiced History].[INWH_B104]+[IN Whse Qty Invoiced History].[INWH_B105]+[IN Whse Qty Invoiced History].[INWH_B106]+[IN Whse Qty Invoiced History].[INWH_B107] AS [AVRG-MO]
FROM [IN Whse Quantities] INNER JOIN ([IN Part Master] INNER JOIN [IN Whse Qty Invoiced History] ON [IN Part Master].INPM_PART = [IN Whse Qty Invoiced History].INWH_PART) ON [IN Whse Quantities].INPM_PART = [IN Part Master].INPM_PART
WHERE ((([IN Part Master].INPM_PART) Not Like "/*") AND (([IN Part Master].INPM_PCLS) Between "199" And "950"))
ORDER BY [IN Part Master].INPM_PART;

 

[Skip Bisconer]

I haven't the faintest idea why but I was able to make a query that seems to work. But I had to make two expressions one to add the fields together and the next to devide the Total by the number of fields.

I guess I don't know how to state the arguments for the columns in an AVG function as I couldn't get any results using it the way I was trying. The only example I could find in help was to average a single field of data. But, I probably don't understand the explanation.

Thanks for looking at my problem.