Solved

Access average

Posted on 2016-08-23
17
54 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
  • 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 34

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 34

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
 

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 34

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 34

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 34

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

947 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now