Re: Calculating two fields on based of the value of one field

Tech-Archive recommends: Fix windows errors by optimizing your registry

Is that query working? If so, it looks like you already have your Credit and
Debit totals. What problem are you having?

Carl Rapson

"Daljeet Bajwa" <DaljeetBajwa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:05609CDD-C5F0-41FC-8ED8-CC931DA60E1C@xxxxxxxxxxxxxxxx
Thanks Carl but I forget clarify my question. First of all, here is my SQL
Statement :

SELECT tblAtlantic.[Cardholder Name], "************" &
Right(tblAtlantic.[Card Number],4) AS [Secured Number],
tblAtlantic.[Accounting Code], tblAtlantic.[Merchant Name] & " " &
tblAtlantic.[MCC code] AS Merchant, Left(tblAtlantic![Posting Date],4) &
"/"
& Mid(tblAtlantic![Posting Date],5,2) & "/" & Right(tblAtlantic![Posting
Date],2) AS [Formatted Posting Date], tblAtlantic.[Gross Amount],
tblAtlantic.[National Tax], tblAtlantic.[Net Amount],
Left(tblAtlantic![Cycle
Date],4) & "/" & Mid(tblAtlantic![Cycle Date],5,2) & "/" &
Right(tblAtlantic![Cycle Date],2) AS [Formatted cycle Date],
IIf(Left(tblAtlantic.[Gross Amount],1)="-","C","D") AS [Credit Status],
DSum(tblAtlantic.[Gross Amount],"tblAtlantic",[Credit Status]='C') AS
[Total
Credits], DSum(tblAtlantic.[Gross Amount],"tblAtlantic",[Credit
Status]='D')
AS [Total Debits]
FROM tblAtlantic;

I am using this query for a report and on my report I need two separate
totals for credits and debits. Any help will greatly appreciated. Thanks.

Regards

"Carl Rapson" wrote:

"Daljeet Bajwa" <DaljeetBajwa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9A62D82C-E44B-45F7-9B31-BA6A038CBBC3@xxxxxxxxxxxxxxxx
My table has a amount field which contains Credits and debits. I have
another
field which has two values C for Credit and D for Debit.
I would like to sum up both credits and debits into two different
fields.
How can I accomplish that? Thanks.


Credits = DSum("Amount", "MyTable","AnotherField='C'")
Debits = DSum("Amount","MyTable","AnotherField='D'")

Note the single quotes around the C and D in the third parameter. Be sure
to
use you own field and table names.

Carl Rapson





.

Relevant Pages

  • Re: Checkbook Balance with 2 Accounts
    ... it would be easier to store Credits and Debits together ... credit) negative numbers -- then, you just have one field to ... Name --> Balance ... only one checkbook account, that was pretty simple. ...
    (microsoft.public.access.formscoding)
  • Re: Topic Sentences
    ... >>> the balance sheet because an increase in assets has the same effect as ... from the standpoint of the total worth of the ... >> increase accounts and debits decrease them. ... When you credit someone with something, ...
    (rec.arts.sf.composition)
  • AR Aging
    ... credits to the debits to ultimately age out only the ... records with outstanding debits. ... credit totals by account ... >from each line of transactions, so as to only age the ...
    (microsoft.public.access.queries)
  • Aging Function
    ... SELECT [Debits by Trx Date Copy].[Active or Inactive ... ") AS OlderDebits, [Total Credits By Account ... After I get the dsum calculation to work within Query 1, ... >the total credit amount, and use that to calculate your ...
    (microsoft.public.access.modulesdaovba)
  • Re: Checkbook Balance with 2 Accounts
    ... btw, it would be easier to store Credits and Debits together using, ie: an Amount field -- make one of them negative numbers -- then, you just have one field to sum. ... you can still have 2 columns -- one for Debit and one for Credit -- use the ABS function to make all numbers display as positive. ... Your balance calculations will be faster if they are on just one field instead of 2. ... Since there's only one checkbook account, ...
    (microsoft.public.access.formscoding)