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

[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

 

[Mio]

Make two queries:

Q1
SELECT DISTINCT Data.vBusinessName
FROM Data;

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

Hope this helps,

Mika

 

[KARL DEWEY]

This query has a two fields (vBusinessName and vDateTime).
Leave vDateTime out as it is making each record unique.

 

[John W. Vinson]

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

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.

 

[Brendan]

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