We help IT Professionals succeed at work.
Get Started

need help with Access update query

Cam Raben
Cam Raben asked
on
517 Views
Last Modified: 2021-04-21
Hi,

This question is a follow up to a previous question at the following link:  
https://www.experts-exchange.com/Database/MS_Access/Q_28485399.html#a40224667

I have an Access db (chemicals2.mdb--attached) with a xtab query that produces results that are close to what I want, but it needs tweaking.  I need the xTab query to produce all of the following fields:
LOCCODE
LOCATION
MYDATE
OWNER
COUNTY
and a list of individual ANALYTES (as described below).  

The individual analytes should be only TWO columns per chemical element, one for the element's concentration and one for the element's lab reporting limit.  (As you can see, multiple columns exist for Al (aluminum))....there should only be two.  Again, of the two columns (per chemical element), the first is for the concentration of the element and should have a header/field name = the two letter name for that element (e.g. Al for aluminum); the second column is for the "lab reporting limit" and currently contains the letters PQL in the column headers in my existing xTab query.  That column should have the header/field name = the two letter name for that element + "-PQL" (e.g. Al-PQL).  So the query will produce two columns for all the different chemical elements (e.g.  Al, Al-PQL, As, As-PQL, Be, Be-PQL, etc).   The first column of the two will be populated by the RESULT field, and if the QUALIFY field contains a "U", then the result for that element should be 1/2 RESULT + 0.999.     Note:  If there is more than one version of a given element (e.g. As-dissolved-furnace AND As-dissolved-ICPMS AND As-furnace) in the db for a given "LOCCODE-MYDATE" event, then those different versions should be averaged and the result placed in the first column for that element.  

And to make sure that the query is working properly, I need to make sure that there are not two or more instances of a LOCCODE-MYDATE event.  In other words, the query should not produce more than one record for a given LOCCODE-MYDATE, and if it does, I need to know that.      

Thanks in advance!
Chemicals2.mdb
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE