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:
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.