Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need Access query/update statement to average field's values

Posted on 2014-07-30
13
Medium Priority
?
455 Views
Last Modified: 2014-08-13
Hi,

I have an access db (see attached) with a tabled titled TCampProj that contains sample data collected at various locations (LOCCODE) on various dates (MYDATE) and presents data for various elements (SumOfFe, SumOfMn, SumOfZn, etc), along with their corresponding element-lab method reporting limit (SumOfFe-PQL, SumOfMn-PQL, SumOfZn-PQL, etc).

I need a query/update command that will
1) re-name my element and element-lab method reporting limit columns by deleting the first 5 letters of each of those fields  (SumOfFe becomes Fe, etc.....SumOfFe-PQL becomes Fe-PQL, etc);

2) create a new table (same updated field names as TCampProject table) that contains only those rows corresponding to unique LOCCODEs, with the values for each of the elements averaged for all MYDATES associated with that LOCCODE.  For example, if there are 5 sample dates (MYDATES) for LOCCODE = A470000, then Fe = average of all 5 Fe's, Mn = average of all Mn's, etc for all elements.  

3)  Repeat two except provide the median instead of the average.

Prior related question: http:Q_28485399.html
0
Comment
Question by:Cam Raben
  • 5
  • 3
  • 2
  • +1
11 Comments
 

Author Comment

by:Cam Raben
ID: 40230409
Here's the db.
073014TCampProjectALL.mdb
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40230500
Ted,

Have not looked at this db yet, still working the other question, but do you only want these averages for the [Result] column, not sure how you would calculate an average on the -PQL columns, since they are not numeric (have the "<") prefix.
0
 

Author Comment

by:Cam Raben
ID: 40230514
No, there is no average on those.  The single value chosen for the -PQL would be the value that shows up the most times.  I'm out tomorrow and back on Friday.  Thanks in advance!
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 46

Expert Comment

by:aikimark
ID: 40237701
@Ted

Is this a follow-up for your prior question?
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 500 total points
ID: 40237858
Sounds like you need to learn some basics of Access queries. I wrote a paper that covers some of this:
Microsoft Access Query Tips and Techniques (SQL and VBA).

See the section on Renaming Fields via Calculated Fields. Average is a Total feature within the built-in Access queries. Median is not. We offer a commercial add-in product, Total Access Statistics, that offers percentile calculations, but that may be overkill for what you need. The free trial will let you perform percentile calculations for a limited number of records.

The query paper is part of our Microsoft Access Query Help Center which has lots of other query related papers.

Our Microsoft Access Developer and VBA Programming Help Center has additional resources for Access beyond queries. Hope this helps.
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1000 total points
ID: 40237871
@Ted

You can simply change those SumOf____ names in the query I posted in the prior question.  Those names are auto generated by the query builder.
0
 

Author Comment

by:Cam Raben
ID: 40238172
Thanks all - I'll be back in the office and back on this on Tues.   And yes, Aikimark, this is a follow up to the earlier question - thanks.
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 40238704
As I was looking at this, I realized that you have a lot of records that, when comparing only Location, Element, Method, and MyDate have duplicate entries (see qry Duplicates).  In some cases there are as many as 8 records for a single location/element/method/date combination, in others there may be only 2

For example:

Location: BACK SWAMP OFF SR 1321 NR CASTALIA
Element: As
Method: dissolved - ICPMS
MyDate: 1/28/2011

There are some differences in the LOCCODE field with some of these duplicates, but not all.

I did not take this into account with the Crosstab query in the previous question, I simply took the first value in the [Return] and [PQL] columns.  Although for most of the duplicate records, it appears that the Result, PQL, and Qualify are the same, an example where that is not the case is:

Location: BACK SWAMP OFF SR 1321 NR CASTALIA
Element: As
Method: dissolved - ICPMS
MyDate: 7/25/11

where there are 4 records with [Result] values of (2.0, 2.3, 2.0, and 2.2)

So, for both this question, and the previous one.  How do you want to deal with these duplicate records for your results.  Do you want to average those values for the same location, element, method, and MyDate to create a single record for that one site/date, and then average those across all of the dates for that location/element/method (which I believe would be more accurate) or do you want to simply average all of the [Result] values across all of the location/element/method records?

BTW, when you throw the LOCCode field into the mix, the most "duplicates" you get for any Location/LocCode/Element/Method/Date is 4.
Chemicals2ShowingDups.zip
0
 

Author Comment

by:Cam Raben
ID: 40241354
Dale - thanks for post and for your attention to detail!   I'm now back in the office and will have a look and will reply back after I make sure I understand what I have here and what the queries are doing and retrieving.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1000 total points
ID: 40243897
You can build a query that uses the prior query as input.  In this case, you would group by LOCCODE and calculate the AVG() values of the columns you want.
0
 

Author Closing Comment

by:Cam Raben
ID: 40259235
Thanks all.  I appreciate the input which helped me wind my way to the solution.  Best regards.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Make the most of your online learning experience.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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…

824 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