If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Selecting and counting records in one query
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 |
#2
|
|||
|
|||
Selecting and counting records in one query
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. |
#3
|
|||
|
|||
Selecting and counting records in one query
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. |
#4
|
|||
|
|||
Selecting and counting records in one query
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" |
Thread Tools | |
Display Modes | |
|
|