Outstanding Balance Query on Time-Billing Template- Access 2003

[Jason]

Hello. I am trying to create a report or a query to spit out a report
of what clients still have outstanding balances (and the balance) . I
have tried to past the client subform into a report, and all I get is
'#NAME?' in the 'Amount Due' column.
Anyone get something like that going in this template?

Thanks in advance,

-J

 

[Guest]

I'd have thought a query along these lines, which returns all clients and the
sum of all time card billing amounts and expenses in relation to each
client's projects less the sum of all payments in relation to the project,
where this is more than zero, would give the required results. You could if
you wished break it down by project by joining the projects table to the
Clients table in the outer query and correlating the subqueries to the outer
query on ProjectID rather than ClientID.

SELECT CompanyName,
(SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) AS BalanceOutstanding
FROM Clients
WHERE (SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) > 0;

Ken Sheridan
Stafford, England

 

[Jason]

That query isn't bringing anything up.

The query in design view shows the criteria as:
BalanceOutstanding: (SELECT SUM(ExpenseAmount)

I pasted your query in the query in SQL view verbatim.

Anyway, I appreciate your help. My SQL programming skills leave a bit
to be desired...

-J

I'd have thought a query along these lines, which returns all clients and the
sum of all time card billing amounts and expenses in relation to each
client's projects less the sum of all payments in relation to the project,
where this is more than zero, would give the required results. You could if
you wished break it down by project by joining the projects table to the
Clients table in the outer query and correlating the subqueries to the outer
query on ProjectID rather than ClientID.

SELECT CompanyName,
(SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) AS BalanceOutstanding
FROM Clients
WHERE (SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) > 0;

Ken Sheridan
Stafford, England

Hello. I am trying to create a report or a query to spit out a report
of what clients still have outstanding balances (and the balance) . I
have tried to past the client subform into a report, and all I get is
'#NAME?' in the 'Amount Due' column.
Anyone get something like that going in this template?

Thanks in advance,

-J

 

[Guest]

Try catering for Nulls with the NZ function:

SELECT CompanyName,
NZ((SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time Card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
+NZ((SELECT SUM(BillableHours*BillingRate)
FROM [Time Card Hours], Projects
WHERE [Time Card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
-NZ((SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0) AS BalanceOutstanding
FROM Clients
WHERE
NZ((SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time Card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
+NZ((SELECT SUM(BillableHours*BillingRate)
FROM [Time Card Hours], Projects
WHERE [Time Card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
-NZ((SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0) > 0;

Ken Sheridan
Stafford, England

That query isn't bringing anything up.

The query in design view shows the criteria as:
BalanceOutstanding: (SELECT SUM(ExpenseAmount)

I pasted your query in the query in SQL view verbatim.

Anyway, I appreciate your help. My SQL programming skills leave a bit
to be desired...

-J

I'd have thought a query along these lines, which returns all clients and the
sum of all time card billing amounts and expenses in relation to each
client's projects less the sum of all payments in relation to the project,
where this is more than zero, would give the required results. You could if
you wished break it down by project by joining the projects table to the
Clients table in the outer query and correlating the subqueries to the outer
query on ProjectID rather than ClientID.

SELECT CompanyName,
(SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) AS BalanceOutstanding
FROM Clients
WHERE (SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) > 0;

Ken Sheridan
Stafford, England

Hello. I am trying to create a report or a query to spit out a report
of what clients still have outstanding balances (and the balance) . I
have tried to past the client subform into a report, and all I get is
'#NAME?' in the 'Amount Due' column.
Anyone get something like that going in this template?

Thanks in advance,

-J

 

[Jason]

It worked. Thanks alot (from across the pond)!!!
I will look into the syntax so I can design this myself next time.

Cheers,

-J

Try catering for Nulls with the NZ function:

SELECT CompanyName,
NZ((SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time Card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
+NZ((SELECT SUM(BillableHours*BillingRate)
FROM [Time Card Hours], Projects
WHERE [Time Card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
-NZ((SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0) AS BalanceOutstanding
FROM Clients
WHERE
NZ((SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time Card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
+NZ((SELECT SUM(BillableHours*BillingRate)
FROM [Time Card Hours], Projects
WHERE [Time Card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0)
-NZ((SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID),0) > 0;

Ken Sheridan
Stafford, England

That query isn't bringing anything up.

The query in design view shows the criteria as:
BalanceOutstanding: (SELECT SUM(ExpenseAmount)

I pasted your query in the query in SQL view verbatim.

Anyway, I appreciate your help. My SQL programming skills leave a bit
to be desired...

-J

I'd have thought a query along these lines, which returns all clients and the
sum of all time card billing amounts and expenses in relation to each
client's projects less the sum of all payments in relation to the project,
where this is more than zero, would give the required results. You could if
you wished break it down by project by joining the projects table to the
Clients table in the outer query and correlating the subqueries to the outer
query on ProjectID rather than ClientID.

SELECT CompanyName,
(SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) AS BalanceOutstanding
FROM Clients
WHERE (SELECT SUM(ExpenseAmount)
FROM [Time Card Expenses], Projects
WHERE [Time card Expenses].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
+(SELECT SUM(BillableHours*BillingRate)
FROM [Time card Hours], Projects
WHERE [Time card Hours].ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID)
-(SELECT SUM(PaymentAmount)
FROM Payments, Projects
WHERE Payments.ProjectID = Projects.ProjectID
AND Projects.ClientID = Clients.ClientID) > 0;

Ken Sheridan
Stafford, England

:

Hello. I am trying to create a report or a query to spit out a report
of what clients still have outstanding balances (and the balance) . I
have tried to past the client subform into a report, and all I get is
'#NAME?' in the 'Amount Due' column.
Anyone get something like that going in this template?

Thanks in advance,

-J

 

[Guest]

Glad to have helped. If you look at each subquery separately you'll see they
are really pretty straightforward, just summing the time card hours, time
card expenses and the payments. In each case the sums for the current client
only are computed by having the subquery reference the client ID of the outer
query. This is known as a correlated subquery. The first two subquery's
results are added together to get the total debits for the client, and the
last (the total credits) is subtracted from this to give the outstanding
balance.

The outer queries WHERE clause repeats the same subqueries and checks to see
if the balance is greater than zero, so only the clients who currently owe
money are returned by the outer query.

The reason it didn't work first time, I'd guess, is that there were only
hourly costs and no expenses recorded, so the expenses subquery returned
NULL. Now NULL is not the same as zero. Its not a value at all but the
absence of a value. The nearest thing we can say as regards its meaning is
that its an 'unknown value'. A property of NULL in arithmetical operations
is that it propagates. This means that whatever the other values in the
operation are the result will always be NULL. This makes sense if you think
about it because 10 + 'unknown value' must = 'unknown value' as it could be
10 + 0, 10 + 10, 10 +1,000 or any other value you can think of, so the result
is a range of possible values from minus infinity to infinity, i.e. its
unknown and therefore NULL. To handle situations like this Access provides
the NZ function which returns a value, by default zero, in place of a NULL.
So by applying this function to each subquery the result returned by the
function is zero when the query returns a NULL, otherwise the actual value
returned by the query.

Correlated subqueries can be quite slow sometimes. Its very important that
the table is indexed appropriately to improve performance (this applies
equally to joins). In this case the ClientID as the primary will be indexed
uniquely so the correlations on this columns should be pretty efficient.
More complex correlated subqueries which might not be able to make efficient
use of the indexes, especially where the subqueries themselves have
correlated subqueries can be slow. Sometimes a JOIN can be used instead and
this will usually be more efficient. Sometimes breaking it down into
separate queries and joining the queries can improve performance.

Ken Sheridan
Stafford, England