It's always wise to check the state of your transformed data. There's no substitute for visual inspection but there are a variety of ways to check your data using SQL.

For example, there should be no orphaned member records. Since we've migrated from a flat-table database that contained all the original data, finding an id in the members table with no corresponding record in the member accreditations table would indicate that something was wrong. The following SQL statement will return all records in the members table that don't have matching records in the member accreditations table:

SELECT `t`.`id` ,`t`.`firstname`,`t`.`lastname`  
  FROM `members` `t` 
  LEFT JOIN `memberaccreditations` `tma` 
  ON `t`.`id` = `tma`.`memberid`  
  WHERE ISNULL(`tma`.`memberid`);      

If the above SELECT statement returns an empty set, there are no orphaned member records.

An easy way to reuse this SQL statement is to save it as a view. To do this using Query Browser, make sure the association database is active, then right click on any one of the tables in the Schemata window and choose the Create View option. Clicking Okay after entering a view name opens a new tab displaying the basic syntax for creating a view. Paste the preceding SQL statement into the AS clause and execute the query. After refreshing the schemata the new view should show up. You can view the record set associated with this view in exactly the same way that you would view the record set associated with a table.

To check that there are no orphaned records in the member accreditations table execute the following query:

SELECT `tma`.`certification`,
  `tma`.`memberid`,`tma`.`certificationnumber`,
  `tma`.`expirydate` 
  FROM (`memberaccreditations` `tma` 
  LEFT JOIN `members` `t` ON ((`tma`.`memberid` = `t`.`id`))) 
  WHERE ISNULL(`t`.`id`);     

Again, to save this SQL statement, convert it to a view using the procedure described above.

There are also various other ways of querying your records to verify the data. For example, if all certification numbers in the member accreditations table are meant to be unique, executing the following query would determine if there are duplicates:

SELECT COUNT(t.`certificationnumber`), t.`certificationnumber` 
  FROM memberaccreditations t 
  GROUP BY (t.`certificationnumber`)
  HAVING COUNT(t.`certificationnumber`) > 1;     

Checking the number of records in the memberaccreditations table provides further assurance of the integrity of your data. The number should exactly match the number of records in the alldata table.

If you notice discrepancies in the data and wish to update records you can do this from within Query Browser. Click the Start Editing button and then select the record you wish to change and place the cursor in the column you wish to change. When you are finished editing click the Apply Changes button.

Once you're satisfied with the integrity of the data, drop the alldata table. This is easily done by right clicking the table and choosing the Drop option. Before exiting Query Browser make sure that you save the script file of all the queries.