Calculation Help Needed

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

re: Calculation Help Needed

"Marina" <maiden_moon@earthlink.net> wrote in
news:1142459789.491468.310320@j52g2000cwj.googlegr oups.com:
[color=blue]
> 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
>[/color]
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.

--
Bob Quintal

PA is y I've altered my email address.

re: Calculation Help Needed

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];

re: Calculation Help Needed

"Marina" <maiden_moon@earthlink.net> wrote in
news:1142638178.293829.304260@v46g2000cwv.googlegr oups.com:
[color=blue]
> 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];
>[/color]
you said in a previous post:[color=blue]
> I need to add a field for AccountBalance that will keep a
> running total of AmountBilled-AmountPaid for each ClientID.
>[/color]

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.



--
Bob Quintal

PA is y I've altered my email address.