In article <ar************ *************** *****@4ax.com>,
John Vinson <jvinson@STOP_S PAM.WysardOfInf o.comwrote:
On Thu, 10 Aug 2006 17:16:24 -0400, MN <mn@mn.mn.inval idwrote:
Hello,
I have a customer table and another table that I need to prepopulate
with special customer IDs, unique and not sequential. Is there a way to
configure Access to assign the customer ID to every record each time
that a record is either created for the first time or imported for the
first time? I would be importing into a temp table and then running an
update query to copy the data to the customer table. Another confusing
issue is that I will be updating the customer table through an update
query occasionally to just update existing customer data -- I don't want
that process to trigger issuing a new customer ID. Any thoughts?
Thanks.
I'm trying to imagine WHY you would need to create a table with a
bunch of empty "placeholde r" records with nothing but an ID. What's
the point? How is storing this redundant information of use?
John W. Vinson[MVP]
It's a school ID system. On a daily basis we import data via flat file
from an enterprise system. We need a way to assign a 16 digit number to
each imported record at the time of import. That 16 digit number is a
unique identifier and ends up encoded on their ID card. This is the
table structure that we are looking at:
Table 1: Customer table
school ID number
last name
first name
status
etc ...
Table 2: 16 digit numbers
16 digit number
assigned? yes or no
We need a way to automatically have Access assign a 16 digit number to
each record in the customer table at import or record creation and then
mark that number as assigned so it won't be issued again. The key part
is that we need the number to be grabbed from a pool of available
numbers that were provided to us (15 digits, sequential one after
another, plus a check digit, which makes the 16 digit number
non-sequential).
I realize this is complicated but I appreciate any recommendations .
Thanks.