Automate Numbers - 2nd Request

[Lisa W.]

I am looking for advice for the best way to solve a problem.

I am developing a system of telephony extensions etc at a
workplace, some sites may have more than 200.

I am trying to automate entering all the extension numbers, rather than
manually inputting them. I also have a format for the range of extensions,
but don't know if Access will allow me to give an example and then create the
extension for me automatically in sequence.

The format will be something like 256 0 00 00; it's called a TN Range

1st set of numbers ( up to 256)
2nd number (can be 0 or 1)
3rd set of numbers (0 to 15)
4th set of numbers (0 to 31)

**Not sure how difficult this will be, but very necessary because we have
several ranges of numbers that would have to be manually entered.**


Thanks,
Lisa W.

 

[Minton M]

I am looking for advice for the best way to solve a problem.

I am developing a system of telephony extensions etc at a
workplace, some sites may have more than 200.

I am trying to automate entering all the extension numbers, rather than
manually inputting them. I also have a format for the range of extensions,
but don't know if Access will allow me to give an example and then create the
extension for me automatically in sequence.

The format will be something like 256 0 00 00; it's called a TN Range

1st set of numbers ( up to 256)
2nd number (can be 0 or 1)
3rd set of numbers (0 to 15)
4th set of numbers (0 to 31)

**Not sure how difficult this will be, but very necessary because we have
several ranges of numbers that would have to be manually entered.**

Thanks,
Lisa W.

Lisa,

If I understand you correctly, you should create a table with the four
parts of the TN ranges and set the validation rules on each field to
match the ranges above. I don't know what your logic is for creating
extensions from these numbers but you could then use either a VBA
function or query (depending on the complexity of the formula) to
create the extension.

If you can provide the logic to create the extension (and let me know
if I'm reading your request correctly), I can help further.

-- James

 

[Lisa W.]

You are reading my request accurately. Ranges are a part of the telephone
programming and our goal is not to manually enter all possible ranges because
there are so many. Was hoping there was a way for the system to look at a
sample range and create others based on set criteria????

Thanks,
Lisa W.

 

[Lance]

We're still at the same problem we were at the last time you asked this
question.
What is your "set criteria?"

If you get that clearly defined, it should be possible to write code to
generate most of the range data.

 

[John W. Vinson]

I am looking for advice for the best way to solve a problem.

I am developing a system of telephony extensions etc at a
workplace, some sites may have more than 200.

I am trying to automate entering all the extension numbers, rather than
manually inputting them. I also have a format for the range of extensions,
but don't know if Access will allow me to give an example and then create the
extension for me automatically in sequence.

The format will be something like 256 0 00 00; it's called a TN Range

1st set of numbers ( up to 256)
2nd number (can be 0 or 1)
3rd set of numbers (0 to 15)
4th set of numbers (0 to 31)

**Not sure how difficult this will be, but very necessary because we have
several ranges of numbers that would have to be manually entered.**


Thanks,
Lisa W.

One way to help with this process is to create a little auxiliary table,
Numbers, with one Long Integer field N. Fill it with numbers from 0 through
the largest number you'll ever need in a sequence (256 in this case, I use
either 10000 or 65536 - no reason to be stingy). Make N the primary key so
that it's indexed.

Your TN Range field - I'd name it TN_Range, since blanks aren't ideal in
fieldnames - should be of Text datatype.

It's not clear which portion of this field should be entered manually, and
which should be autoincremented. What I'd do is use a Form with four unbound
textboxes for the four components, and an Append query based on Numbers. For
example, if you want to increment the fourth set and leave the first three
components as entered by the user, you could use an append query

INSERT INTO tablename([TN_Range])
SELECT Format(Forms!YourForm!txtFirstPart,"000")
& Forms!YourForm!txtSecondPart
& Format(Forms!YourForm!txtThirdPart,"00")
& Format(Forms!YourForm!txtFourthPart + N, "00")
FROM Numbers
WHERE Forms!YourForm!txtFourthPart + N <= 31;


