Selecting and counting records in one query

[Peter]

Hi there. I have a table with relatively simple data. If I build a query
where the criteria uses the Like "*" & [Expr1] & "*" expression, it works
fine and returns all the records. What I'd really like to do is for the
query to provide a count of the matches rather that the records that match.
So, instead of saying here's all the "Smiths" in the table, that there were
41 Smith's returned.

I've tried changing the query to a group and counting the field but that
runs but doesn't show me a value.

Eventually, I'd like to be able to build a form where multiple queries show
the number of matches of expr1 against a number of fields in the same table
but I seem to be stuck at level 1.

Any advice or help would be greatly appreciate.

By the way, my counting will be on text and numeric fields - don't know if
that makes a difference.

Thanks

Peter

 

[Bob Barrows [MVP]]

Hi there. I have a table with relatively simple data. If I build a
query where the criteria uses the Like "*" & [Expr1] & "*"
expression, it works fine and returns all the records. What I'd
really like to do is for the query to provide a count of the matches
rather that the records that match. So, instead of saying here's all
the "Smiths" in the table, that there were 41 Smith's returned.

I've tried changing the query to a group and counting the field but
that runs but doesn't show me a value.

Eventually, I'd like to be able to build a form where multiple
queries show the number of matches of expr1 against a number of
fields in the same table but I seem to be stuck at level 1.

Any advice or help would be greatly appreciate.

By the way, my counting will be on text and numeric fields - don't
know if that makes a difference.

This is what the sql will look like (what you see when you switch the
query from Design View to SQL View):

select count(*)
from tablename
where fieldname Like "*" & [Expr1] & "*"

 

[mokihi]

To follow on from this last reply. Can someone please tell me how to build an
expression that will let me count all of the places listed in my mailing list
with a corresponding number beside them.

that is, I want to know what Cities are in there and how many records are at
each city.

Is this possible. I tried using the previous query presented however that
one asks me to input the name of the city before it returns an answer. I
would like to be able to see all of the city names and their counts.

Hi there. I have a table with relatively simple data. If I build a
query where the criteria uses the Like "*" & [Expr1] & "*"
expression, it works fine and returns all the records. What I'd
really like to do is for the query to provide a count of the matches
rather that the records that match. So, instead of saying here's all
the "Smiths" in the table, that there were 41 Smith's returned.

I've tried changing the query to a group and counting the field but
that runs but doesn't show me a value.

Eventually, I'd like to be able to build a form where multiple
queries show the number of matches of expr1 against a number of
fields in the same table but I seem to be stuck at level 1.

Any advice or help would be greatly appreciate.

By the way, my counting will be on text and numeric fields - don't
know if that makes a difference.

This is what the sql will look like (what you see when you switch the
query from Design View to SQL View):

select count(*)
from tablename
where fieldname Like "*" & [Expr1] & "*"


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Select City, count(*)
from tablename
group by City

To follow on from this last reply. Can someone please tell me how to
build an expression that will let me count all of the places listed
in my mailing list with a corresponding number beside them.

that is, I want to know what Cities are in there and how many records
are at each city.

Is this possible. I tried using the previous query presented however
that one asks me to input the name of the city before it returns an
answer. I would like to be able to see all of the city names and
their counts.

Hi there. I have a table with relatively simple data. If I build a
query where the criteria uses the Like "*" & [Expr1] & "*"
expression, it works fine and returns all the records. What I'd
really like to do is for the query to provide a count of the matches
rather that the records that match. So, instead of saying here's all
the "Smiths" in the table, that there were 41 Smith's returned.

I've tried changing the query to a group and counting the field but
that runs but doesn't show me a value.

Eventually, I'd like to be able to build a form where multiple
queries show the number of matches of expr1 against a number of
fields in the same table but I seem to be stuck at level 1.

Any advice or help would be greatly appreciate.

By the way, my counting will be on text and numeric fields - don't
know if that makes a difference.

This is what the sql will look like (what you see when you switch the
query from Design View to SQL View):

select count(*)
from tablename
where fieldname Like "*" & [Expr1] & "*"


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.