How do I create a duplicate copy of a table?

[JRoss]

However, I created a table containing information on books in a private library, as it was being organized. Many books turned out to have duplicates which were stored elsewhere, but noted in a field marked "Notes." At some point, I did manage to make a table, from the original table, that contained most of these books, and I was able to use it as a table, entering data into fields, etc. Now that I have re-organized the duplicates, to get multiple copies together, and have entered this data into the main database, I'd like to make another copy of only those books with duplicates. But I can't remember how or find any clues in my ACCESS 2000 books. A query/report won't do it, as that doesn't give me access to the records. And I could re-enter the data, but that seems to defeat the purpose of the database to start with! Can anyone help?

[John_G]

To just copy a table, highlight the table name in the table list, then use Ctrl-C (Copy) and Ctrl-V (paste). It will ask you for the new table name, and whether the new table should be created including the existing data or not.

[JRoss]

John..that was way too easy. Now I just have to delete anything without a duplicate copy indicated in notes!

Seems like I should have done something different when I first set up the database, so that I had a table for "duplicate copy" that linked to the original book title. Can I go back and do that now? Every book has a ID which could be used as a 'key' or whatever you call it. At the time, I didn't know anything about databases, although I had worked through a tutorial. But it seemed way too complicated. Famous last words, yes?

[John_G]

What you have is a many-to-many relationship between books and libraries: one library can contain one or more books, and one book (title) can be in one or more libraries.

This arrangement is implemented (is its simplest form) with three tables, e.g.:

tblBook - information on each distinct book, with the BookID as the key)
tblLibrary - information on each library with libraryID as the key
tblLibraryBook - the join table, which tells you which books are in which libraries.

The last table is the important one, and at its simplest will have three or four fields:

RecordID - A unique ID for each record in this table. Not mandatory, but a good idea if you will be using this table in relationships
BookID - The ID of a book in tblBook
LibraryID - The ID of a library in tblLibrary
CopyCount - The number of copies of that book in that library. not required if you know for sure it's always 1.

This schema eliminates the need for multiple tables and "duplicate notes"

Post back if you would like further clarification.

[JRoss]

John: Thanks, I'll have to play with this. I take it that the fields for the tblLibrary would essentially consist of the names of the two libraries or their ID, as there is no other info I can think of that I'd need, and this would be a field for input in the Data Enty Form? CopyCount would also need to be a field on the Form, yes?

But will this result in a Table that just lists the duplicates?

Or is that better accomplished with a Report, with all the fields, and I should quit complicating life? IE, add a field for CopyCount to the original table and then make a query that takes any book with 2 or more and creates a report.

Seems like either way I'm going to end up re-entering a lot of data. So be it. The advantages of really thinking through the process before you start, which isn't always possible. oh, well....