[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

need help with Access update query

Posted on 2014-08-06
13
Medium Priority
?
410 Views
Last Modified: 2014-08-11
Hi,

This question is a follow up to a previous question at the following link:  
http://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
0
Comment
Question by:Cam Raben
13 Comments
 
LVL 40

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 40244260
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.
0
 

Author Comment

by:Cam Raben
ID: 40244380
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.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40244404
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85
ID: 40244425
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 ...
0
 

Author Comment

by:Cam Raben
ID: 40244450
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.

TC
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40244731
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.
0
 

Author Comment

by:Cam Raben
ID: 40244779
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.
0
 

Author Comment

by:Cam Raben
ID: 40248883
@ 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.
0
 

Author Closing Comment

by:Cam Raben
ID: 40248963
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.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40249203
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) - https://www.youtube.com/watch?v=RVFgjMDeGaw
Here's an excellent explanation of what a crosstab does - http://www.fmsinc.com/microsoftAccess/query/crosstab-report/index.html
0
 

Author Comment

by:Cam Raben
ID: 40249802
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.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40252797
@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.
0
 

Author Comment

by:Cam Raben
ID: 40252895
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

868 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