Calculation Help Needed

[Marina]

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

[Bob Quintal]

"Marina" <ma*********@earthlink.net> wrote in
news:11**********************@j52g2000cwj.googlegr oups.com:

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

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.

[Marina]

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

[Bob Quintal]

"Marina" <ma*********@earthlink.net> wrote in
news:11**********************@v46g2000cwv.googlegr oups.com:

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

--
Bob Quintal

PA is y I've altered my email address.