Calculation Help Needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marina - 15 Mar 2006 22:56 GMT

I have a querie that needs to use multiple calculations (...I think...)

Fields:
TransID
TransDate
TransDescipt
ClientID
HoursBilled
BillingRate
AmountBilled
AmountPaid

Amount Billed is a calculation of HoursBilled*BillingRate

I need to add a field for AccountBalance that will keep a running total
of AmountBilled-AmountPaid for each ClientID.

I'm pretty sure I need to use group by, but how do I do the running
totals?? Do I need to do a sum of all AmountBilled and AmountPaid and
go from there??

Please help... feeling lost and kinda stupid here... just can't think
after my surgery 3 weeks ago... grrrrr

Marina

Reply to this Message

Bob Quintal - 16 Mar 2006 00:48 GMT

> I have a querie that needs to use multiple calculations (...I
> think...)
[quoted text clipped - 22 lines]
>
> Marina

You do not want to add the field to the table, you recalculate
running totals on the fly when you want them. You do this by
building a sub-query or by using the dsum() aggregate function.

The idea is to sum AmountBilled-AmountPaid where ClientID in the
sub-query is equal to the ClientID in your table, and
BillingDate in the query <= Billingdate in the table.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

Marina - 18 Mar 2006 00:29 GMT

I can do this as it's own query, but can't seem to make a sub query
work.....

Main Query is:

SELECT ClientTransactions.ClientID, [client info].[Client FirstName],
[client info].[Client LastName], [client info].[Client Address],
[client info].[Client City], [client info].[Client State], [client
info].[Client Zip], ClientTransactions.TransactionID,
ClientTransactions.TransactionDate,
ClientTransactions.TransactionDescription,
ClientTransactions.HoursBilled, ClientTransactions.BillingRate,
ClientTransactions!HoursBilled*ClientTransactions!BillingRate AS
AmountBilled, ClientTransactions.AmountPaid
FROM [client info]
INNER JOIN ClientTransactions ON [client info].[Client ID] =
ClientTransactions.ClientID;

Sub-Query should be something like:

SELECT DISTINCTROW [client info].[Client ID],
Sum(ClientTransactions.HoursBilled) AS [Sum Of HoursBilled],
Sum(ClientTransactions.BillingRate) AS [Sum Of BillingRate],
Sum(ClientTransactions.AmountPaid) AS [Sum Of AmountPaid], ([Sum Of
HoursBilled]*[Sum Of BillingRate])-[Sum Of AmountPaid] AS Balance
FROM [client info] I
NNER JOIN ClientTransactions ON [client info].[Client ID] =
ClientTransactions.ClientID
GROUP BY [client info].[Client ID];

Reply to this Message

Bob Quintal - 18 Mar 2006 01:25 GMT

> I can do this as it's own query, but can't seem to make a sub
> query work.....
[quoted text clipped - 27 lines]
> ClientTransactions.ClientID
> GROUP BY [client info].[Client ID];

you said in a previous post:

> I need to add a field for AccountBalance that will keep a
> running total of AmountBilled-AmountPaid for each ClientID.

that would be:
(SELECT sum([HoursBilled]*BillingRate]-[AmountPaid]) from
[clientTransactions] alias sqry WHERE sqry.clientID=[client
info].[clientID] and sqry.TransactionDate <= TransactionDate) as
Balance,

you'd put that as a single field in your main query. To get your
multiple columns, you will need to create multiple subqueries
each returning a single field.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message