I have two tables (A and B) that display the presence or absence of a species within a polygon and within each polygons associated buffer layer. The purpose of the buffer layer is to account for a margin of error for species recorded at low spatial accuracy? By that I mean the species presence points are outside the target polygon, but close enough to potentially be within the polygon for example some species were recorded using a 6 figure grid reference which has a 200m margin of error. As you can see within the tables, species may be present in both tables (eg Spp1 in ID 1), however they may also be only present in only one (eg spp6 is present in ID 2 in table B but not A).
I wish to know the number unique species recorded at low spatial within the buffer in addition to the number of unique species recorded at higher spatial accuracy within the polygons but the two must be combined in the same operation to avoid counting the same species as two separate ones. So far I have used a spatial join on the datasets to produce a presence absence matrix (Tables A and B) but this does not work as it duplicates all of the species layers, eg creating spp1A and spp1B rather than just one column for spp1, spp2, spp3 etc. Is there anyway to achieve this?
Table A (eg polygon) ID spp1 spp3 spp4 spp6 1 1 1 1 2 1 1 3 1 1 4 1 5 1 Table B (eg polygon buffer) ID spp1 spp2 spp3 spp4 spp5 spp6 1 1 1 1 2 1 1 3 1 4 1 5 1