|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 12th, 2010, 06:53 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Query results where gaps exist Min & Max do not work
Hello all,
I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137 018 2009 200999137 019 2009 200999137 020 2009 200999137 021 2009 200999137 022 2009 200999137 023 2009 200999137 024 2009 200999137 025 2009 200999137 026 2009 200999137 027 2009 200999137 028 2009 200999137 029 2009 200999137 030 2009 200999137 031 2009 200999137 032 2009 200999137 033 2009 200999137 034 2009 200999137 035 2009 200999137 036 2009 200999137 037 2009 200999137 038 2009 200999137 039 2009 200999137 040 2009 200999137 041 2009 200999137 042 2009 200999137 043 2009 200999137 044 2009 200999137 045 2009 200999137 046 2009 200999137 047 2009 200999137 048 2009 200999137 049 2009 200999137 050 2009 200999137 090 2009 200999137 091 2009 200999137 093 2009 200999137 094 2009 200999137 095 2009 200999137 096 2009 200999137 097 2009 200999137 098 2009 200999137 099 2009 200999137 100 2009 200999137 190 2009 200999137 191 2009 200999137 192 2009 200999137 193 2009 200999137 194 2009 200999137 195 2009 200999137 196 2009 200999137 197 2009 200999137 198 2009 200999137 199 2009 200999137 200 2009 200999137 222 2009 200999137 244 A tab does exist between these values but is hard to see here in the group. The last three numbers represent the specimen id. I am not sure how to create a query that will return the following: 2009 200999137 001 050 2009 200999137 090 100 2009 200999137 190 200 2009 200999137 222 222 2009 200999137 244 244 Is it even possible to do this in Access? 
|
|
#2
May 12th, 2010, 07:25 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query results where gaps exist Min & Max do not work
I'm having trouble visualizing the "rules" you want applied.
If you were working with an inexperienced intern and had to explain how to find the values you seek, given the data you provided, what instructions would you give him/her? For instance, I can see that all those 'records' share the same Year and SampleID, and that there are apparent "gaps" in the SpecimenIDs. Are you looking for "gaps", or what? Based on your post, you want "to show the first and last" ... and that would be the minimum SpecimenID and the maximum SpecimenID, unless there's something you haven't mentioned. .... by the way, the word "Year" is a reserved word in Access -- you may find that Access doesn't do what you expect if you use it. .... by the way, #2, "I was using a min and max"... how? Where? Are you using a Totals query? More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "SloppyJavaJoe" wrote in message ... Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137 018 2009 200999137 019 2009 200999137 020 2009 200999137 021 2009 200999137 022 2009 200999137 023 2009 200999137 024 2009 200999137 025 2009 200999137 026 2009 200999137 027 2009 200999137 028 2009 200999137 029 2009 200999137 030 2009 200999137 031 2009 200999137 032 2009 200999137 033 2009 200999137 034 2009 200999137 035 2009 200999137 036 2009 200999137 037 2009 200999137 038 2009 200999137 039 2009 200999137 040 2009 200999137 041 2009 200999137 042 2009 200999137 043 2009 200999137 044 2009 200999137 045 2009 200999137 046 2009 200999137 047 2009 200999137 048 2009 200999137 049 2009 200999137 050 2009 200999137 090 2009 200999137 091 2009 200999137 093 2009 200999137 094 2009 200999137 095 2009 200999137 096 2009 200999137 097 2009 200999137 098 2009 200999137 099 2009 200999137 100 2009 200999137 190 2009 200999137 191 2009 200999137 192 2009 200999137 193 2009 200999137 194 2009 200999137 195 2009 200999137 196 2009 200999137 197 2009 200999137 198 2009 200999137 199 2009 200999137 200 2009 200999137 222 2009 200999137 244 A tab does exist between these values but is hard to see here in the group. The last three numbers represent the specimen id. I am not sure how to create a query that will return the following: 2009 200999137 001 050 2009 200999137 090 100 2009 200999137 190 200 2009 200999137 222 222 2009 200999137 244 244 Is it even possible to do this in Access?
|
|
#3
May 12th, 2010, 08:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query results where gaps exist Min & Max do not work
I suspect your field SpecimenID is a string (leading 0 at the left)
The solution I proposed will be in multiple queries, the last one being: SELECT [year], sampleID, MIN(specimenID), MAX(specimenID) FROM rankedData GROUP BY [year], sampleID, int(specimenID) - rank with rankedData itself a query, ranking the specimenID, which can be done with a join, as example: SELECT a.[year], a.sampleID, a.specimenID, COUNT(*) AS rank FROM originalData AS a INNER JOIN originalData AS b ON a.[year]=b.[year] AND a.sampleID = b.sampleID AND a.specimenID = b.specimenID GROUP BY a.[year], a.sampleID, a.specimenID where I assumed that the table name is originalData. Indeed, that last query should produce something like: 2009 200999137 001 1 2009 200999137 002 2 2009 200999137 003 3 .... 2009 200999137 048 48 2009 200999137 049 49 2009 200999137 050 50 2009 200999137 090 51 2009 200999137 091 51 .... And the first query simply capitalized on the fact that the subtraction of the last two columns return a CONSTANT for a given sequence,in other word, int(specimenID)-rank defines a sequence and thus MIN and MAX over the sequence return the border of the sequence, as wanted. Vanderghast, Access MVP
|
|
#4
May 12th, 2010, 08:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query results where gaps exist Min & Max do not work
There is a solution published by Joe Celko for identifying sequences. It
should be possible to apply it here to return the start and end values of each sequence of specimen values per sample per year SELECT T1.Year, T1.SampleID, T1.SpecimenID As Starts, MIN(T2.SpecimenID) AS Ends FROM YourTable AS T1, YourTable AS T2 WHERE T1.Year =T2.Year AND T1.SampleID = T2.SampleID AND T1.SpecimenID = T2.SpecimenID AND NOT EXISTS (SELECT * FROM YourTable AS T3 WHERE T3.Year = T1.Year AND T3.SampleID = T1.SampleID AND T3.SpecimenID NOT BETWEEN T1.SpecimenID AND T2.SpecimenID AND (T3.SpecimenID = T1.SpecimenID - 1 OR T3.SpecimenID = T2.SpecimenID +1)) GROUP BY T1.Year, T1.SampleID, T1.SpecimenID; Ken Sheridan Stafford, England Ken Sheridan Stafford, England SloppyJavaJoe wrote: Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137 018 2009 200999137 019 2009 200999137 020 2009 200999137 021 2009 200999137 022 2009 200999137 023 2009 200999137 024 2009 200999137 025 2009 200999137 026 2009 200999137 027 2009 200999137 028 2009 200999137 029 2009 200999137 030 2009 200999137 031 2009 200999137 032 2009 200999137 033 2009 200999137 034 2009 200999137 035 2009 200999137 036 2009 200999137 037 2009 200999137 038 2009 200999137 039 2009 200999137 040 2009 200999137 041 2009 200999137 042 2009 200999137 043 2009 200999137 044 2009 200999137 045 2009 200999137 046 2009 200999137 047 2009 200999137 048 2009 200999137 049 2009 200999137 050 2009 200999137 090 2009 200999137 091 2009 200999137 093 2009 200999137 094 2009 200999137 095 2009 200999137 096 2009 200999137 097 2009 200999137 098 2009 200999137 099 2009 200999137 100 2009 200999137 190 2009 200999137 191 2009 200999137 192 2009 200999137 193 2009 200999137 194 2009 200999137 195 2009 200999137 196 2009 200999137 197 2009 200999137 198 2009 200999137 199 2009 200999137 200 2009 200999137 222 2009 200999137 244 A tab does exist between these values but is hard to see here in the group. The last three numbers represent the specimen id. I am not sure how to create a query that will return the following: 2009 200999137 001 050 2009 200999137 090 100 2009 200999137 190 200 2009 200999137 222 222 2009 200999137 244 244 Is it even possible to do this in Access? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1
|
|
#5
May 12th, 2010, 10:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query results where gaps exist Min & Max do not work
On May 12, 10:25*am, "Jeff Boyce" wrote:
I'm having trouble visualizing the "rules" you want applied. If you were working with an inexperienced intern and had to explain how to find the values you seek, given the data you provided, what instructions would you give him/her? For instance, I can see that all those 'records' share the same Year and SampleID, and that there are apparent "gaps" in the SpecimenIDs. *Are you looking for "gaps", or what? *Based on your post, you want "to show the first and last" ... and that would be the minimum SpecimenID and the maximum SpecimenID, unless there's something you haven't mentioned. ... by the way, the word "Year" is a reserved word in Access -- you may find that Access doesn't do what you expect if you use it. ... by the way, #2, "I was using a min and max"... how? *Where? *Are you using a Totals query? More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "SloppyJavaJoe" wrote in message ... Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137 018 2009 200999137 019 2009 200999137 020 2009 200999137 021 2009 200999137 022 2009 200999137 023 2009 200999137 024 2009 200999137 025 2009 200999137 026 2009 200999137 027 2009 200999137 028 2009 200999137 029 2009 200999137 030 2009 200999137 031 2009 200999137 032 2009 200999137 033 2009 200999137 034 2009 200999137 035 2009 200999137 036 2009 200999137 037 2009 200999137 038 2009 200999137 039 2009 200999137 040 2009 200999137 041 2009 200999137 042 2009 200999137 043 2009 200999137 044 2009 200999137 045 2009 200999137 046 2009 200999137 047 2009 200999137 048 2009 200999137 049 2009 200999137 050 2009 200999137 090 2009 200999137 091 2009 200999137 093 2009 200999137 094 2009 200999137 095 2009 200999137 096 2009 200999137 097 2009 200999137 098 2009 200999137 099 2009 200999137 100 2009 200999137 190 2009 200999137 191 2009 200999137 192 2009 200999137 193 2009 200999137 194 2009 200999137 195 2009 200999137 196 2009 200999137 197 2009 200999137 198 2009 200999137 199 2009 200999137 200 2009 200999137 222 2009 200999137 244 A tab does exist between these values but is hard to see here in the group. *The last three numbers represent the specimen id. *I am not sure how to create a query that will return the following: 2009 * * 200999137 * * 001 * * 050 2009 * * 200999137 * * 090 * * 100 2009 * * 200999137 * * 190 * * 200 2009 * * 200999137 * * 222 * * 222 2009 * * 200999137 * * 244 * * 244 Is it even possible to do this in Access?- Hide quoted text - - Show quoted text - I apologize, I should have explained better what I was looking for. Yes, Year and SampleID repeat over and over again, it is the Specimen ID that is important. I need to show where they worked up the specimens from the first specimen to the last. I need them to see if they missed one specimen or a "gap" of specimens. That is my quandary. a Min and a Max function will return 2009 200999137 001-244 because it doesn't quantify the specimens as a series. The whole idea is to show the users a "status" screen so they know what specimens have been done and what has not been done hence the example output I am hoping to achieve. Thanks for asking!! yes, Year is a reserved word, I should have added brackets around it for my example. ;-)
|
|
#6
May 12th, 2010, 11:30 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query results where gaps exist Min & Max do not work
On May 12, 11:48*am, "KenSheridan via AccessMonster.com" u51882@uwe
wrote: There is a solution published by Joe Celko for identifying sequences. *It should be possible to apply it here to return the start and end values of each sequence of specimen values per sample per year SELECT T1.Year, T1.SampleID, * T1.SpecimenID As Starts, MIN(T2.SpecimenID) AS Ends FROM YourTable AS T1, YourTable AS T2 WHERE T1.Year =T2.Year AND T1.SampleID = T2.SampleID AND *T1.SpecimenID = T2.SpecimenID AND NOT EXISTS * *(SELECT * * * FROM YourTable AS T3 * * WHERE T3.Year = T1.Year * * AND T3.SampleID = T1.SampleID * * AND T3.SpecimenID NOT BETWEEN T1.SpecimenID AND T2.SpecimenID * * AND (T3.SpecimenID = T1.SpecimenID - 1 * * * *OR T3.SpecimenID = T2.SpecimenID +1)) GROUP BY T1.Year, *T1.SampleID, *T1.SpecimenID; Ken Sheridan Stafford, England Ken Sheridan Stafford, England SloppyJavaJoe wrote: Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year * * * *SampleID * * * *SpecimenID 2009 * * * *200999137 * * * 001 2009 * * * *200999137 * * * 002 2009 * * * *200999137 * * * 003 2009 * * * *200999137 * * * 004 2009 * * * *200999137 * * * 005 2009 * * * *200999137 * * * 006 2009 * * * *200999137 * * * 007 2009 * * * *200999137 * * * 008 2009 * * * *200999137 * * * 009 2009 * * * *200999137 * * * 010 2009 * * * *200999137 * * * 011 2009 * * * *200999137 * * * 012 2009 * * * *200999137 * * * 013 2009 * * * *200999137 * * * 014 2009 * * * *200999137 * * * 015 2009 * * * *200999137 * * * 016 2009 * * * *200999137 * * * 017 2009 * * * *200999137 * * * 018 2009 * * * *200999137 * * * 019 2009 * * * *200999137 * * * 020 2009 * * * *200999137 * * * 021 2009 * * * *200999137 * * * 022 2009 * * * *200999137 * * * 023 2009 * * * *200999137 * * * 024 2009 * * * *200999137 * * * 025 2009 * * * *200999137 * * * 026 2009 * * * *200999137 * * * 027 2009 * * * *200999137 * * * 028 2009 * * * *200999137 * * * 029 2009 * * * *200999137 * * * 030 2009 * * * *200999137 * * * 031 2009 * * * *200999137 * * * 032 2009 * * * *200999137 * * * 033 2009 * * * *200999137 * * * 034 2009 * * * *200999137 * * * 035 2009 * * * *200999137 * * * 036 2009 * * * *200999137 * * * 037 2009 * * * *200999137 * * * 038 2009 * * * *200999137 * * * 039 2009 * * * *200999137 * * * 040 2009 * * * *200999137 * * * 041 2009 * * * *200999137 * * * 042 2009 * * * *200999137 * * * 043 2009 * * * *200999137 * * * 044 2009 * * * *200999137 * * * 045 2009 * * * *200999137 * * * 046 2009 * * * *200999137 * * * 047 2009 * * * *200999137 * * * 048 2009 * * * *200999137 * * * 049 2009 * * * *200999137 * * * 050 2009 * * * *200999137 * * * 090 2009 * * * *200999137 * * * 091 2009 * * * *200999137 * * * 093 2009 * * * *200999137 * * * 094 2009 * * * *200999137 * * * 095 2009 * * * *200999137 * * * 096 2009 * * * *200999137 * * * 097 2009 * * * *200999137 * * * 098 2009 * * * *200999137 * * * 099 2009 * * * *200999137 * * * 100 2009 * * * *200999137 * * * 190 2009 * * * *200999137 * * * 191 2009 * * * *200999137 * * * 192 2009 * * * *200999137 * * * 193 2009 * * * *200999137 * * * 194 2009 * * * *200999137 * * * 195 2009 * * * *200999137 * * * 196 2009 * * * *200999137 * * * 197 2009 * * * *200999137 * * * 198 2009 * * * *200999137 * * * 199 2009 * * * *200999137 * * * 200 2009 * * * *200999137 * * * 222 2009 * * * *200999137 * * * 244 A tab does exist between these values but is hard to see here in the group. *The last three numbers represent the specimen id. *I am not sure how to create a query that will return the following: 2009 * * 200999137 * * 001 * * 050 2009 * * 200999137 * * 090 * * 100 2009 * * 200999137 * * 190 * * 200 2009 * * 200999137 * * 222 * * 222 2009 * * 200999137 * * 244 * * 244 Is it even possible to do this in Access? -- Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1- Hide quoted text - - Show quoted text - Thank you for the example. MOst of all, thank you for the information as I now know what to search for. I didnt' think to look for "series". Pretty dumb. Anyway, these examples are a great starting point. I am not getting the results exactly but with your assistance I believe I can now figure it out. Thanks!!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
