Assign result of a query to a variable

G

Guest

Hello,

I have an application where I need to generate sequential number in the
range 1-99999. I have a table which will have one column storing these
numbers.

tblSeqNumbers
SeqNum Integer PK

I need to:
1. Query the table to determine the highest number.
Either of these will work but not sure if one is better than the other.
A. Select count (*) from tblSeqNumbers
B. SELECT max (SeqNum ) FROM tblSeqNumbers;
2. Store the value returned to a variable.
3. Add 1 to the number returned.
4. Insert the new value back into the table.

Can anyone tell me how to assign the value returned from the query to a
variable?

TIA,
Rich
 
J

John Spencer

If you have the numbers 1 to 10 in the table you can use a query like the
following to add numbers up to one million. And if you want to you can
extend it even further by adding a millions table to the query.


PARAMETERS [Increase Max # to:] Long;
INSERT INTO IntegerTable ( IntegerField )
SELECT
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)
AS NewNumber
FROM IntegerTable, IntegerTable AS I_10, IntegerTable AS I_100, IntegerTable
AS I_1000, IntegerTable AS I_10000, IntegerTable AS I_100000
WHERE
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)>(Select
Max(IntegerField) FROM IntegerTable) And
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)<=[Increase
Max # to:] AND ((IntegerTable.IntegerField)<11) AND ((I_10.IntegerField)<11)
AND ((I_100.IntegerField)<11) AND ((I_1000.IntegerField)<11) AND
((I_10000.IntegerField)<11) AND ((I_100000.IntegerField)<11);


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Try this --
INSERT INTO tblSeqNumbers ( SeqNum )
SELECT Max(tblSeqNumbers.SeqNum)+1 AS MaxOfSeqNum
FROM tblSeqNumbers;
 
G

Guest

Thanks for the reply John. However, english please (LOL), I'm just past
being a rookie.
After the "Select", you're multiplying that value by 10 and subtracting 1,
repeatedly until you max that value out but keeping it less than 100K.
(Table should only go up to 99999).

I don't understand the multiple tables after "AS".
I don't understand the values being computed in the where clause.

I'm sorry, the logic here is just way over my head. Can you simplify the
explaination?

Rich






John Spencer said:
If you have the numbers 1 to 10 in the table you can use a query like the
following to add numbers up to one million. And if you want to you can
extend it even further by adding a millions table to the query.


PARAMETERS [Increase Max # to:] Long;
INSERT INTO IntegerTable ( IntegerField )
SELECT
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)
AS NewNumber
FROM IntegerTable, IntegerTable AS I_10, IntegerTable AS I_100, IntegerTable
AS I_1000, IntegerTable AS I_10000, IntegerTable AS I_100000
WHERE
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)>(Select
Max(IntegerField) FROM IntegerTable) And
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)<=[Increase
Max # to:] AND ((IntegerTable.IntegerField)<11) AND ((I_10.IntegerField)<11)
AND ((I_100.IntegerField)<11) AND ((I_1000.IntegerField)<11) AND
((I_10000.IntegerField)<11) AND ((I_100000.IntegerField)<11);


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

rich said:
Hello,

I have an application where I need to generate sequential number in the
range 1-99999. I have a table which will have one column storing these
numbers.

tblSeqNumbers
SeqNum Integer PK

I need to:
1. Query the table to determine the highest number.
Either of these will work but not sure if one is better than the other.
A. Select count (*) from tblSeqNumbers
B. SELECT max (SeqNum ) FROM tblSeqNumbers;
2. Store the value returned to a variable.
3. Add 1 to the number returned.
4. Insert the new value back into the table.

Can anyone tell me how to assign the value returned from the query to a
variable?

TIA,
Rich
Click to expand...
Click to expand...
 
G

Guest

Hi Karl,

I thank you also for your reply.

You are selecting the max value from the table and immediatly adding 1 to it
giving the new number which will be inserted back into the table for the next
time.

Assuming the table has 1 row with a value of 1, the select statement will
return a value of 2. Is that correct? At least that is what my test run is
doing.

This of course will be followed by another insert increasing the max value
by 1.

Is this correct? This looks pretty simple and effective. If so, just
another follow-up.

HOw do I assign the value returned from the select to a variable so that I
can insert it into a field on the form? Can I just add a "AS NewNumber"
clause and set the field on the form = NewNumber? This is what I have tested
but with 1 little problem. The field must have 5 digits. I'm trying to
figure out how to pad the number with zeroes so that I end up with 00001,
00002, etc.

I really appreciate the help provided by everyone on the group as I continue
to learn. You are a GREAT help!!

Thanks,
Rich
 
G

Guest

HOw do I assign the value returned from the select to a variable so that I
can insert it into a field on the form?
Use the default property of the form text box.
You are describing a text field so use this query --
INSERT INTO tblSeqNumbers ( SeqNum )
SELECT Right("0000" & Max([tblSeqNumbers].[SeqNum])+1,5) AS MaxOfSeqNum
FROM tblSeqNumbers;
 
J

John Spencer

If you take that query statement and replace
IntegerTable with the name of your table and
IntegerField with the name of your field
you can then execute the query.

It will ask you for the Maximum number. Input 99999

Assuming that you have at least the number 1 to 10 in your table already.

What is does is alias the integer table 5 times (so you are working with
a total of 6 copies of the table. It takes the numbers 1 to 10 in the
first copy and adds them to the numbers 0 to 9 * 10 for the second copy
and adds them to the numbers 0 to 9 * 100 in the third column, etc.

So it generates all the numbers from 1 to 9999999 then limits the
records returned to those that are between the maximum already in the
table and the cut-off number you have input.

Finally it inserts any records that meet the criteria into the table.



PARAMETERS [Increase Max # to:] Long;
INSERT INTO IntegerTable ( IntegerField )
SELECT
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+
(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)

AS NewNumber
FROM IntegerTable, IntegerTable AS I_10, IntegerTable AS I_100,
IntegerTable
AS I_1000, IntegerTable AS I_10000, IntegerTable AS I_100000
WHERE
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)
+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)
+(([I_100000].[IntegerField]-1)*100000)>
(Select Max(IntegerField) FROM IntegerTable) And
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)
+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+
(([I_100000].[IntegerField]-1)*100000)<=[Increase Max # to:] AND
((IntegerTable.IntegerField)<11) AND ((I_10.IntegerField)<11)
AND ((I_100.IntegerField)<11) AND ((I_1000.IntegerField)<11) AND
((I_10000.IntegerField)<11) AND ((I_100000.IntegerField)<11);

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the reply John. However, english please (LOL), I'm just past
being a rookie.
After the "Select", you're multiplying that value by 10 and subtracting 1,
repeatedly until you max that value out but keeping it less than 100K.
(Table should only go up to 99999).

I don't understand the multiple tables after "AS".
I don't understand the values being computed in the where clause.

I'm sorry, the logic here is just way over my head. Can you simplify the
explaination?

Rich






John Spencer said:
If you have the numbers 1 to 10 in the table you can use a query like the
following to add numbers up to one million. And if you want to you can
extend it even further by adding a millions table to the query.


PARAMETERS [Increase Max # to:] Long;
INSERT INTO IntegerTable ( IntegerField )
SELECT
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)
AS NewNumber
FROM IntegerTable, IntegerTable AS I_10, IntegerTable AS I_100, IntegerTable
AS I_1000, IntegerTable AS I_10000, IntegerTable AS I_100000
WHERE
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)>(Select
Max(IntegerField) FROM IntegerTable) And
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)<=[Increase
Max # to:] AND ((IntegerTable.IntegerField)<11) AND ((I_10.IntegerField)<11)
AND ((I_100.IntegerField)<11) AND ((I_1000.IntegerField)<11) AND
((I_10000.IntegerField)<11) AND ((I_100000.IntegerField)<11);


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

rich said:
Hello,

I have an application where I need to generate sequential number in the
range 1-99999. I have a table which will have one column storing these
numbers.

tblSeqNumbers
SeqNum Integer PK

I need to:
1. Query the table to determine the highest number.
Either of these will work but not sure if one is better than the other.
A. Select count (*) from tblSeqNumbers
B. SELECT max (SeqNum ) FROM tblSeqNumbers;
2. Store the value returned to a variable.
3. Add 1 to the number returned.
4. Insert the new value back into the table.

Can anyone tell me how to assign the value returned from the query to a
variable?

TIA,
Rich
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,

This is a lot easier if your IntegerTable only contains the numbers 0 - 9.
I also find it easier to read by aliasing the tables as Ones, Tens, Hundreds,
Thousands.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


John Spencer said:
If you have the numbers 1 to 10 in the table you can use a query like the
following to add numbers up to one million. And if you want to you can
extend it even further by adding a millions table to the query.


PARAMETERS [Increase Max # to:] Long;
INSERT INTO IntegerTable ( IntegerField )
SELECT
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)
AS NewNumber
FROM IntegerTable, IntegerTable AS I_10, IntegerTable AS I_100, IntegerTable
AS I_1000, IntegerTable AS I_10000, IntegerTable AS I_100000
WHERE
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)>(Select
Max(IntegerField) FROM IntegerTable) And
[IntegerTable].[integerField]+(([I_10].[IntegerField]-1)*10)+(([I_100].[integerField]-1)*100)+(([I_1000].[IntegerField]-1)*1000)+(([I_10000].[Integerfield]-1)*10000)+(([I_100000].[IntegerField]-1)*100000)<=[Increase
Max # to:] AND ((IntegerTable.IntegerField)<11) AND ((I_10.IntegerField)<11)
AND ((I_100.IntegerField)<11) AND ((I_1000.IntegerField)<11) AND
((I_10000.IntegerField)<11) AND ((I_100000.IntegerField)<11);


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

rich said:
Hello,

I have an application where I need to generate sequential number in the
range 1-99999. I have a table which will have one column storing these
numbers.

tblSeqNumbers
SeqNum Integer PK

I need to:
1. Query the table to determine the highest number.
Either of these will work but not sure if one is better than the other.
A. Select count (*) from tblSeqNumbers
B. SELECT max (SeqNum ) FROM tblSeqNumbers;
2. Store the value returned to a variable.
3. Add 1 to the number returned.
4. Insert the new value back into the table.

Can anyone tell me how to assign the value returned from the query to a
variable?

TIA,
Rich
Click to expand...
Click to expand...
 
G

Guest

Good Morning Karl,

Just making sure I understand. Will this query pad with 1-4 leading zeroes,
as needed? Please explain just so I'm sure. This is definitely one to
remember.

Thanks very much,
Rich


KARL DEWEY said:
can insert it into a field on the form?
Use the default property of the form text box.
You are describing a text field so use this query --
INSERT INTO tblSeqNumbers ( SeqNum )
SELECT Right("0000" & Max([tblSeqNumbers].[SeqNum])+1,5) AS MaxOfSeqNum
FROM tblSeqNumbers;

--
KARL DEWEY
Build a little - Test a little


rich said:
Hi Karl,

I thank you also for your reply.

You are selecting the max value from the table and immediatly adding 1 to it
giving the new number which will be inserted back into the table for the next
time.

Assuming the table has 1 row with a value of 1, the select statement will
return a value of 2. Is that correct? At least that is what my test run is
doing.

This of course will be followed by another insert increasing the max value
by 1.

Is this correct? This looks pretty simple and effective. If so, just
another follow-up.

HOw do I assign the value returned from the select to a variable so that I
can insert it into a field on the form? Can I just add a "AS NewNumber"
clause and set the field on the form = NewNumber? This is what I have tested
but with 1 little problem. The field must have 5 digits. I'm trying to
figure out how to pad the number with zeroes so that I end up with 00001,
00002, etc.

I really appreciate the help provided by everyone on the group as I continue
to learn. You are a GREAT help!!

Thanks,
Rich
Click to expand...
Click to expand...
 
G

Guest

1 - Max([tblSeqNumbers].[SeqNum]) ---- finds the maximum value
2 - Max([tblSeqNumbers].[SeqNum])+1 ---- adds one to the maximum value
3 - "0000" & Max([tblSeqNumbers].[SeqNum])+1 ---- concatenates four zeros
to the left of the maximum value plus one
4 - Right("0000" & Max([tblSeqNumbers].[SeqNum])+1,5) extracts the right
most five characters from the srting

--
KARL DEWEY
Build a little - Test a little


rich said:
Good Morning Karl,

Just making sure I understand. Will this query pad with 1-4 leading zeroes,
as needed? Please explain just so I'm sure. This is definitely one to
remember.

Thanks very much,
Rich


KARL DEWEY said:
HOw do I assign the value returned from the select to a variable so that I
Click to expand...
can insert it into a field on the form?
Use the default property of the form text box.
The field must have 5 digits. I'm trying to figure out how to pad the number with zeroes so that I end up with 00001, 00002, etc.
Click to expand...
You are describing a text field so use this query --
INSERT INTO tblSeqNumbers ( SeqNum )
SELECT Right("0000" & Max([tblSeqNumbers].[SeqNum])+1,5) AS MaxOfSeqNum
FROM tblSeqNumbers;

--
KARL DEWEY
Build a little - Test a little


rich said:
Hi Karl,

I thank you also for your reply.

You are selecting the max value from the table and immediatly adding 1 to it
giving the new number which will be inserted back into the table for the next
time.

Assuming the table has 1 row with a value of 1, the select statement will
return a value of 2. Is that correct? At least that is what my test run is
doing.

This of course will be followed by another insert increasing the max value
by 1.

Is this correct? This looks pretty simple and effective. If so, just
another follow-up.

HOw do I assign the value returned from the select to a variable so that I
can insert it into a field on the form? Can I just add a "AS NewNumber"
clause and set the field on the form = NewNumber? This is what I have tested
but with 1 little problem. The field must have 5 digits. I'm trying to
figure out how to pad the number with zeroes so that I end up with 00001,
00002, etc.

I really appreciate the help provided by everyone on the group as I continue
to learn. You are a GREAT help!!

Thanks,
Rich

:

Try this --
INSERT INTO tblSeqNumbers ( SeqNum )
SELECT Max(tblSeqNumbers.SeqNum)+1 AS MaxOfSeqNum
FROM tblSeqNumbers;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have an application where I need to generate sequential number in the
range 1-99999. I have a table which will have one column storing these
numbers.

tblSeqNumbers
SeqNum Integer PK

I need to:
1. Query the table to determine the highest number.
Either of these will work but not sure if one is better than the other.
A. Select count (*) from tblSeqNumbers
B. SELECT max (SeqNum ) FROM tblSeqNumbers;
2. Store the value returned to a variable.
3. Add 1 to the number returned.
4. Insert the new value back into the table.

Can anyone tell me how to assign the value returned from the query to a
variable?

TIA,
Rich
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Gaps in ordered series 1
Concatenate Date and Number field for Append Query 0
SELECT a field value into a user variable. 3
Returning A Varibale from a query 1
Make table query: [Enter 1] AS Test 1
Using Variable as criteria 4
Assign the result of this simple query to a variable 2
Query that will assign a sequence number 1

Top