Counting unique records

[leaster]

I have created a database that has 3 tables with the Client name being the connecting relationship. I created a query to filter out certain information from these tables. The tables are:

Green Book, Yellow Book, and White Book/Debtors

I need to count the # of debtors for each client. However, the table does include duplicate debtor names for comparison purposes for each Client. I need to count the unique debtors. Can this be done in the query? I have included the code below:

SELECT [Green Book].[Legal/Not Funding], [Green Book].[Client Name], [Green Book].[Factor Start Date], [Green Book].[First Funding], [Yellow Book].[Collateral Exam Date], [Yellow Book].[Collateral Exam Sign-off], [Green Book].[Line Limit], [Green Book].[Minimum Volume], [Yellow Book].[Collateral Notes], [Green Book].[Contract Termination Date], [Yellow Book].[Profit Loss Date], [Yellow Book].[Financials Current], Count([White Book/Debtors].[Debtor Name]) AS [CountOfDebtor Name]
FROM ([Green Book] INNER JOIN [Yellow Book] ON [Green Book].ID = [Yellow Book].[Client Name]) INNER JOIN [White Book/Debtors] ON [Green Book].ID = [White Book/Debtors].[Client Name]
GROUP BY [Green Book].[Legal/Not Funding], [Green Book].[Client Name], [Green Book].[Factor Start Date], [Green Book].[First Funding], [Yellow Book].[Collateral Exam Date], [Yellow Book].[Collateral Exam Sign-off], [Green Book].[Line Limit], [Green Book].[Minimum Volume], [Yellow Book].[Collateral Notes], [Green Book].[Contract Termination Date], [Yellow Book].[Profit Loss Date], [Yellow Book].[Financials Current], [Green Book].Active
HAVING ((([Green Book].[Legal/Not Funding])=False) AND (([Green Book].Active)=True))
ORDER BY [Green Book].[Client Name];

Any input would be greatly appreciated.

 

[jdraw]

The only input I can offer is as follows:

-You should avoid using spaces and special characters in object and field names.
-When you join tables, the joining fields should have the same datatype.

This

([Green Book] INNER JOIN [Yellow Book] ON [Green Book].ID = [Yellow Book].[Client Name]) INNER JOIN [White Book/Debtors] ON [Green Book].ID = [White Book/Debtors].[Client Name]

seems to indicate you are tryiing to join tables on an ID field(typically number) and a name field (typically text)

There is an interesting article on relational database tables etc here
http://forums.aspfree.com/attachment.php?attachmentid=4712