Newbie question

[K Dales]

There are ways to do what you want, but they won't be
terribly easy since the part numbers do not have a
fixed "format" - i.e. some of them are #####A, some are
######A, some are ####-### and some are ####-###-#, etc.
Somehow you need to figure out how to convert these into
numerical order by stripping out any non-numeric
characters - and, at the same time (this could be
important) distinguishing between the different patterns
(for example, 1113-5 is not the same as 11135).

Here's how I would tackle it - can only give a quick
outline for now:

1) Develop a query with a calculated field that strips the
part numbers of any non-numeric characters and puts them
in the desired order (I'll call this query "ITEM SORT" and
the calculated field "SORTED PART NO")

2) Develop a second query based on the first one that
lists the same items in the same order, but also includes
a calculated field that shows the part number of the
previous item - you can do this with the DMax function;
i.e. in your column definition grid use the following
expression:

PREVIOUS PARTMax("[SORTED PART NO]","ITEM SORT","[SORTED
PART NO]<" & [SORTED PART NO]")

(this function finds the SORTED PART NO that is the
maximum value less than the value of the current record in
the new query).

3) Now use the test ([SORTED PART NO] - [PREVIOUS PART])>1
to determine if there is a gap in the numeric sequence.

Note: this query could take a long time to run if you have
many part numbers, since it needs to run a domain
aggregate function for each record. But it should be able
to do the job.

 

[K Dales]

It all depends on the part numbers. How many different
formats are there? Are these likely to stay the same
permanently or might they change?

If you need to distinguish between the different number
formats - and if you know in advance the formats you need
to recognize, I would suggest using the LIKE operator to
determine which pattern they fit:

Function PartNoFormat(PartNum as String) as Integer

If PartNum Like "#####[A-Z]" Then PartNoFormat = 1
If PartNum Like "######[A-Z]" Then PartNoFormat = 2
etc...

End Function

You could use the results of this function as one field
and group on this field to separate the part numbers into
their corresponding groups by format.

Then you need another way to strip any non-numeric
characters so you can do a numeric sort:

Function NumberPart(PartNum as String) as Long

Dim PartNumLen as Integer, i as Integer
Dim PartNum1 as String

PartNumLen = Len(PartNum)
PartNum1 = ""

For i = 1 to PartNumLen
If IsNumeric(Mid(PartNum,i,1)) Then _
PartNum1 = PartNum1 & Mid(PartNum,i,1)
Next i

NumberPart = CLng(PartNum1)

End Function

This - when used in another calculated field - gives a
numeric value to sort on and the basis for comparing the
current record with the previous one to see if you
have "skipped" any numeric values, as I described in my
original reply.

Hope this gives you enough to go by, at least to give you
some ideas to get you started!

-----Original Message-----
Mr. Dales,

Now that I have looked at this, I am questioning how to create the calulated
field. I am not sure how to approach the formatting aspects. Should I use an
If then Else statement?

There are ways to do what you want, but they won't be
terribly easy since the part numbers do not have a
fixed "format" - i.e. some of them are #####A, some are
######A, some are ####-### and some are ####-###-#, etc.
Somehow you need to figure out how to convert these into
numerical order by stripping out any non-numeric
characters - and, at the same time (this could be
important) distinguishing between the different patterns
(for example, 1113-5 is not the same as 11135).

Here's how I would tackle it - can only give a quick
outline for now:

1) Develop a query with a calculated field that strips the
part numbers of any non-numeric characters and puts them
in the desired order (I'll call this query "ITEM SORT" and
the calculated field "SORTED PART NO")

2) Develop a second query based on the first one that
lists the same items in the same order, but also includes
a calculated field that shows the part number of the
previous item - you can do this with the DMax function;
i.e. in your column definition grid use the following
expression:

PREVIOUS PARTMax("[SORTED PART NO]","ITEM SORT","[SORTED
PART NO]<" & [SORTED PART NO]")

(this function finds the SORTED PART NO that is the
maximum value less than the value of the current record in
the new query).

3) Now use the test ([SORTED PART NO] - [PREVIOUS PART]) 1
to determine if there is a gap in the numeric sequence.

Note: this query could take a long time to run if you have
many part numbers, since it needs to run a domain
aggregate function for each record. But it should be able
to do the job.

-----Original Message-----
I have just inherited an excel file (Part Numbers)

that
was converted into

Access 2002. Within this Table I have a series of

part
numbers that have

gaps in the numerical order. Is there a way that I can program or query the
table to look for gaps in the order so as to create a

new
record? (Note: this

inheritance includes 18 tables in the same condition)

Example Below
P/N Description
01268A CFP-20 1.5HP 12O/60
01269A CFP-20 1.5HP 230/50
01270A CFP-20DS 1.5HP 120/60
01271A CFP-20DS 1.5HP 230/50
01272A CFP-17 1HP 120/60
01273A CFP-17 1HP 230/50
01274A CFP-17 1.5HP 120/60
01275A CFP-17 1.5HP 230/50
01276A CFP-13 .5HP 120/60
01277A CFP-13 .5HP 230/50
01278A FM-1700HD (HDTR)
01279a C2K-20O0DS 120/50/60 (COMBO)
0152-053 NLS**ASY DISC SNDG 19 IN 13202
017100A SANDER 1600
017101A SANDER 1600 DC
0174-502 NLS***ASSY-BRUSH,SUPER-VAC
0176-002-1 GEAR-SHAFT-BRUSH NLS
0176-004 ASSY-MOTOR-DR,115V NLS
0176-009 NLS***ASSY-BRUSH-AMERFIL II,OP
0176-010 ASSY-DRVR-PAD,OPTL NLS

.

.