Please help, Finding unmatched records and append them

[Guest]

Appending challenge, 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
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
I need to find out the species that missing from each combinations in table1
(sp2 and sp3 are missing from Sur1 and Sit1, and sp4 is missing from Sur1 and
Sit2).
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
Sur1 Sit1 sp2 0
Sur1 Sit1 sp3 0
Sur1 Sit2 sp4 0

 

[Guest]

Try this ---
INSERT INTO GadiLevy_1 ( SurvyID, SiteNo, SpecieNo, SpPresence )
SELECT GadiLevy_1.SurvyID, GadiLevy_1_1.SiteNo, GadiLevy_1_2.SpecieNo, 0 AS
Expr1
FROM GadiLevy_1, GadiLevy_1 AS GadiLevy_1_1, GadiLevy_1 AS GadiLevy_1_2
GROUP BY GadiLevy_1.SurvyID, GadiLevy_1_1.SiteNo, GadiLevy_1_2.SpecieNo, 0;

 

[Guest]

Thank you for your help.
I just found out that it is not doing the job as I expected
If I have another record in Table1 with new survyNo eg Sur5 and SiteNO the
result table, will show the SiteNo in each Survy which is not correct. I
added SurNo and SiteNo shoing the result table.
Can you please help me find a solution?
Thank you
Gadi

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 Sit5 sp2 1
The result should be as follow
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 Sit5 sp2 1
Sur1 Sit1 sp2 0
Sur1 Sit1 sp3 0
Sur1 Sit2 sp4 0
Sur2 Sit5 sp1 0
Sur2 Sit5 sp3 0
Sur2 Sit5 sp4 0
Each Survy have many sites, on each site we know what species are presence
(mark as 1) the result table should have also the absence species on each
site (mark as 0)