I'm using the following code:

SELECT teRManager, count(*) as ItemCount FROM Time_Entry GROUP BY teRManager

and I seem to be getting numerous repeats when my goal is to get a total for each distinct instance of each item. It seems to work when I run it for other fields in the same table. Strange......

Here is what I get:

teRManager ItemCount
Bruce Amman 59
Jeff Mort 6275
James Monroe 2556
James Monroe;Jill Manners 38
Jill Manners 4
James Monroe;Jill Manners 17
Jill Manners 1698
Jill Manners;James Monroe 1
Jill Manners 28
Jill Manners;James Monroe 1
Jill Manners 63
Jill Manners;James Monroe 2
Jill Manners 90
Jill Manners;James Monroe 1

Here is what I'm looking for:

teRManager ItemCount
Bruce Amman 59
Jeff Mort 6275
James Monroe 2556
James Monroe;Jill Manners 55
Jill Manners;James Monroe 5
Jill Manners 1883

(I'm using Access to pull the info from a Lotus Notes database)

Thanks!

Norm

Your query looks correct. Do you have a primary or foreign key for the teRManager field? I ask, because is it possible that the values in the teRManager field are not all the same (though they look the same)? Perhaps some combinations of Jill Manners; James Monroe have extra spaces mixed into the text, or appended to the end of the names?

For example (assuming '_' is a space), 'Jill_Manners" is not the same as "Jill_Manners_".

Run a query of Select Distinct teRManager from Time_Entry and see if the name combinations show up more than once. If a combination shows up more than once, then there is some difference in how it was entered into the field.

I'm using a 4 field "combo" for my primary field. However, the teRManager is not one of them (nor is it a foreign key).

I also considered that perhaps there was extra spacing or some hidden characters in one of the names. The only way I knew to test for it was copy the info and set them equal to each other in Excel and see if it came back "true" (which it did).

Strangely, when I run your suggested query:

Select Distinct teRManager from Time_Entry

I don't get the repeated fields that I get during the first statement I used.

Any ideas?

Norm

What version of Access are you using?

The query looks right, so if your data is correct (which your SQL distinct query indicates), I'm at a loss for what else could be the issue.

I was using Access 2002.

Thanks for the help.

I might be off base...but dont you have to do an ORDER BY - so the data is grouped together - otherwise everytime it hits a change in teRManager is spits out the total count to date.

Can you post the mdb - I would like to test it.

In Access 2002, you should not have to use a ORDER BY statement in the grouping in order to get the proper count.

The GROUP BY statement will order the data accordingly.

Try to count a specific field as opposed to all the fields (*).

For some reason I have had this work in the past.

Great! Thanks for all the help....the problem has been solved.

Norm