Cam Raben
asked on
need help with Access update query
Hi,
This question is a follow up to a previous question at the following link:
https://www.experts-exchange.com/questions/28485399/need-help-with-an-MS-Access-query.html?anchorAnswerId=40224667#a40224667
I have an Access db (chemicals2.mdb--attached)
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
This question is a follow up to a previous question at the following link:
https://www.experts-exchange.com/questions/28485399/need-help-with-an-MS-Access-query.html?anchorAnswerId=40224667#a40224667
I have an Access db (chemicals2.mdb--attached)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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 ...
ASKER
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
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
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.
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.
ASKER
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.
ASKER
@ 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.
ASKER
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) - 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
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
ASKER
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.
@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.
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.
ASKER
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.
ASKER