Prev: Calculated field
Next: Show only this year
From: SloppyJavaJoe on 12 May 2010 13:53
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?

From: Jeff Boyce on 12 May 2010 14:25
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" <tfrawley(a)gci.net> wrote in message
news:d2b7c7dd-e8c8-4da8-873c-ad1908cd36d8(a)s13g2000prc.googlegroups.com...
> 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?
>


From: vanderghast on 12 May 2010 15:26
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

From: KenSheridan via AccessMonster.com on 12 May 2010 15:48
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/Forums.aspx/access-queries/201005/1

 | 
Pages: 1
Prev: Calculated field
Next: Show only this year