[AccessD] One Question (Was Two Questions)

Hello All,

I think I understand what you are trying to get to, but...
Not sure if it is a good solution...but I believe this can be accomplised 
with 1 query.  For this example I have a table (Table1) with a field(Code) 
which has a series of nonsequential numbers.  Here is the SQL:

SELECT TOP 1 [code]+1 AS NextCode
FROM Table1
GROUP BY Table1.Code, [code]+1
HAVING (((DLookUp("[code]","table1",[code] & "= code -1")) Is Null))
ORDER BY Table1.Code;

Comments/suggestions???

Thanks,

Mark A. Matte




>From: Rocky Smolin - Beach Access Software <bchacc at san.rr.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] One Question (Was Two Questions)
>Date: Wed, 14 Jun 2006 10:17:42 -0700
>
>Karen:
>
>There are lots of clever ways to find the holes.  But in this case,
>since the number of records is small, I'd use brute force - open a DAO
>recordset of the dictation codes sorted ascending and loop though them
>checking for a missing number.  If no numbers are missing, add one to
>the last number.  Put the routine in a function which returns the first
>missing or new number.  My guess is that even though it's looping
>through the whole series of numbers the user won't experience any lag
>time when you run that function.
>
>Regards,
>
>Rocky
>
>
>karenr7 at oz.net wrote:
> > Yes, that's what has to happen. These dictation codes used to be kept in 
>a
> > big notebook. Then a spreadsheet was started. But the old numbers are in
> > use, just not on the spreadsheet. They started numbering at 8000, went 
>to
> > 99999 and then started back at 1. So I want to "fill in the holes" going
> > forward, but there are odd outlyers, not to mention wholes in the past.
> >
> > Yuck.
> >
> > Regards,
> > Karen Rosenstiel
> > Seattle WA USA
> >
> >
> >> Karen:
> >>
> >> Why do you need to 'fill in the holes'.  Why not just carry on from the
> >> last number.  Going forward there won't be any holes to fill.
> >>
> >> Rocky
> >>
> >>
> >> Karen Rosenstiel wrote:
> >>
> >>> OK, so with your collective help, this thing is coming along.
> >>>
> >>> I want to sort the dictation passwords (a unique 5 digit number)with 
>the
> >>> highest number on top so that when adding a new dictating provider to
> >>> the
> >>> database, I can see what the next highest new number should be.
> >>> Unfortunately, I inherited this really crappy Excel spreadsheet and
> >>> there's
> >>> a bunch of codes out of sequence. I think that people were just being
> >>> lazy
> >>> and giving a random high number  to incoming requests because they
> >>> didn't
> >>> feel like looking something up. So I can't just have some code to pick
> >>> the
> >>> highest number, because it would be out of sequence.
> >>>
> >>> Now I've got an unbound text box with the following code:
> >>>
> >>> SELECT tStaff.StaffID, [LanierID] & " -- " & [LastName] &
> >>> IIf(IsNull([FirstName]),"",", " & [FirstName]) AS Lanier FROM tStaff
> >>> ORDER
> >>> BY [LanierID] DESC;
> >>>
> >>> I think the answer is probably going to be no, but... Is there a way I
> >>> can
> >>> mark in the unbound box when the numbers go out of sequence.
> >>>
> >>> In other words:
> >>>
> >>> 13888
> >>> -------------------> Is there some way to put a line here or different
> >>> colored text or some kind of marker
> >>> so that the users can can see at a glance what the next highest
> >>> sequential
> >>> number shuld be?
> >>> 12347
> >>> 12346
> >>> 12345
> >>>
> >>> Any ideas?
> >>>
> >>> Regards,
> >>>
> >>> Karen Rosenstiel
> >>> Seattle WA USA
> >>>
> >>>
> >>>
> >>>
> >>>
> >> --
> >> Rocky Smolin
> >> Beach Access Software
> >> 858-259-4334
> >> www.e-z-mrp.com
> >>
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >>
> >>
> >
> >
> >
>
>--
>Rocky Smolin
>Beach Access Software
>858-259-4334
>www.e-z-mrp.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com