Solved

Access average

Posted on 2016-08-23
17
59 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 35

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 35

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 35

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 35

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 35

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

832 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