Simple Counting Query Question... But it's killing me.

  • Thread starter Thread starter Brendan
  • Start date Start date
B

Brendan

For topic sake, I will keep it simple. I have a query that derives its data
from a table. This query has a two fields (vBusinessName and vDateTime).
What I am trying to do is count the amount of unique fields for vBusinessName
and do it in the query so I can then sort them (in rank) for output to a
report.

When I use the count function, all I get is "1" as and answer in each row.
There has to be a way that I can total each address that we have gone to.

Thanks,

Brendan
 
Make two queries:

Q1
SELECT DISTINCT Data.vBusinessName
FROM Data;

Q2
SELECT Count(NoDubs.vBusinessName) AS CountOfvBusinessName
FROM NoDubs;

Hope this helps,

Mika
 
This query has a two fields (vBusinessName and vDateTime).
Leave vDateTime out as it is making each record unique.
 
For topic sake, I will keep it simple. I have a query that derives its data
from a table. This query has a two fields (vBusinessName and vDateTime).
What I am trying to do is count the amount of unique fields for vBusinessName
and do it in the query so I can then sort them (in rank) for output to a
report.

When I use the count function, all I get is "1" as and answer in each row.
There has to be a way that I can total each address that we have gone to.

Thanks,

Brendan
Click to expand...

If you're using the vDateTime field as a criterion to select records, uncheck
its Show checkbox in the query grid and change the default "Group By" on the
totals row to Where.
 
Problem Solved and thanks to all who helped.

Created two fields in my query from my table. Both fields auto named as
vbusinessname. In the first field (column), everything stays the same. In
the second column, the vbusinessname becomes a "Count" option.

You guys were right about the vDateTime field forcing each value to be
unique and thus not allowing a true count. It s been a while and its amazing
what you forget about the simplest things.

Brendan
 
Back
Top