L
LiquorGuy via AccessMonster.com
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];
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];