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
"Jerry Whittle" wrote:
> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "bluesky" wrote:
>
> > 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
|
Similar Threads
