Access average

I am working on a inventory model where I have to calculate average demand. My table looks like this.
ToolID       PartNumber      DemandDate     PartDemand    ToolNumber    
ToolID is the primary key. Part Number and Tool Number are often duplicated. I am trying to find average for the same part number only which can also be used in future calculation. I would really appreciate it if someone can help me.
Kai LeeAsked:
Who is Participating?
 
PatHartmanCommented:
I hate to disagree with other experts but in this case I do.  You should never use domain functions in queries if you have an alternative.

Select PartNumber, Avg(PartDemand) as AvgPartDemand
From tblPartInfo
Group by PartNumber;

Domain functions are themselves queries so what happens is Access runs a separate query for the domain function for each and every row of the main query.  So, if you have 10,000 parts, using the suggested domain function will run 10,000 queries.  For low rowcounts, you won't have an issue but once tables get enough rows, you will eventually find them to be much slower than necessary.  The query I posted takes one pass through the table.  The domain functions take 10,000 passes through the table although if you have an index on PartNumber, that could speed up the process.  If you don't have such an index, Access will do a full table scan for each domain function.  That's why they get so slow so fast.  10,000 x 10,000 is an awful lot of I/O for no reason.
1
 
Jeffrey CoachmanMIS LiasonCommented:
You did not say where you will be presenting this value, ...or what datatypes you have there.
But in a very general sense, ...you would use the Davg Aggregate function.

Something like this:

Davg("PartDemand", "YourTable", "PartNumber=" & YourPartNumber)
...if PartNumber is numeric...

If PartNumber is a string, ...use something like this:
Davg("PartDemand", "YourTable", "PartNumber=" & "'" & YourPartNumber & "'")

Hope this helps

JeffCoachman
1
 
PatHartmanCommented:
Average of what?
Is the Average for Part Number or PartNumber plus ToolNumber?

Select PartNumber, ToolNumber, Avg(somefield) As AvgSomeField
From yourtable
Group By PartNumber, ToolNumber;

If you want the average to be by PartNumber, remove the references to ToolNumber.

Select PartNumber, Avg(somefield) as AvgSomeField
From yourtable
Group by PartNumber;
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
I would not suggest using domain functions in a query.  That is extremely inefficient.  However, if you want to show an average on a form where you need an updateable recordset, then domain functions will do the job.
1
 
Kai LeeAuthor Commented:
Hi, Jeffrey and PatHartman. I am sorry I didn't make it clear. I am still an Access noob. My part number is text. I want to present it in query. And I am looking for average of PartDemand.
0
 
Jeffrey CoachmanMIS LiasonCommented:
If you wanted the average demand for all of the Parts, ...you could use a query, something like this :

SELECT tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & [PartNumber]) AS AverageDemand
FROM tblPartInfo
GROUP BY tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & [PartNumber]);
0
 
Jeffrey CoachmanMIS LiasonCommented:
If, as you say the PartNumber is text, then give this a whirl:

SELECT tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & "'" & [PartNumber] & "'") AS AverageDemand
FROM tblPartInfo
GROUP BY tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & "'" & [PartNumber] & "'");
0
 
Kai LeeAuthor Commented:
is "PartNumber="  the condition? if so, What should I put here if I want to have average demand for all PartNumber that have the  same text within the column? I attached a photo that might help.
Average demand for 23re, rt34, and 23rer.
Capture1.PNG
0
 
PatHartmanCommented:
is "PartNumber="  the condition?
In Jeff's query, the domain functions must be correlated to the main query.  So the PartNumbe = is syncing the domain function (which is itself a query) with the main query so that each time the domain function runs, it runs for only a single part number - the part number of the row that is causing the domain function to run.

As I said - do not use domain functions in queries unless you have no other alternative and in this case you can use a simple totals query so you don't need domain functions at all.
1
 
Jeffrey CoachmanMIS LiasonCommented:
sample db using aggregate functions
Database177.mdb
1
 
Kai LeeAuthor Commented:
I figured it out. Your methods all work out. Thank you very much for all the help.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,
I was a bit slow in checking the other Experts contributions.

Yes, ...there are other ways of doing this.
If you have a small number of values,  then using the aggregate functions should not be a problem,...
However, ... if large datasets and other complex structures are introduced, ...the aggregate functions can become a performance drag.

So please investigate all the Experts suggestions.
;-)

JeffCoachman
1
 
Kai LeeAuthor Commented:
Thank you all so much. I really love the atmosphere here. You guys are awesome.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, ...just catching up...
In the interest of fairness...
Look at the difference in syntax.
Notice that for the exact same results...
Pat's syntax is shorter (in addition to being more efficient and easier to read)
;-)

SELECT PartNumber, Avg(PartDemand) AS AvgPartDemand
FROM tblPartInfo
GROUP BY PartNumber;

SELECT tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & "'" & [PartNumber] & "'") AS AverageDemand
FROM tblPartInfo
GROUP BY tblPartInfo.PartNumber, DAvg("PartDemand","tblPartInfo","PartNumber=" & "'" & [PartNumber] & "'");

;-)

Jeff
1
 
PatHartmanCommented:
Are you really going with the domain function in a query or did you award the wrong answer?  
Pat's syntax is shorter (in addition to being more efficient and easier to read)
;-)
 Even Jeff agreed with my solution.
0
 
Kai LeeAuthor Commented:
I used yours. Sorry I award wrong answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.