Query results where gaps exist Min & Max do not work

[SloppyJavaJoe]

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?

 

[Jeff Boyce]

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.

 

[vanderghast]

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

 

[SloppyJavaJoe]

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.












- 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. ;-)

 

[SloppyJavaJoe]

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

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!!