Results 1 to 6 of 6
  1. #1
    EcologyHelp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    16

    Trouble Shooting Union and Distinct Count Queries

    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.
    Click image for larger version. 

Name:	Final_Union.PNG 
Views:	9 
Size:	27.3 KB 
ID:	24416Click image for larger version. 

Name:	Final_Count.PNG 
Views:	9 
Size:	33.0 KB 
ID:	24418Click image for larger version. 

Name:	Final_Union_SQL.PNG 
Views:	9 
Size:	9.5 KB 
ID:	24419Click image for larger version. 

Name:	Final_Count_SQL.PNG 
Views:	9 
Size:	5.6 KB 
ID:	24417
    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.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    EcologyHelp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    16
    Quote Originally Posted by orange View Post
    Don't use UNION ALL
    The ALL includes duplicates

    Use UNION http://www.w3schools.com/sql/sql_union.asp
    I need the duplicates because I am trying to get a count of the total number of times that each plant species occurs in the database. The table WSPZAll_Union displays correctly. It should be returning all the duplicate entries. I am wondering why the COUNT is not counting all the occurrences. If I use just UNION I only return 96 plant species (which is the number of distinct plant species) but then the distinct count would only be 1 for each species rather then the total number of times the plant species occurs in the database.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Distinct Count Queries is to count the DISTINCT/UNIQUE entries.

    You might consider Count and Group By to get the count of plants by species.

  5. #5
    EcologyHelp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    16
    I did do a COUNT(*) and then a GROUP BY ColumnName, and in closing and reopening my database some how the problem was magically fixed. Thank you for you taking the time to respond.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help trouble shooting a related records issue
    By Bkper087 in forum Access
    Replies: 3
    Last Post: 02-25-2015, 03:18 PM
  2. Distinct Count
    By ertweety in forum Queries
    Replies: 1
    Last Post: 04-15-2014, 10:10 AM
  3. DISTINCT on a Union All query?
    By Robeen in forum Queries
    Replies: 3
    Last Post: 04-27-2011, 10:30 AM
  4. Union and Group by/Distinct
    By Rixxe in forum Queries
    Replies: 3
    Last Post: 11-10-2010, 09:45 AM
  5. Replies: 3
    Last Post: 08-10-2010, 02:12 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums