Filter non-existent ranges from a Min/Max query

[rradelet]

From an Access 2000 table of Postal Codes, I am trying to run a query will display the minimum and maximum ranges of the postal codes, but not include any invalid ranges in the min & max.

Example:

[POSTAL_CODE]

V6M0A1
V6M1A0
V6M1A1
V6M1A2
V6M1A3
V6M1A4
V6M1A5
V6M1A6
V6M1A7
V6M1A8
V6M1A9
V6M1B2
V6M1B3
V6M1B4
V6M1B7
V6M1B8
V6M1B9
V6M1C1
V6M1C2
V6M1C3
V6M1C4
V6M1C5
V6M1C7
V6M1C8
V6M1C9

The result required is:

Min Max
V6M0A1 V6M0A1
V6M1A0 V6M1A9
V6M1B2 V6M1B4
V6M1B7 V6M1B9
V6M1C1 V6M1C5
V6M1C7 V6M1C9

I have tried grouping on a formula:

Left ({POSTAL_CODE},5 )

and then using min & max grouping but the resulting ranges then include
values that don’t exist:

Min Max
V6M0A1 V6M0A1
V6M1A1 V6M1A9
V6M1B2 V6M1B9 (V6M1B5 & V6M1B6 do not exist)
V6M1C1 V6M1C9 (V6M1C6 does not exist)


Here is the SQL view of my current query:

SELECT Left(

![POSTAL_CODE],5) AS Expr1, Min(TABLE].POSTAL_CODE) AS MinOfPOSTAL_CODE, Max(

.POSTAL_CODE) AS MaxOfPOSTAL_CODE
FROM

GROUP BY Left(

![POSTAL_CODE],5);


Can anyone steer me in the right direction?

 

[Pampers]

Just a question,
How can one determine that V6M1C5 is a maximum?? I would think that V6M1C9 is a maximum.

V6M1C1 V6M1C5
V6M1C7 V6M1C9

Pampers
Keeping it simple can be complicated

 

[rradelet]

I guess what I should have said is that I am trying to produce the min & max for consecutive ranges only.

Using the min & max grouping was the closest way that I could think of to get the result I am trying to create, which is a from/to list of the ranges of valid postal codes.

 

[AKelly77]

SELECT First(

.[POSTAL_CODE]) AS FirstOfZip,
FROM

GROUP BY (

.[POSTAL_CODE])
ORDER BY First(

.[POSTAL_CODE])DESC;

 

[rradelet]

AKelly:

Thanks for the reply, but your query just produces a list
of all the records in descending order.

What I am trying to generate is a list of all of the ranges
based on the ranges of the 6th character for the first 5 characters.

For the records:

V6M1C1
V6M1C2
V6M1C3
V6M1C4
V6M1C5
V6M1C7
V6M1C8
V6M1C9

The first is V6M1C1 and the last is V6M1C9,
but the value V6M1C6 doesn't exist.

Instead of a single range: V6M1C1 TO V6M1C9

I am trying to produce a list that will create multiple groups based on the first 5 characters if the 6th characters are not consecutive:


V6M1C1 to V6M1C5
V6M1C7 to V6M1C9

 

[fneily]

I created a table of zipcodes(yours) and a table with two fields, minzip and maxzip. You can make minzip a primary key but I didn't.
I then made a form with a command button and a listbox. The code on the OnClick event of the command button is:

Private Sub Command0_Click()
Dim RS As DAO.Recordset, RS2 As DAO.Recordset
Dim holda As String
Dim minfield As String
Dim maxfield As String
Dim y As Integer
Dim z As Integer
Set RS = CurrentDb.OpenRecordset("zipcode_table", dbOpenDynaset)
Set RS2 = CurrentDb.OpenRecordset("zipcode_minmax_table", dbOpenDynaset)
RS.MoveFirst
minfield = RS![zipcode]
maxfield = RS![zipcode]
holda = Left(RS![zipcode], 5)
y = Int(Right(RS![zipcode], 1))
TA: RS.MoveNext
If RS.EOF Then GoTo Exit_zipcode
If holda = Left(RS![zipcode], 5) Then
z = Right(RS![zipcode], 1)
If y = z - 1 Then
y = Right(RS![zipcode], 1)
maxfield = RS![zipcode]
GoTo TA
Else
RS2.AddNew
RS2![minzip] = minfield
RS2![maxzip] = maxfield
RS2.Update
minfield = RS![zipcode]
maxfield = RS![zipcode]
y = Right(RS![zipcode], 1)
GoTo TA
End If
Else
RS2.AddNew
RS2![minzip] = minfield
RS2![maxzip] = maxfield
RS2.Update
holda = Left(RS![zipcode], 5)
minfield = RS![zipcode]
maxfield = RS![zipcode]
y = Int(Right(RS![zipcode], 1))
GoTo TA
End If
Exit_zipcode:
RS2.AddNew
RS2![minzip] = minfield
RS2![maxzip] = maxfield
RS2.Update
RS.Close
RS2.Close
Set RS = Nothing
Set RS2 = Nothing
Exit Sub
End Sub

So it opens the zipcode table and the other table. It then does a first record process. It then loops through the other zipcodes comparing the group, then within the group the sequence number.
It produces the second table of min, max ranges.
You can then use this table as a source for a listbox, combobox or whatever.