I've worked mostly in Visual Basic and simple .html and was wondering if there was an easy method to perform the following operation:

1. Find all blank cells in "Pedigree" column
2. Once blank cell is found, insert the corresponding information from column "MgPedigree" (the information corresponds based on a second column in the table which must be identical to a corresponding column in an exterior table, from which the MgPedigree is coming)
3. List all the information found in a query

Here's what I have so far, I realize that many of these commands do not exist in .sql...wondering if anyone knows of a nice suitable replacement for my beloved "IF" command:

SELECT [Pedigree] FROM [TD Inventory Data]
WHERE [Pedigree] = NULL


INSERT [MgPedigree] FROM [TD2 Tree Data Erase Later]
IF "corresponding" [Accession] = "corresponding" [MgAccession] ...

DISPLAY [Accession]

Thanks in advance!

David,

Here is a simple select that reflects my understanding of the task:
SELECT
 case when pedigree IS NOT NULL then pedigree
 else mypedigree end AS 'pedigree',
 accession
FROM
 (
  SELECT
   pedigree,
   accession,
   mypedigree
  FROM
   td LEFT JOIN td1 ON td.accession=td1.myaccession
 ) AS t;

(1) What is the relation between 'accession' and 'myaccession' fields -- 1 to Many, M:1, or M:M?
(2) What if there are records in the first table (TD) that do not have corresponding records in the second table (TD1)? For example, TD.accession = 3, but there is no corresponding TD1.myaccession = 3. And if there are records in TD1 that do not have pairs in TD?
(3) The above statement achieves what you want (I think) by combining data from the two tables in the result, without the need to insert data from one table to another.

Hope this helps.

Dimitar

Thanks very much.

I seem to be having a operator error, apparently produced by the 'case when' command. I am working in Access by the way.

Sorry, I am not a MS Access user. The statement itself should work on most SQL 92 engines, but obviously not in Access.