ONE of us can't count.

[Sue]

Either me or Access.

I have a query that lists all sessions for clients for 2009.The session date
criteria is >=#1/1/2009#. It runs fine. all sessions listed, no duplicate
dates, etc. I based a report on this query & have a field -
Count([SessionDate]) - in that report which properly counts the number of
session dates for almost all clients. It's the "almost all" I'm concerned
about. One client has had only 6 sessions so far this year, but the report
counts 12.

Ideas???

--
Thanks for your time!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...db4f1c9f21&dg=microsoft.public.access.reports

 

[Stefan Hoffmann]

hi Sue,

I have a query that lists all sessions for clients for 2009.The session date
criteria is >=#1/1/2009#. It runs fine. all sessions listed, no duplicate
dates, etc. I based a report on this query & have a field - [..]

Check the record source of your report, maybe you have a wrong assigned
relation in a JOIN operation.

Otherwise post the record source statement.


mfG
--> stefan <--

 

[Sue]

qry2009SessionCount is the record source for the report.
The query SQL 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;


Not to be dense, but if there was a problem with the join, wouldn't there be
errors for more than just this one client?
--
Thanks for your time!

hi Sue,

I have a query that lists all sessions for clients for 2009.The session date
criteria is >=#1/1/2009#. It runs fine. all sessions listed, no duplicate
dates, etc. I based a report on this query & have a field - [..]

Check the record source of your report, maybe you have a wrong assigned
relation in a JOIN operation.

Otherwise post the record source statement.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Sue,

FROM tblClient INNER JOIN tblSessionDates ON tblClient.Client_ID =
tblSessionDates.Client_ID
Not to be dense, but if there was a problem with the join, wouldn't there be
errors for more than just this one client?

This dependes on the type of JOIN operation and the data.
tblClient.Client_ID is a unique field, either the primary key or an
unique index?


mfG
--> stefan <--

 

[Sue]

Yes, Client_ID is the primary key, a unique field.

 

[Sninkle]

It could be due to the inner join.
--
Carrie

Yes, Client_ID is the primary key, a unique field.

 

[Sue]

If so, do you have an idea about how to fix the issue?

 

[Stefan Hoffmann]

hi Sue,

If so, do you have an idea about how to fix the issue?

As your JOIN looks quite normal, I think you have to take a closer look
at the actual data. Does the JOIN itself returns the correct data in
your problematic case?

If so, how do you do the actual count?


mfG
--> stefan <--