Prev: display top 5 records for each subcategory
Next: Make table query combining records of two tables into a third
From: Peter on 30 Jun 2008 17:23
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
From: Bob Barrows [MVP] on 30 Jun 2008 17:40
Peter wrote:
> 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.


From: mokihi on 30 Jun 2008 18:53
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.



"Bob Barrows [MVP]" wrote:

> Peter wrote:
> > 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.
>
>
>
From: Bob Barrows [MVP] on 30 Jun 2008 19:00
Select City, count(*)
from tablename
group by City

mokihi wrote:
> 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.
>
>
>
> "Bob Barrows [MVP]" wrote:
>
>> Peter wrote:
>>> 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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 | 
Pages: 1
Prev: display top 5 records for each subcategory
Next: Make table query combining records of two tables into a third