Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access average

Posted on 2016-08-23
17
Medium Priority
?
65 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 39

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 39

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 39

Accepted Solution

by:
PatHartman earned 2000 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 39

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 39

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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