John W. Vinson [MVP]

 

[Lisa W.]

Thanks John. I need each set of numbers to increment. I'll give it a try.

I am looking for advice for the best way to solve a problem.

I am developing a system of telephony extensions etc at a
workplace, some sites may have more than 200.

I am trying to automate entering all the extension numbers, rather than
manually inputting them. I also have a format for the range of extensions,
but don't know if Access will allow me to give an example and then create the
extension for me automatically in sequence.

The format will be something like 256 0 00 00; it's called a TN Range

1st set of numbers ( up to 256)
2nd number (can be 0 or 1)
3rd set of numbers (0 to 15)
4th set of numbers (0 to 31)

**Not sure how difficult this will be, but very necessary because we have
several ranges of numbers that would have to be manually entered.**


Thanks,
Lisa W.

One way to help with this process is to create a little auxiliary table,
Numbers, with one Long Integer field N. Fill it with numbers from 0 through
the largest number you'll ever need in a sequence (256 in this case, I use
either 10000 or 65536 - no reason to be stingy). Make N the primary key so
that it's indexed.

Your TN Range field - I'd name it TN_Range, since blanks aren't ideal in
fieldnames - should be of Text datatype.

It's not clear which portion of this field should be entered manually, and
which should be autoincremented. What I'd do is use a Form with four unbound
textboxes for the four components, and an Append query based on Numbers. For
example, if you want to increment the fourth set and leave the first three
components as entered by the user, you could use an append query

INSERT INTO tablename([TN_Range])
SELECT Format(Forms!YourForm!txtFirstPart,"000")
& Forms!YourForm!txtSecondPart
& Format(Forms!YourForm!txtThirdPart,"00")
& Format(Forms!YourForm!txtFourthPart + N, "00")
FROM Numbers
WHERE Forms!YourForm!txtFourthPart + N <= 31;


John W. Vinson [MVP]

 

[Lisa W.]

Lance,

I have given my criteria. I need each of these sets of numbers to be
automatically increment. I'm not sure what other information you are looking
for?? Thanks in advance for any assistance.

 

[Minton M]

Lance,

I have given my criteria. I need each of these sets of numbers to be
automatically increment. I'm not sure what other information you are looking
for?? Thanks in advance for any assistance.

Lisa,

If you just need a table containing all the possible combinations of
these numbers, it would be very easy to do in an Excel spreadsheet
(using FillDown) and then copying/pasting to Access. The only thing
I'm not clear on is the logic used to create the extension. For
example (in really bad code):

Function MakeMyExtension(a,b,c,d)
MakeMyExtension = a+b+(c*2)/d
End Function

....which is course is nonsensical but if you can encapsulate your
logic into a function like this, you can call it in a query to
automate the extensions.

I hope this helps!
James

 

[John W. Vinson]

Thanks John. I need each set of numbers to increment. I'll give it a try.

Start with 000 0 00 00, increment the fourth chunk to 31, then start over with
000 0 01 00 and increment that to 31, and so on? If so it will take a bit more
code and/or a more complex query. It can be done but I'd be inclined to put it
into VBA.

John W. Vinson [MVP]

 

[Lance]

That's what happens with individual numbers AFTER the sets/ranges/groups are
created. The only criteria you've given for defining these is that you'd
like to create one and have the rest use it as a template ( which isn't
really a viable approach ).

Do the 4 numbers in your TN have any impact on how you want the number
ranges set up? If so, how?

Or... Are you looking to just ignore the 4 sets and treat it as a continous
8 digit number, with each range having a set amount of sequential numbers
reserved for it?

What are your "ranges" for and how many where there be?

Are there number ranges for sub departments which should be contained within
larger number ranges for their parent department ( Example: AP is part of
Finance, AP is assigned numbers X3100-X3200 while Finance as a whole gets
X3000-X3999 )