I have a CD Library for work that I am numbering within separate 'classes'. I
have them incrementing in each class by one using DMax. But sometimes I may
move a CD to a new class. So when I move it it will get a new number from the
new class and the old number is left 'open'. Now that CD's original number
is available to be assigned to a new CD. How can I search the table to find a
missing number?
THis is probably a simple procedure, but I am mind blocked.

Fairly new to a VBA so be gentle :-)

Mary

Create a table of all possible numbers. In a query left join to your CD
table number field. Use Null as criteria for the CD table number field. Set
it to select the TOP 1 by either editing the SQL so it reads SELECT Top 1
YouAllNumberList, xxx, xxx etc.
Or in the design view change the icon that reds All to 1 by clicking in it
and typing 1.

"mj" wrote:

> I have a CD Library for work that I am numbering within separate 'classes'. I
> have them incrementing in each class by one using DMax. But sometimes I may
> move a CD to a new class. So when I move it it will get a new number from the
> new class and the old number is left 'open'. Now that CD's original number
> is available to be assigned to a new CD. How can I search the table to find a
> missing number?
> THis is probably a simple procedure, but I am mind blocked.
>
> Fairly new to a VBA so be gentle :-)
>
> Mary

I really wish I knew more. I really didn't understand this solution. My SQL
is really bad.

Creating a table, each time a new CD is added, with all possible numbers
would slow down the process, wouldn't it. As each class varies in how many
CDs are in it. In some cases we have 10 CD's in a class and 1000 in another.
I need to be able to look through the 1000 numbers to see if number 7 is
missing and assign that to the newly added CD rather than 1001. I don't want
to have to print out a report and eyeball this.
Is there something in VBA that can get this to work (I didn't quite
understand your SQL solution)?


Mary


------------
"KARL DEWEY" wrote:

> Create a table of all possible numbers. In a query left join to your CD
> table number field. Use Null as criteria for the CD table number field. Set
> it to select the TOP 1 by either editing the SQL so it reads SELECT Top 1
> YouAllNumberList, xxx, xxx etc.
> Or in the design view change the icon that reds All to 1 by clicking in it
> and typing 1.
>
> "mj" wrote:
>
> > I have a CD Library for work that I am numbering within separate 'classes'. I
> > have them incrementing in each class by one using DMax. But sometimes I may
> > move a CD to a new class. So when I move it it will get a new number from the
> > new class and the old number is left 'open'. Now that CD's original number
> > is available to be assigned to a new CD. How can I search the table to find a
> > missing number?
> > THis is probably a simple procedure, but I am mind blocked.
> >
> > Fairly new to a VBA so be gentle :-)
> >
> > Mary

Find next unused number in series (using SQL)

Non-equi self-join on the ID field. Find the Min() of the first one
lacking having a Null value in the field N+1.

This will give you all the IDs where there isn't an ID+1 value:


SELECT tblMain.ID
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+1 WHERE NextIDTable.ID Is Null;


Then take the Min() of the first column and add 1 to it:


SELECT Min(tblMain.ID) + 1 As NextIDToFillGap
FROM tblMain LEFT JOIN tblMain AS NextIDTable ON tblMain.ID =
NextIDTable.ID+1 WHERE NextIDTable.ID Is Null;


That will give you the next available unused ID number.


Many people don't know you can do non-equi joins in Access. You
can't do them in the QBE, but if you create it as an equi-join in
the QBE, you can then edit it in SQL view to change it to any type
of non-equi join you like.


Ron

>>Creating a table, each time a new CD is added, with all possible numbers
would slow down the process, wouldn't it.
Create a table of all possible numbers. This is a one time creation. The
easy way is to use autofill in Excel. Fill it with your highest possible
number - 10,000.

No SQL required ---
Create a query using your Number_Table and your CD_List_Table. In the
design view of the query place the Number_Table to the left of your
CD_List_Table. Click on the number field in the Number_Table and drag to the
number field in the CD_List_Table. Double click on the line created when you
drug from one to the other. Select the option the says to Include all
records from Number_Table and only those the match in CD_List_Table. Double
click on the number field of the Number_Table and in CD_List_Table. Set the
sort to ascending for the number field of the Number_Table. Use Null as
criteria for the CD_List_Table number field. In the icon that reads "All"
click in it and type 1.

Save the query. Run the query.
"mj" wrote:

