VB.net Trying to find balance owed by customers
Hi
I am trying to use the following statement to combine the total in two tables (Invoices and Payments) so that I can find the balance owed by customer. I am not sure what I have done wrong. I hope the problem is fairly obvious
oSQL = "select"
oSQL = oSQL & " CustomerID, sum(amount) Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]"
oSQL = oSQL & " from"
oSQL = oSQL & "("
oSQL = oSQL & "select [Link ID] CustomerID, Amount * -1 As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from payments Inner Join Customers On [LINK ID] = [CustID]"
oSQL = oSQL & " union all "
oSQL = oSQL & "select invoices.customerId, [inv amt] As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from invoices Inner Join Customers On invoices.[CustomerID] = Customers.[CustID]"
oSQL = oSQL & ") SubQuery"
oSQL = oSQL & " where Amount > 0"
oSQL = oSQL & " group by CustomerID, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]"
I am trying to use the following statement to combine the total in two tables (Invoices and Payments) so that I can find the balance owed by customer. I am not sure what I have done wrong. I hope the problem is fairly obvious
oSQL = "select"
oSQL = oSQL & " CustomerID, sum(amount) Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]"
oSQL = oSQL & " from"
oSQL = oSQL & "("
oSQL = oSQL & "select [Link ID] CustomerID, Amount * -1 As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from payments Inner Join Customers On [LINK ID] = [CustID]"
oSQL = oSQL & " union all "
oSQL = oSQL & "select invoices.customerId, [inv amt] As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from invoices Inner Join Customers On invoices.[CustomerID] = Customers.[CustID]"
oSQL = oSQL & ") SubQuery"
oSQL = oSQL & " where Amount > 0"
oSQL = oSQL & " group by CustomerID, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]"
Lee
What problem are you getting? Any errors?
ASKER
If I run the following statement I get 1375 invoiced for CustomerID 1
select invoices.customerId, Sum([inv amt]) As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from invoices Inner Join Customers On invoices.[CustomerID] = Customers.[CustID]
group by CustomerID, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]
If I run the following statement I get -1800 paid for Customer ID 1
select [Link ID], Sum(Amount) * -1 As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from payments Left Join Customers On [LINK ID] = [CustID]
group by [Link ID], School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]
Now using the full statement mentioned in my question I a trying to get -425 as a result but I get 1375 which is the total Invoiced and not the difference
select invoices.customerId, Sum([inv amt]) As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from invoices Inner Join Customers On invoices.[CustomerID] = Customers.[CustID]
group by CustomerID, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]
If I run the following statement I get -1800 paid for Customer ID 1
select [Link ID], Sum(Amount) * -1 As Amount, School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3] from payments Left Join Customers On [LINK ID] = [CustID]
group by [Link ID], School, [Last Name], [First Name], [Contact No], [Child1], [Child2], [Child3]
Now using the full statement mentioned in my question I a trying to get -425 as a result but I get 1375 which is the total Invoiced and not the difference
ASKER
I see where I may have gone wrong. I get the balance of -425 when I take the following line out
oSQL = oSQL & " where Amount > 0"
This line was intended to only show a client balance that is positive but it has disrupted things
I hope I am making sense
oSQL = oSQL & " where Amount > 0"
This line was intended to only show a client balance that is positive but it has disrupted things
I hope I am making sense
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the help