By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Automatically associate value from one table to another table

P: n/a
Hello All

Can anybody please help me out with this small and ofcourse a crucial
problem for me.

I have two tables in the database system.

Table1 has following fields
Field1 - SequenceNumber (Auto number)
Field2 - RandomNumber

There are about 17,000 alphanumeric customized random numbers in the
second field (Field2), which was generated using some other programs
and I have been necessarily asked to use these random numbers.

Table2 has about 20 fields,
Field1
Field2
Field3
Field4
Field5
Field6
Field7

....

Field20

One of the fields in Table2 say Field2 should have RandomNumber(value
of Field2 of Table1). For each and every record, there has to be a
unique random number associated to it. My problem is - When user
enters a new record in Table2, automatically the next available unused
random number (from Table1) should be associated to Field2 of Table2
from Field2 of Table1.
I used the following logic to solve the problem, but unfortunately
it's not working.

1. Select RandomNumber(Field2) from the last record in Table2.
2. Pass the RandomNumber to the Table1 and get the SequenceNumber
associated with that RandomNumber
3. Add +1 to the sequence number and fetch the random number for the
next sequence number from Table1
4. Insert this new RandomNumber back into Table2 for the new record.
Can somebody let me know as to where am I going wrong with this logic.
And if there is some other way to get this done, I will be happy to
know.
I would really appreciate your help.

THanks in advance

Regards
Anita
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The logic seems logical, but it does assume that:

a. adding 1 will always get the next SequenceNumber - which is correct only
if there are no skips.
b. Table1 Field2 values are unique (you said that Table2 Field2 values are,
but ...)

What exactly is "going wrong"?
Darryl Kerkeslager

"anita" <ta*******@yahoo.com> wrote:
Table1 has following fields
Field1 - SequenceNumber (Auto number)
Field2 - RandomNumber

There are about 17,000 alphanumeric customized random numbers in the
second field (Field2), which was generated using some other programs
and I have been necessarily asked to use these random numbers. Table2 has about 20 fields, One of the fields in Table2 say Field2 should have RandomNumber(value
of Field2 of Table1). For each and every record, there has to be a
unique random number associated to it. My problem is - When user
enters a new record in Table2, automatically the next available unused
random number (from Table1) should be associated to Field2 of Table2
from Field2 of Table1.
I used the following logic to solve the problem, but unfortunately
it's not working.

1. Select RandomNumber(Field2) from the last record in Table2.
2. Pass the RandomNumber to the Table1 and get the SequenceNumber
associated with that RandomNumber
3. Add +1 to the sequence number and fetch the random number for the
next sequence number from Table1
4. Insert this new RandomNumber back into Table2 for the new record.


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.