> I really wish I knew more. I really didn't understand this solution. My SQL
> is really bad.
>
> Creating a table, each time a new CD is added, with all possible numbers
> would slow down the process, wouldn't it. As each class varies in how many
> CDs are in it. In some cases we have 10 CD's in a class and 1000 in another.
> I need to be able to look through the 1000 numbers to see if number 7 is
> missing and assign that to the newly added CD rather than 1001. I don't want
> to have to print out a report and eyeball this.
> Is there something in VBA that can get this to work (I didn't quite
> understand your SQL solution)?
>
>
> Mary
>
>
> ------------
> "KARL DEWEY" wrote:
>
> > Create a table of all possible numbers. In a query left join to your CD
> > table number field. Use Null as criteria for the CD table number field. Set
> > it to select the TOP 1 by either editing the SQL so it reads SELECT Top 1
> > YouAllNumberList, xxx, xxx etc.
> > Or in the design view change the icon that reds All to 1 by clicking in it
> > and typing 1.
> >
> > "mj" wrote:
> >
> > > I have a CD Library for work that I am numbering within separate 'classes'. I
> > > have them incrementing in each class by one using DMax. But sometimes I may
> > > move a CD to a new class. So when I move it it will get a new number from the
> > > new class and the old number is left 'open'. Now that CD's original number
> > > is available to be assigned to a new CD. How can I search the table to find a
> > > missing number?
> > > THis is probably a simple procedure, but I am mind blocked.
> > >
> > > Fairly new to a VBA so be gentle :-)
> > >
> > > Mary

Thanks Karl - this worked 1/2 way. I did get the query to display the
missing number, but NOW how do I get that top number assigned to a variable
to use in my form and/or write to my table.

Call I treat a query similar to a recordset and say .movefirst?? I did try
that but it didn't work, if it is possible I must need to make some changes
as a query really isnt' a recordset.

Thanks
Mary


"KARL DEWEY" wrote:

> >>Creating a table, each time a new CD is added, with all possible numbers
> would slow down the process, wouldn't it.
> Create a table of all possible numbers. This is a one time creation. The
> easy way is to use autofill in Excel. Fill it with your highest possible
> number - 10,000.
>
> No SQL required ---
> Create a query using your Number_Table and your CD_List_Table. In the
> design view of the query place the Number_Table to the left of your
> CD_List_Table. Click on the number field in the Number_Table and drag to the
> number field in the CD_List_Table. Double click on the line created when you
> drug from one to the other. Select the option the says to Include all
> records from Number_Table and only those the match in CD_List_Table. Double
> click on the number field of the Number_Table and in CD_List_Table. Set the
> sort to ascending for the number field of the Number_Table. Use Null as
> criteria for the CD_List_Table number field. In the icon that reads "All"
> click in it and type 1.
>
>
> > > "mj" wrote:
> > >
> > > > I have a CD Library for work that I am numbering within separate 'classes'. I
> > > > have them incrementing in each class by one using DMax. But sometimes I may
> > > > move a CD to a new class. So when I move it it will get a new number from the
> > > > new class and the old number is left 'open'. Now that CD's original number
> > > > is available to be assigned to a new CD. How can I search the table to find a
> > > > missing number?
> > > > THis is probably a simple procedure, but I am mind blocked.
> > > >
> > > > Fairly new to a VBA so be gentle :-)
> > > >
> > > > Mary

>>but NOW how do I get that top number assigned to a variable to use in my
form
Use an unbound textbox on your form and enter a select statement as record
source. Place an "=" sign in front of the SQL.

>>but NOW how do I get that top number assigned to a variable write to my table.
Try a couple of things. Set the default of your bound text box to the
unbound text box. If that fails you can use code or just manually type it in.

"mj" wrote:

> Thanks Karl - this worked 1/2 way. I did get the query to display the
> missing number, but NOW how do I get that top number assigned to a variable
> to use in my form and/or write to my table.
>
> Call I treat a query similar to a recordset and say .movefirst?? I did try
> that but it didn't work, if it is possible I must need to make some changes
> as a query really isnt' a recordset.
>
> Thanks
> Mary
>
>
> "KARL DEWEY" wrote:
>
> > >>Creating a table, each time a new CD is added, with all possible numbers
> > would slow down the process, wouldn't it.
> > Create a table of all possible numbers. This is a one time creation. The
> > easy way is to use autofill in Excel. Fill it with your highest possible
> > number - 10,000.
> >
> > No SQL required ---
> > Create a query using your Number_Table and your CD_List_Table. In the
> > design view of the query place the Number_Table to the left of your
> > CD_List_Table. Click on the number field in the Number_Table and drag to the
> > number field in the CD_List_Table. Double click on the line created when you
> > drug from one to the other. Select the option the says to Include all
> > records from Number_Table and only those the match in CD_List_Table. Double
> > click on the number field of the Number_Table and in CD_List_Table. Set the
> > sort to ascending for the number field of the Number_Table. Use Null as
> > criteria for the CD_List_Table number field. In the icon that reads "All"
> > click in it and type 1.
> >
> >
> > > > "mj" wrote:
> > > >
> > > > > I have a CD Library for work that I am numbering within separate 'classes'. I
> > > > > have them incrementing in each class by one using DMax. But sometimes I may
> > > > > move a CD to a new class. So when I move it it will get a new number from the
> > > > > new class and the old number is left 'open'. Now that CD's original number
> > > > > is available to be assigned to a new CD. How can I search the table to find a
> > > > > missing number?
> > > > > THis is probably a simple procedure, but I am mind blocked.
> > > > >
> > > > > Fairly new to a VBA so be gentle :-)
> > > > >
> > > > > Mary