How to combine multiple tables (all are with unique ID names) into one table?

[Ruth Liew]

I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest. Thanks.

[pbaldy]

The data should all be in one table, with a field for category in my opinion. If you want to keep it like it is, you can use a UNION query to pull them all together.

[Dal Jeanis]

Unless the categories have vastly different information requirements, Paul is right, that should be one table with a field to indicate which category of book is involved. If there is a significant difference (lots of distinct fields required) for some categories of books, the differences (only) can be stored in a second table. If there are two distinct sets of data, for instance nonfiction and fiction books having vastly different information, then it's your choice whether to have three tables (one for all books fields, one for fiction supplemental fields, one for nonfiction supplemental fields). or to have two tables (one for fiction, one for nonfiction).

If you want to merge the tables, you can build a query that will append each of the six tables onto a seventh table. For each one, have an OLDID field, type text, to hold the old F1, F2 etc, a category field, and an autokey field to give each record a unique key. The process will take some time, and should be repeated in a test copy of the database until you have all the steps working to make the change.

[June7]

I concur that one table would be best.

But if you must maintain separate tables, use a UNION query to combine into a single dataset for report output.