Customer Query

Hello,

I am trying to run a query that lists all my clients, their invoice totals,
and total payments thus far. I have created two separate queries. Once
totals up the invoices and the other sums up the total payments fine.
However when I try to run them together, it only returns the one record of
the client that has a payment. How can I get it so that all clients are
displayed irregardless of whether they have paid or not. Here is my SQL
statement thus far.... THANKS!

SELECT DISTINCTROW Projects.ClientID, Projects.ProjectID, Projects.
ProjectName, Projects.ProjectDescription, [Sum of Invoice Query].[Invoice
Total], [Sum of Payments Query].[Total Payments]
FROM ((Clients INNER JOIN ([Sum of Payments Query] RIGHT JOIN Projects ON
[Sum of Payments Query].ProjectID = Projects.ProjectID) ON Clients.ClientID =
Projects.ClientID) INNER JOIN Payments ON Projects.ProjectID = Payments.
ProjectID) INNER JOIN [Sum of Invoice Query] ON Clients.ClientID = [Sum of
Invoice Query].ClientID
GROUP BY Projects.ClientID, Projects.ProjectID, Projects.ProjectName,
Projects.ProjectDescription, [Sum of Invoice Query].[Invoice Total], [Sum of
Payments Query].[Total Payments];

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1

It sounds like you have two queries. I think you need to link the two
queries and use a left (or right) join. Right click on the relationship
(line) between the two queries and it will show a window that will ask you
how you want the link to be. You will want it to include all records from
the table that has all of the invoicing. I assume there won't be any
payments for customers unless there is an invoice.

"LiquorGuy via AccessMonster.com" wrote:

Hello,

I am trying to run a query that lists all my clients, their invoice totals,
and total payments thus far. I have created two separate queries. Once
totals up the invoices and the other sums up the total payments fine.
However when I try to run them together, it only returns the one record of
the client that has a payment. How can I get it so that all clients are
displayed irregardless of whether they have paid or not. Here is my SQL
statement thus far.... THANKS!

SELECT DISTINCTROW Projects.ClientID, Projects.ProjectID, Projects.
ProjectName, Projects.ProjectDescription, [Sum of Invoice Query].[Invoice
Total], [Sum of Payments Query].[Total Payments]
FROM ((Clients INNER JOIN ([Sum of Payments Query] RIGHT JOIN Projects ON
[Sum of Payments Query].ProjectID = Projects.ProjectID) ON Clients.ClientID =
Projects.ClientID) INNER JOIN Payments ON Projects.ProjectID = Payments.
ProjectID) INNER JOIN [Sum of Invoice Query] ON Clients.ClientID = [Sum of
Invoice Query].ClientID
GROUP BY Projects.ClientID, Projects.ProjectID, Projects.ProjectName,
Projects.ProjectDescription, [Sum of Invoice Query].[Invoice Total], [Sum of
Payments Query].[Total Payments];

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200611/1