Solved

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

Posted on 2014-07-30
13
438 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
13 Comments
 

Author Comment

by:Cam Raben
Comment Utility
Here's the db.
073014TCampProjectALL.mdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@Ted

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

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 125 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
Comment Utility
@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
Comment Utility
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
aikimark earned 250 total points
Comment Utility
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
Comment Utility
Thanks all.  I appreciate the input which helped me wind my way to the solution.  Best regards.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 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

9 Experts available now in Live!

Get 1:1 Help Now