Querying multiple occurances of data in a column

[martin461]

Hi there,

This will hopefully take someone only a minute to answer but as for me I'am a bit of a beginner and have searched quite hard but cannot find an answer.

I have a table with a number of columns, for arguements sake its a simple customer details table with ID, first_name, surname etc...

I now need a query that I can run once and it will give me the amount of first names including for example amount of 'steve's and amount of 'jeffrey's, ‘Becky’s etc. The required output should be:

Query_get_first_names

steve | jeffrey | Becky
14 21 50

I have a query i've written and in the first column I have a Like "steve" that gives only people with that name and then to get the count I have another column that has this Expr1: Sum(1) which gives me a count of the amount of steve in the table. Duplicating this doesn't give me the required results and I don’t want to have to create multiple queries for this.

However, I would like a query that can query the same column giving the amount of instances. Hope this makes sense.

Thank you.

p.s. not sure how to use the 'count' citeria but you must be able to do something like:

count ("steve") | count ("Becky") | etc etc

 

[Kryst51]

Just put the first name field in the query and then add in the total row in the qbe, then select "count" in the total row of the first name field.

Check out the attachments

 

[namliam]

Look into a crosstab query, this will put the first names across the top and count them downwards...

 

[HiTechCoach]

Have you tried a cross tab query?

Based on your example, this may help:
Dynamic Report based on a Crosstab query

 

[martin461]

Apologies for taking so long to back, couldn't of asked for better help you little stars you. Many many thanks.