Solved

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

Posted on 2014-07-30
13
442 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
ID: 40230409
Here's the db.
073014TCampProjectALL.mdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
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.  

 
LVL 45

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 125 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 45

Assisted Solution

by:aikimark
aikimark earned 250 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 47

Assisted Solution

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

Accepted Solution

by:
aikimark earned 250 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
table joins in qry 17 61
How to properly refer to a form in the code below 2 22
SetProperty Foreground Colour 5 14
Access Creating a report using a date range 11 10
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

839 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