I have some pretty simple SQL expressions that I need help trouble shooting. I have built a number of UNION queries to create a master list of plant species. In the final Union query I have 1200 records (some of those are blank), When I run a distinct count query on the final UNION query I am not receiving accurate numbers for plant species. Below are pictures of the datasheets and the SQL views.
I have highlighted one plant species Juncus balticus as an example, as you can see just in the first 50 records or so it occurs 5 times and the final distinct count has it occurring 4. I am wondering why this is happening. Any help would be appreciated. I have a feeling this should be a simple fix as I am not very experienced with Access.