UID numbers in a table

[d9pierce]

Hi all,
I have many tables that are linked. I am trying to create a UID number
(UniqueID) becides the autonumber ID Primary Key!

I created an update Query to perform the following:

Update Query

Field: Expr1: CompanyUID
Table: Company
Update To: "111" &[CompanyID]

This produces the result Below on my table "Company"!
ID UID
1 1111
2 1112
3 1113
And so on.

Both are indexed YES (No Dups) and ID is Primary Key

Well, when applying this to my form after update event, DoCmd...
when I edit a record, it slows down the process something fierce! I am
sure it is searching through 23,000 records each time I edit or add a
record.

Is there a way I can do this without slowing down my db? I only need
this to update a new record when added. Maybe there is something else
that will do this function automatically?
I am not real familiar with these types of functions and how to make
them work smoothly!

I would really apriociate any suggestions and of course, examples are
so wonderful!
Thanks so mcuh,

Dave

 

[Ron Weiner]

Looks like you need to limit the number of rows updated to only those that
need to be updated. A guess at what the query ought to look like is below:

UPDATE Company
SET CompanyUID = '111' & CompanyID
WHERE CompanyUID Is Null

This will only update the records where the CompanyUID column is null.
Presumably this would the only the new record that you just created.
However I cant imagine why you want to store this value in the table at all,
as this value can be easily computed on the fly whenever you need it.

 

[d9pierce]

Thanks Ron,

Any idea of what type of brackets go around this statement? I am not
very good with Qry and Sql yet. I cant figure out what goes around the
last part IsNull?

Thanks,
Dave

 

[Ron Weiner]

Assuming the table name is Company and the Column Name in Company is named
CompanyUID, and there is a Column CompanyID in the Company table then the
statement that I gave you needs no brackets. Open a new query and go to Sql
View and paste in the statement below and execute it.

UPDATE Company
SET CompanyUID = '111' & CompanyID
WHERE CompanyUID Is Null

This will update all of the rows where the CompanyUID column is null in
your table. What I do not understand is why you want to do this. These
values can be easily be computed on the fly whenever you need them. It
makes no sense to store these values in the table. I suspect your Sql
inexperience is leading you in the wrong direction.

Consider the following Sql statement

SELECT CompanyID, '111' & CompanyID as CompanyUID
FROM Company
ORDER BY CompanyID