Count in Joined queries

[Draya]

I have the most puzzling problem
Using Access 97 (my client wont update to a newer Access Version at this
time) I have the data portion of my database on a win2000 server. I have
the interface which contains my queries on several different machines
accessing the database. I am generating a report based on a query which is
built on sub queries. two of my subqueries use the count function. 2
machines generate the report properly. The other machines do not. The
count is returning null. If I look at the subqueries individually they
return the proper count.

What can be causing this. I have tried looking at the .mdb driver version
numbers, service packages, operating system updates, but I must be missing
something, What could it be?

The two working machines have the following setups:

1) Windows XP, all up to date service packages and patches (compaq) This
machine only had the Access SR-1 on it. It was generating the proper counts
in my final query even with sr1 but I updated to sr2b anyway, and the
queries continue to work properly.
This machine also has the microsoft .mdb driver version 4.00.6019.00

2)Windows ME, all up to date service packages and patches. This machine
also only had SR-1 and the final query worked properly, I also updated to
SR-2b and the query continues to work properly.
This machine also has the microsoft .mdb driver version 4.00.4403.02
The Machines that the queries don't work on are as follows:

1)Windows XP, all up to date service packages and patches. I updated to
SR-2 on this machine also. the final query shows null as the count results,
but when I run the subquery by itsself the count works properly.
This machine also has the microsoft .mdb driver version 4.00.6019.00 (same
as working number 1 above)


I also have 2 other machines it is not working on, however I was unable to
look into their configurations at this time. One is a windows 98 machine,
and the other is another winxp (not compaq if that makes any difference)

I'm so confused. Anyone have ideas of how to solve this problem??

Here is the sql:
the final query:
SELECT [RPT_No-ShowsBetweenDates_COUNT].afterCareRecordID_FK,
[RPT_No-ShowsBetweenDates_COUNT].TotalActualNOShowsBetweenDates AS
TotalNoShowsBetweenDates, nz([CountOfafterCareRecordID_FK],0) AS
TotalOrigAppts, nz([TotalActualShowsBetwweenDates],0) AS ShowsBetweenDates,
Clients.ClientID, Clients.lastName, Clients.firstName, Clients.middleInitial
FROM Clients RIGHT JOIN ((([RPT_No-ShowsBetweenDates_COUNT] LEFT JOIN
TotalOrigAppointmentsSchedualedPerAfterCareRecord ON
[RPT_No-ShowsBetweenDates_COUNT].afterCareRecordID_FK =
TotalOrigAppointmentsSchedualedPerAfterCareRecord.afterCareRecordID_FK) LEFT
JOIN RPT_KeptAppointmentsBetweenDates_COUNT ON
[RPT_No-ShowsBetweenDates_COUNT].afterCareRecordID_FK =
RPT_KeptAppointmentsBetweenDates_COUNT.afterCareRecordID_FK) LEFT JOIN
Client_AfterCare_Records ON
[RPT_No-ShowsBetweenDates_COUNT].afterCareRecordID_FK =
Client_AfterCare_Records.afterCareRecordID) ON Clients.ClientID =
Client_AfterCare_Records.clientID_FK
ORDER BY Clients.lastName, Clients.firstName, Clients.middleInitial;

One fo the Subqueries RPT_No-ShowsBetweenDates_COUNT

SELECT [RPT_No-ShowsBetweenDates].afterCareRecordID_FK,
Count([RPT_No-ShowsBetweenDates].afterCareRecordID_FK) AS
TotalActualNOShowsBetweenDates
FROM [RPT_No-ShowsBetweenDates]
GROUP BY [RPT_No-ShowsBetweenDates].afterCareRecordID_FK;

Thank you in advance for any clues as to how to make this work on all
machines.
-draya