Merging records from two tables
Q I want to merge two files of contact data in Paradox into one file. But the files have different fields. What's the best way to handle this?
-- Martha Bay
A It depends on how the files differ. If the tables have the same structures but one has more fields, you just add the shorter table to the longer one. Select Tools-Utilities-Add. In the Add Records From field in the Add dialogue box, choose the table with fewer fields; in the To field, choose the larger table.
If the tables' structures don't match, you'll have to go through a few more steps. Say you have two tables, Contacts and Phonebk. Here's how to merge them in Paradox, Access and Approach.
Paradox: Most fields in the two tables are the same, but contacts has a few more fields (Title, Fax, Date Entered), its Phone field is longer and it doesn't have a Notes field or key field, which let you check for duplicate records.
You could make a new table with all the fields from both contacts and phonebk, but it's easier to restructure the contacts table. Select Tools-Utilities-Restructure, select CONTACTS.DB (or click the Restructure button), and add a Notes field. Drag the Phone field up under Last Name and First Name, and make these first three fields keys. Then click Save.
If any fields in contacts are shorter than those in phonebk, you'll have to lengthen them (or trim the data later). But don't worry about non-matching field names (for example, Last in one table and Last Name in the other).
Now you can merge. Select File-New-Query, or right-click the Open Query button. Select New. Hold <Ctrl>, click CONTACTS.DB and PHONEBK.DB then click OK.
In the query window, click the blank area under "contacts.db" and select Insert from the menu. Then click the Join Tables button for each phonebk field you want to insert into contacts. For example, click Join Tables, click the box under the Last Name field in contacts, then click the box under the same field in phonebk. A highlighted join1 will appear in both fields. Click the Run Query button. Paradox displays a temporary table listing the records inserted from phonebk into contacts. Close that table and look at contacts, and you'll see the restructured table and the new records from phonebk.
Access: In Access, you use an append query to add records from one table to another. As in Paradox, you may need to change the structure of the table receiving new records. Open the table in Design mode and add, move or lengthen the fields as needed.
To merge the tables, click the Query tab in the Database window, then New. In the resulting dialogue box, click New Query to open the Add Table dialogue box, double-click Phonebk, then Close.
In the Select Query window, choose Query-Append, select Contacts from the Table Name list, and click OK. Drag the name of each field in phonebk that corresponds to a field in contacts into the query grid, and click the Run Query button.
Approach: Open the file you want to add to (contacts.apr). Select Create-Field Definition to add or expand fields. Then select File-Import Data and double-click phonebk.dbf.
In the Import Setup dialogue box, match up the fields by dragging them; click OK when done.
-- Celeste Robinson