Can't count.

[Sue]

I have a query that lists the number of sessions for each client for 2009.
The criteria for "SessionDate" = >=#1/1/2009#. The query runs properly and
lists all sessions for 2009, no sessions EXCEPT 2009, and no duplicate
sessions. The SQL for the query is:
SELECT tblClient.LN, tblClient.FN, tblSessionDates.SessionDate
FROM tblClient INNER JOIN tblSessionDates ON tblClient.Client_ID =
tblSessionDates.Client_ID
WHERE (((tblSessionDates.SessionDate)>=#1/1/2009#))
ORDER BY tblClient.LN, tblClient.FN, tblSessionDates.SessionDate;

I based a report on that query & have a field that counts the number of
sessions for each client for 2009. The field is "=Count([SessionDate])".

The report accurately counts the number of sessions for all clients EXCEPT
ONE. For this particular client, who has had 6 sessions so far this year,
Access counts 12 sessions.

I've run Find Duplicate queries on the data and checked it manually. There
are ONLY 6 sessions for this client, no duplicates, and the client is listed
only once, so I can't figure out why I'm having this problem.

Please help. I'm pulling my hair out and I don't look good bald. I posted on
this issue yesterday but responses didn't result in a solution.

 

[Jeff Boyce]

Sue

When the aggregate (Count()) isn't working correctly, I sometimes have to
resort to generating ALL the detail lines, then looking for what might be
different for that one client.

There's nothing I'm aware of in Access that would cause it to count
differently for one client vs. all the others, so the logical first place to
check is the data, at the detail level.

Of course, this may be what you've already tried ...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Check tblClient.Client_ID for double entry of that client's ID.