Help counting records

[stauartadair]

Hello everyone,

Got another problem that I'm sure you guys will solve with your eyes closed

I've got a database with a field called courier_no. Imagine this set of data.


1234
1234
1266
1277
1277
1288
1299

I want to run a query to count the number of instances that each number appears. For example I would expect the results
1234 2
1266 1
1277 2
1288 1
1299 1

I then want to run a criteria on the count field (But I think I'm ok with that bit once I've solved the counting of the data.)

I've tried the count function in the query builder but it doesnt total the instances that each record appears.

Help please (and please keep it simple cos you know I struggle with all that VBA stuff )

Stu

 

[stauartadair]

Actually just had a revelation. If I just have 2 fields in the query...

Courier_no in twice.
In the total field I've got one with group by and the other with count it works and I get the results I want.
Problem I then have is that I want to add other field to the query but when I drag them in the count stops working - Maybe something to do with the total field on the other field being down as Group By ?

Am I confusing you lot as much as I'm confusing myself

 

[boblarson]

Use that one count query and then pull it into another query and then you can use more fields/tables. You just have to have that one two field query to do the counts first and then use it like a table in another query.

 

[stauartadair]

Cracked it thanks.

Problem was that I was dragging fields into the query that could not be grouped in line with the counts of the others. Funny how just typing it here helps clear your mind

 

[boblarson]