Upending unmatched record from another table

[GadiLevy]

Appending challenge,More complicated than the previous one, I have a huge
data over 750000 records that I need to
mange one of the challenges I am facing is as follow:
Table1 is a list of SurvyID, SiteNo, SpeciesNo and SpPresence where SurvyID,
SiteNo and SpecieNo are the unique combination.
SurvyID SiteNo SpecieNo SpPresence
Sur1 Sit1 sp1 1
Sur1 Sit1 sp4 1
Sur1 Sit2 sp1 1
Sur1 Sit2 sp2 1
Sur1 Sit2 sp3 1
Sur2 Sit3 sp2 1

Table2 is a list of Species where SpecieNo is the unique No
SpecieNo SpecieName SpecieFamily
Sp1 sn1 F1
Sp2 sn2 F1
Sp3 sn3 F1
Sp4 sn4 F1
Sp5 sn5 F2
I need to find out the species that missing from each combinations in table1
(sp2 and sp3 are missing from Sur1 and Sit1, sp4 is missing from Sur1 and
Sit2 and sp1, sp3, sp4 sp5 missing from Sur2 and Site3 combanation).
I need to add the missing combination to table1 and mark each record in the
SpPresence column with 0. The result after append( sorting is not important)
SurvyID SiteNo SpecieNo SpPresence
Sur1 Sit1 sp1 1
Sur1 Sit1 sp4 1
Sur1 Sit2 sp1 1
Sur1 Sit2 sp2 1
Sur1 Sit2 sp3 1
Sur2 Sit3 sp2 1
Sur1 Sit1 sp2 0
Sur1 Sit1 sp3 0
Sur1 Sit1 sp5 0
Sur1 Sit2 sp4 0
Sur1 Sit2 sp5 0
Sur2 Sit3 sp1 0
Sur2 Sit3 sp3 0
Sur2 Sit3 sp4 0
Sur2 Sit3 sp5 0
There are over 2000 unique combinations of the SurvyId plus the SiteNo each
of the combination should have a complete list of all the SpecieNo
Thank you

 

[John W. Vinson]

There are over 2000 unique combinations of the SurvyId plus the SiteNo each
of the combination should have a complete list of all the SpecieNo

I suspect that you don't need to store all these "absent" records in a Table.
You can use an Outer Join query:

SELECT table1.SurvyID, Table1.SiteNo,
Table2.SpeciesNo,IIF(IsNull(Table1.SpeciesNo), 0, 1) AS SpPresence
FROM table1 RIGHT JOIN table2
ON table1.SpeciesNo = Table2.SpeciesNo;

If you must, then base an append query on this query... but you can base an
export, a report, a form, pretty much anything you want directly on the query.

John W. Vinson [MVP]