Correct method for comparing data in two tables

[GNorman]

I have 2 tables. Table A contains records for a unique key field. A record for the same key field should exist in Table B, and B contains a "pipelength" field whose value should match the "pipelength" field in A. 1-to-1 relationship between A and B.
1) I need to verify that all records in A have a key field match in B. If not, I need to add a record in B (all the data I need for adding the record in B exists in A).
2) If the record does exist in B, I need to confirm that it's "pipelength" matches the "pipelength" value in A.

I'm maintenancing a corporate system so although the data structure isn't very efficient I have to work with it.

I use DAO recordsets a lot so 1st thought was to load A in a recordset and process each record. Then use a SELECT statement to pull the matching data from B. A null result would necessitate an APPEND query into B. For a non-null result, how can I check and/or edit the value of the "pipelength" field from B?

2nd thought was 2 recordsets and seek the key field value in B. Then I could "see" the "pipelength" field and confirm and/or edit it's value. Thoughts?

 

[PWise]

This will make sure that all uniquekey from Tablea will be in Tableb

insert into tableb (forginkey)
Select uniquekey
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
Where b.forginkey is null

this will set all pipelength

UPDATE tableb 
INNER JOIN tablea  
ON tablea.uniquekey=b.forginkey 
SET tableb.pipelength= [tablea].[pipelength]
WHERE tableb.pipelength<>[tablea].[pipelength]

 

[GNorman]

Beautiful, simple solution for exists in A and not in B. Identified 15 missing records in B. But I oversimplified the scenario thinking I could figure the harder part out based on the reply but no. I think the solution to the full scenario goes beyond SQL statements.

Full scenario: Table A has, among others, six fields which are all dates. If any of the dates get filled by the user in A, B will programatically get 6 new records, one new record for each of the 6 date fields in A. And it only takes filling one of the 6 dates in A for the program to create 6 new records in B. The six new records have a "sequence" field. Sequence "1" corresponds to the 1st date in A; sequence "2" corresponds to the 2nd date in A, and so on thru 6. So one record in A generates six records in B.
Now I need to look in A for the existence of a date in date field1. If yes, look into B for the same key field with sequence field = 1. If not a null result, then see if B.pipelength = A.pipelength and correct if needed. Then for the same record in A, look for a date in date field2. If yes, look into B for the same key field with sequence = 2 and compare pipelength. So on thru the 6th date field. Then skip to the next record in A and repeat the process.

And for each of the 15 records we found missing in B using the SELECT statement solution, I need to create 6 new records, total of 90 records.

 

[PWise]

for inserting 6 sequences

insert into tableb (forginkey,sequence )
Select uniquekey ,1
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =1
Where b.forginkey is null
union
Select uniquekey ,2
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =2
Where b.forginkey is null
union
Select uniquekey ,3
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =3
Where b.forginkey is null
union
Select uniquekey ,4
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =4
Where b.forginkey is null
union
Select uniquekey ,5
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =5
Where b.forginkey is null
union
Select uniquekey ,6
From tablea a
left join tableb b
on a.uniquekey=b.forginkey
and b.sequence =6
Where b.forginkey is null

 

[PWise]

if in tablea you only have one pipelength use same update query

 

[GNorman]

Trying just one piece, I plugged field names in and tried to execute in a query. Error message says 'Join
expression not supported' and it highlights the "b.sequence = 1" section of the code. Using Access 2003.

Select bp_id ,1
From Bulkpipe a
Left Join qty_detail b
On a.bp_id = b.item and b.sequence = 1
Where b.item is null

 

[PWise]

sorry Sb

Select bp_id ,1
From Bulkpipe a
Left Join qty_detail b
On a.bp_id = b.item
Where b.item is null and b.sequence = 1
.....