Distinct Counts

[EcologyHelp]

Hello,

I am trying to generate a query with distinct counts. I have a list of plant names and I am trying to determine the number of occurrences for each plant species.

The first picture is a piece of my database with a list of plant species. As you can see there are multiples of the same plants in that list. What I am trying to do is count the amount of times that each plant occurs in the list. It should look like the picture below.

As you can see the list generated above is only returning a value of 1 for each plant species. I know that I have to do sub-queries, as Access does not support a direct DISTINCT function. Here is a picture of my SQL view to show what I have done, and probably where I have gone wrong.

Please let me know if you need anymore information.



Thank you in advance for any help.

[pbaldy]

Try

SELECT SpeciesField, Count(*) As HowMany
FROM TableName
GROUP BY SpeciesField

[EcologyHelp]

Thanks so much I don't know why I was making it so complicated. I guess that's what happens when you are learning Access on the fly.

[pbaldy]

Happy to help!

[EcologyHelp]

Before I list this as solve I have one more question. I actually have multiple columns of plant species that I would like to get the total number of occurrence for each plant species (Plant Species database below). There is a total of 8 columns with plant species each column is titled PZ1_SP1_DominantSpecies, PZ1_SP2_DominantSpecies, and so on. I would like to generate an occurrence list for all plant species in that database (which has the name WSPZ1_Final1). Any help would be appreciated.
.

[pbaldy]

Sounds like you have a normalization problem; those should probably be records in a related table, not fields in this one. If you're stuck with it, you can create a UNION query that joins the 8 fields into 1, then run your query against that.

[EcologyHelp]

I am not quite sure how to run a UNION query that joins columns from the same table.

[pbaldy]

SELECT Field1
FROM TableName
UNION ALL
SELECT Field2
FROM TableName
UNION ALL
SELECT Field3
FROM TableName