do you mean, if there were records for prdno = 1, 2, and 5 that you want a query that would tell you 3 and 4 didn’t exist?

I would like to have list of all the numbers that are missing if that is possible. Or have a list of all the numbers those present or something showing that that there is a hole in the sequence or the order.
4, 5, 9, – list of all the numbers missing -or-
1, 2, 3, (4), (5), 6, 7, 8, (9), 10 a list of all the numbers missign and present -or-
1, 2, 3, _, _, 6, 7, 8, _, 10 a list of numbers and showing just the holes.
I would like to know if any or all are possible?

My ultimate goal is to be able to down load a query for the AS400 system into an access from that would show the next open sequence number avalible in different groups. All the dfferent groups would be visible too. Each of the groups would show the next avalible number.

Take a look at this similar question. It might help you.

I will review this and work on it and comment back in a day or two …thanks

As I was looking at the string of information….as I began to review the different discussions, I started to wounder if this is using the AS400 Iseires SQL platform? Most of my means of commuication to the server is via passthrought query using MS Access.97 and I do have the ability of use office 2000 or newer. I have not used the Iseireis SQL as much…but could if that is what we are using as a platform there. With a little instruction I could do it.

I started to wounder if this is using the AS400 Iseires SQL platform?

The article linked by Carlosdl is indeed referring to iSeries. But the SQL is standard, with only the syntax of naming the tables being iSeries names.

This is also standard SQL and might give you some ideas:

select * from
 (                    
   SELECT 'Start hole' as Hole, (SEQ1 + 1) as NextSeq FROM myseqtbl
      WHERE not (SEQ1 + 1) in (SELECT seq1 FROM myseqtbl)               
 union 
   SELECT 'End hole  ' as Hole, (SEQ1 - 1) as LastSeq FROM myseqtbl
      WHERE not (SEQ1 - 1) in (SELECT seq1  FROM myseqtbl)
 ) as holetbl
 order by 2,1 desc

It assumes that you have a table named “myseqtbl” that has a column named SEQ1.

It will give a list of the beginning and end of every hole in “myseqtbl”. The UNION has two SELECTs. The first SELECT finds the start of every hole, and the second SELECT finds the end of every hole. The UNION and the ORDER BY put the two results together in a more useful order for viewing.

There are two special cases that can be ignored. If your sequence begins at 1, it lists 0 as the end of the first hole. And also it lists the next sequence after the last one in your table as the start of the last hole.

It does not list sequence numbers in the middle of holes. I.e., if a hole consists of sequence numbers (5, 6, 7 and 8), it won’t list (6 and 7). It only lists (5) as the start and (8) as the end.

Tom

So all I need to do is to replace “myseqtbl’ with “MSMMP100″ and “SEQ1′ with my ‘PRDNO’, since MSPMP100 is my table and PRDNO is my field

Tom Liotta Thanks for the help. I have been reviewing and looking at the SQL statement. My plan of action with this was to setting up a query to pull the information from the AS400 server. I realized that my first query above already has generated the complete list of numbers that are stored in the AS400 server. So all I need to do is set up a query that would pull for the query shown above?

Tom Liotta One other issue is ….there are some numbers in this list that have letters either mixed in with the numbers or precede the number or as a suffix. Is there a way to remove or pull the alpha codes out of the number so I am left with just raw numbers?

Here is what I have put together based on the SQL statement you mentioned.
select *
from
(SELECT `Start hole’ as Hole, (prdno + 1) as NestSeq
FROM MS0802allWapplication
WHERE not (prdno + 1) in (SELECT prdno FROM MS0802allWapplication)
union
SELECT `End hole` as Hole, (prdno – 1) as LastSeq
FROM MS0802allWapplication
WHERE not (prdno -1) in (SELECT prdno FROM MS0802allWapplication)
) as holetbl
order by 2,1 desc

When I go to run it the error show on the the word SELECT right after the first From ( Select
Now is it an issue if I am run Access 95 Querys

Access 95 Querys

I don’t know what language Access 95 uses. If it is a standard SQL language, it either implements only a basic subset or is too old. If it doesn’t follow SQL standards, perhaps it has implementation requirements that would accept some changes to the statement.

Does it support a SQL UNION?

there are some numbers in this list that have letters either mixed in with the numbers or precede the number or as a suffix.

Well, all bets are off. If there are characters that aren’t digits in the column, you don’t have “numbers”.

You could create a function that pulls digits out character by character and generates a resulting “number”. By the time you got that working and then integrated in with an Access 95 query, basic programming on the host server would have long since been finished with the whole process.

Inclusion of any kind of qualifying characters into a “number” sequence implies that the characters have a degree of implicit meaning. For example, there might be a part number like “01234″. Someone might later realize that the part can have modifications that result in “right-side” and “left-side” versions of the basic product, and those are created as parts “01234R” and “01234L”. There are then three distinct part numbers.

But those extra characters carry meaning by themselves. Fundamental database design rules would indicate that the extra characters ought to be in a separate column rather than embedded in an existing column. One reason for doing so is to avoid ever needing to do just what you have been tasked with doing.

If the column isn’t numeric, then the concept of “holes” becomes a mess. It isn’t very meaningful. How many holes are there between “1234A”, “1234B”, “B1234″ and “01234″?

Tom

Due to my lack of knowledge in SQL …that is why I am asking this…in MS Excel there is a function called “Text To Column” and there are two different way that this function can seperate “Delimited” or “Fixted” . Is there a way that this or anything command or fuction in SQL that would give me the same results. A command that looks at what is in each cell in a column and take each character and move them into different location across the rows?

Is there a way that this or anything command or fuction in SQL that would give me the same results.

The practical answer is “No.”

Such manipulation can be possible by very experienced SQL developers who can create intermediate views, functions, etc.; but even then it is complex and time consuming.

It simply isn’t what SQL is intended to do. You’re effectively needing a programming solution rather than a database query solution.

One basic approach in SQL could be first to mask out non-digits with TRANSLATE(). The non-digit characters would be translated to blanks or any other fixed character that wasn’t a digit; this character would be used as a delimiter. The masked value might then be processed recursively to locate substrings of digit characters between the delimiters. Each succeeding substring would be concatenated with the value from the previous recursion cycle. The cycles would end when no digit substrings could be located. The resulting string of digits could then be cast as a numeric value.

I’m not sure that any recursive processing could actually be implemented for that, but some kind of recursion is indicated to locate “letters … mixed in with the numbers”.

I suspect that it might be possible to implement a potentially horrible CASE structure if the pattern of mixed letters/digits is very limited. But some very detailed analysis needs to be done at the start to see if it’s even feasible.

Then again, someone out there just might have a bright idea that makes it easy. A deeply nested series of REPLACE() functions, for example, could replace individual letters with nulls. It’d be a really bad statement at the end, and if some initial values are all letters, the series should choke at some nesting level.

Anyway, I suggest that this simply shouldn’t be done at the query level.

Tom