Assigning unique identifier to unique authors

[Guest]

Hi, I have a table that lists authors by 2 fieds (last name and first name)
among other information, like journal title, etc. What I need to do is
assign a unique identifier to each unique author. There are many duplicate
author names because they have authored more than one article listed in my
table. I need to find unique names and then assign some sort of unique
(auto) number.

Thanks, in advance. I'm new to Access and queries!!

Bluesky

 

[Guest]

1. Make a complete backup of your database.

2. Create a query based on just the FirstName and LastName fields.

3. Change that query to a Totals query. Design View, View, Totals. Make sure
both fields say Group By. Or open the query in SQL view and change SELECT to
SELECT DISTINCT .

4. Run the query and check the results. If you like it, go back to Design
view and change the query to a Make Table query. Call the new table Authors
and run the query.

NOTE: There will be problems if you have two different authors with the
same name. For example one Steven King is more than enough!

5. Open up the new Authors table and add a field called Authors_ID. Make the
data type Autonumber. Save the changes. Next make the Authors_ID table the
primary key for the Authors table. Then make the combination of FirstName and
LastName fields a unique index. You could also add other fields such as the
author's address, date of birth, etc.

6. Next open the original table in design view. Add a field called
Authors_ID and make it a number field.

7. Create a query that joins the two tables. Update the Authors_ID field in
the new table with the Authors_ID data in the Authors table.

8. Open up the original table and delete the FirstName and LastName fields.
No longer needed.

9. Open up the Relationships window and join the two tables with
Referiential Integrity enabled.

10. Open up all forms, reports, and queries based on the original table and
fix them so that the see the needed information in the Authors table.

 

[Guest]

Hi Jerry,

Thanks so much for your reply!

one quick, very naive question:

for number 7, I've made a query that joins the two table, by the same
authors_id, and I did check the relationship and enable referiential
integrity.

But, how do I update the authors_id in each table per #7, is that just
running a query? When I do, I don't get any results. Also, what about join
properties with the three choice?

sorry to ask so many questions! I so appreicate your earlier reply.

Thanks,
bluesky

 

[Guest]

Hi Jerry,

Here is an update. The "autonumber" does show up in the new table (Authors),
but how do I get that field to show up in the original table?

Thanks so much for your time!

 

[Guest]

Hi,

The SQL statement would look something like below. Change the table named
Books to your actual table name. Double check the field names also.

UPDATE Authors INNER JOIN Books
ON (Authors.LastName = Books.LastName)
AND (Authors.FirsName = Books.FirsName)
SET Books.Author_ID = [Authors]![Author_ID];

 

[Guest]

Hi Jerry,

Thanks so much!! It worked!!

-bluesky

Hi,

The SQL statement would look something like below. Change the table named
Books to your actual table name. Double check the field names also.

UPDATE Authors INNER JOIN Books
ON (Authors.LastName = Books.LastName)
AND (Authors.FirsName = Books.FirsName)
SET Books.Author_ID = [Authors]![Author_ID];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry,

Here is an update. The "autonumber" does show up in the new table (Authors),
but how do I get that field to show up in the original table?

Thanks so much for your time!