need help with Access update query


This question is a follow up to a previous question at the following link:

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.      

Thanks in advance!
Cam RabenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I did not download your database so I'm just going to offer suggestions based on what I think you are asking.

Crosstabs pivot rows into columns but they do it for only a single field.  It sounds like you want to pivot two fields from each row.  The only way to do that is to create two crosstab queries and then join them.  The resulting query will result in fieldA pulling from one crosstab and fieldB pulling from the other.

As to duplicate rows.  The crosstab should eliminate them.  The aggregate you choose will determine how that happens,  For example Sum will add the duplicates, Avg will average them, Min will return the lowest value, etc.

Of course since you are selecting columns in addition to LOCCODE-MYDATE, you run the risk of multiple rows and there is nothing the crosstab can do for you.  Eliminate the columns that might cause duplication.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cam RabenAuthor Commented:
Thanks PatHartman - I appreciate the input.  I really don't know SQL very well which is why I am asking for help here on EE.  Hopefully, someone will be able to open the db and provide a solution.
Rather than waiting for someone to do it for you, why not try my suggestion and see if you can get it to work.  Did I understand your request?

I have an alternate suggestion for the duplicates.  Create both the crosstab queries with just the Loccode and mydate fields.  Then join the joined crosstabs back to the original table to pick up the additional columns.  Use group by for loccode and mydate and First() for all the other columns since you don't care what values from the other columns you choose.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also note that our role here is to HELP you arrive at a solution, not do it for you. We're more than happy to help, but posting your database and essentially saying "Do it for me" doesn't work with many of the Experts ...
Cam RabenAuthor Commented:
Thanks everyone.  I simply do not have the skill set to do this on my own.  (I did not write the initial cross tab query as I do not know even the rudimentary basics of SQL, so writing another, more involved query with joins, etc is way beyond me.)   My expertise is hydrogeology, not database programming, and I am simply trying to get a database in a shape where I can do statistical analyses  on the results....and thus be able to better help protect water quality in NC.  

I assure you that if I could do this myself I would do it.  I guess I do not understand the purpose of EE.  Thanks to those who have been helpful on my past queries.  I appreciate it very much.

The purpose of EE is to help you to help yourself.  The people we generally see here are people who create databases for themselves or others but are having trouble with certain aspects.

Learning how to create queries is quite different from learning how to create applications and write code.  But, to create queries, you MUST understand the underlying table schema.  If you use the Create/ query design option from the ribbon (menu in earlier versions), you will open the QBE (query by example) window.  The wizard first asks you to choose one or more tables and/or queries.  Once those are selected, you need to draw the join lines to tell Access how the tables are related.  Access will draw these join lines between tables  automatically if the programmer who created the app created relationships.  You will always have to draw the join lines yourself if you join to a query.  Once the tables/queries are "joined", you select columns that you want to see in the output recordset and finally, you add criteria.

There is a Create/ query wizard option also and that is a wizard that will create specialized query types such as crosstab.  So start by creating the two crosstab queries.  Save them and give them meaningful names (avoid spaces and do NOT use special characters in ANY query/table/column name).  Then create a third query and select the two crosstab queries.  Draw the join lines and select the columns.  The final step is a fourth query that joins the third query back to the source table to pick up the additional columns.  This query type is called a "totals" query.  To build it, just press the sigma button after you have selected all the columns.  Access will populate the totals like with "Group By" for all the columns.  Leave the controlling fields set to Group By but change all the others to First.

I think you'll find it is easier than you imagine and there will be no stopping you once you understand how it works.  You might look at Yahoo for videos on the topic.  Look for Crystal.  I don't know if she's done any videos on this topic but her explanations are very good and geared to the novice.

If you still feel that you can't learn how to do your own queries, you should consider hiring someone to make them for you.  None of the contributors at EE get paid to help you so as you can imagine the best ones are not likely to simply jump in and do your work for you.  We all have our own work to do and help out here because we like teaching and helping people to use our favorite software.
Cam RabenAuthor Commented:
Thanks for taking the time to reply PatHartman - I appreciate it.  I understand that you and the other experts here do not get paid...which is all the more amazing given all the kind efforts on the part of the community.
Cam RabenAuthor Commented:
@ PatHartman - Although the question has not been solved (I have spent quite a bit of time trying to get it to work), I am going to go ahead and award the points so that I can clean the slate and re-post.  Again, thanks for your help with the SQL 101 above.  I plan to re-write my question (hopefully better clarifying my needs) and re-post.  I ask that you please not respond to the next question so that someone else might take the helm and help me with some specific coding.  In trying to get this to work, with the myriad of misc SQL errors that keep popping up, I see that it would take me years to learn what you guys do!   I didn't learn hydrogeology in an afternoon, and I'm seeing that SQL coding is no different!  Thanks much and best regards.
Cam RabenAuthor Commented:
Helpful and appreciated, but I feel like the expert did not help me at my level...the answer was too generalized and over my head.
If I didn't help you to a solution then please do not award points to my answer, especially if you're going to give me a C.  You are not obligated to award points at all.  You can just ask to close the question or you can ask a moderator to specifically invite experts.  

I will not respond to your subsequent post since that is your request but please understand that it was not my answer that prevented other experts from jumping in.  To get yourself the best help, post the query you tried (based on my suggestion that you need to join two separate crosstabs) and tell us what isn't working for you.

Here's a link to a video on creating a crosstab (I didn't watch it) -
Here's an excellent explanation of what a crosstab does -
Cam RabenAuthor Commented:
If the grade of C is an issue and the moderator would like to re-open the question I'm happy to close it as unanswered without points.  I'd like for you to be satisfied with the outcome.
Dale FyeCommented:
@T Bell,

I've posted my modifications to the xTab query in your previous question.

Crosstab queries can get complicated, and as is so often the case, your requirements have changed as more information has come to light, making the process even more complicated.  

I also get skeptical of numbers when people start averaging values, where they start taking averages of numbers which have been manipulated (use [Result]/2 + .999 if [Qualify] = "U").  This makes the numbers very suspect, to me, and if these numbers are used subsequently in additional computations, it obviously makes those numbers suspect as well.

I frequently find that although we like to see our results in a spreadsheet like layout, that is rarely the most efficient method, and is frequently difficult to accomplish  I hope the sample that I posted in clarification of your previous post helps, but I'm going to have to break this thread off and move on to other projects.
Cam RabenAuthor Commented:
Yes, the U value means "less than".  In other words, the lab could not measure the element at their reporting limit, so these use a "U" to denote that.  So when I use statistics, I simply set that value to 1/2 the detection level (the 0.999 just lets me know that the result is a "less than" detection level) and proceed with stats.  This is an accepted method.  Again, thanks for all of your help Dale!  I'll use what you sent to pull all this together.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.