Calculating two fields on based of the value of one field

[Daljeet Bajwa]

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.

 

[Carl Rapson]

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

 

[Daljeet Bajwa]

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]

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

Carl Rapson

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

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