SELECTING TOTAL TO RETURN

[Ben6550]

I wish my query to look at one column of data in a table and ask user for parameter to search for & return a total on ie. ourrances of SMITH in surname, instead of writing seperate queries for each parameter.

 

[lespaul]

SELECT COUNT(LASTNAME) FROM TABLE WHERE LASTNAME = [Please enter the Name to search for:]

Remember though that with this you have to have an EXACT match, it won't find SMITH if the person types SMIHT.

HTH

Leslie

 

[SteveR77]

Select the option of creating a new query in design view.

Add the table that contains the data you need to the query.

Add the surname field.

For the criteria enter - [Enter Surname]

Add the surname field to the query once again.

Find the button that looks like an E (its the sigma character) and click on it.

This should now have added the group by line to your query

For the second instance of the surname field change the GroupBy to Count.



Hope this helps,

Steve

 

[SteveR77]

To follow up on what Leslie provided. If you wanted to have the flexibility of providing a count for all the names based on matching only part of the name you could do what follows. The LIKE statement allows a pattern or part of a pattern to be used to give you results.


SELECT LastName, Count(LastName) AS CountOfLastName
FROM TableName
GROUP BY LastName
HAVING (((LastName) Like [Enter Last Name] & "*&quot);

 

[SteveR77]

One finally thing to make you aware of. If nothing is entered for the SQL statement that uses the LIKE option
you will get a list of unique names and counts of those names for all the entries.


Cheers,

Steve