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

x
?
Solved

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

Posted on 2014-07-30
13
Medium Priority
?
454 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
[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
  • 5
  • 3
  • 2
  • +1
13 Comments
 

Author Comment

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

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
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
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 48

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Progress

721 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