Solved

Access average

Posted on 2016-08-23
17
63 Views
Last Modified: 2016-08-26
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.
0
Comment
Question by:Kai Lee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
17 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767556
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41767561
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41767565
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Kai Lee
ID: 41767579
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767590
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767599
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
 

Author Comment

by:Kai Lee
ID: 41767613
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
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41767623
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41767633
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767636
sample db using aggregate functions
Database177.mdb
1
 

Author Comment

by:Kai Lee
ID: 41767640
I figured it out. Your methods all work out. Thank you very much for all the help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767646
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
 

Author Comment

by:Kai Lee
ID: 41767662
Thank you all so much. I really love the atmosphere here. You guys are awesome.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41767665
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41768877
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
 

Author Comment

by:Kai Lee
ID: 41768891
I used yours. Sorry I award wrong answer
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question