assign number from table to record on import and creation

[MN]

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.

 

[pietlinden]

select a random set of customer records? Forget the technical part of
this. Explain what you want in plain old English. If you knew how to
do it, you wouldn't be posting. So just explain (1) what you have
(give an example record if necessary), and (2) what you want to happen
or the outcome you want.

If you run an update on the customer table, you won't create new IDs.
Where did you get the notion an update query would update anything but
the fields specified in the query?

If you want a random autonumber assigned to a record, create an
autonumber field in your final table and set the type or whatever to
Random instead of Increment.

 

[Lyle Fairfield]

Any thoughts?

There But for the Grace of God go I.

 

[pietlinden]

There But for the Grace of God go I.

Do you mean "only God can understand what that was about"? That's
certainly what I thought, but then I'm a mental midget.

 

[John Vinson]

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 "placeholder" records with nothing but an ID. What's
the point? How is storing this redundant information of use?

John W. Vinson[MVP]

 

[MN]

I'm trying to imagine WHY you would need to create a table with a
bunch of empty "placeholder" 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.