Data Sorting Issue

[phd42122]

Hi All,
I'm doing a project to show different ranges of VIN Numbers with the goal of showing each Model's specific VIN Range. Unfortunately, some MFGs skip around with their VINs as seen in the table below:

MFG	Model	VIN
Honda	Accord	1
Honda	Accord	2
Honda	Accord	3
Honda	Accord	6
Honda	Accord	7
Honda	Accord	8
Honda	Accord	9
Honda	Accord	10
Honda	Accord	11
Toyota	Camry	a1
Toyota	Camry	a2
Toyota	Camry	a3
Toyota	Camry	b1
Toyota	Camry	b2
Toyota	Camry	b3
Toyota	Camry	b4
Toyota	Camry	b5

The ultimate goal is to produce something that looks like this:

MFG	Model	Min	Max
Honda	Accord	1	3
Honda	Accord	6	11
Toyota	Camry	a1	a3
Toyota	Camry	b1	b5

Min and Max functions don't recognize values that skip, so I'm at a loss for how to approach this from a database perspective

Thanks!

[June7]

I presume those aren't real VINs.

Don't understand why you want that output. The min and max for Honda Accord are 1 and 11, why are the gaps relevant? Why are Honda Accord 1 through 3 differentiated from Honda Accord 6 through 11?

What you want will probably require VBA code.

[phd42122]

They gap is relevant because I've found that certain manufacturers will skip a VIN # in one year and then include it the following year. So Honda might use 4-5 the following year.

[June7]

Okay, so they use 4-5 next year, 1 and 11 are still min and max. Still don't understand what you are trying accomplish with this output. Do you just want to identify gaps in sequence?

[phd42122]

I guess Min/Max is misleading--I want to be able to output an accurate list of serial numbers for a given MFG/Model/Year

Something like this:

MFG	Model	Year	VIN
Honda	Accord	2011	4-6
Honda	Accord	2011	11-13
Honda	Accord	2012	1-3
Honda	Accord	2012	7-10

[June7]

Always helps to provide critical information. Having Year attribute makes a big difference.

Options:

1. Totals query with GROUP BY on MFG and Model and Year fields with Min and Max aggregate calc fields

2. DMin and DMax calculations using WHERE CONDITION argument

3. Build a report using Grouping and Sorting features