sutraction after sum

[enrico via AccessMonster.com]

if i have data a, b, c, up to z in my table with specific amounts, how do i
get their sub-total if i don't want to include data l, m, n, o & p? how can
you sum it up and subtract those specific data that are excluded? what is the
code?

 

[Allen Browne]

In query design, type an expression like this into the Field row:
Nz([a],0) + Nz(,0) + Nz([c],0) + ...

The real problem here is that you've build a spreadsheet in Access.
Repeating columns like that is not the way you store data in a database: it
gives you exactly the kind of querying problems you are experiencing.

For info about the Nz() workaround to solve nulls, see item #2 in this
article:
http://allenbrowne.com/casu-12.html

 

[enrico via AccessMonster.com]

a, b, c, etc. are just representations. i have a table with field data
"Banks", "TransCount" and "Amount". for example, if have 30 banks and i want
to get the sum of the amount entered minus the amount of 5 banks. how will i
get it?

 

[Allen Browne]

Whatever the fields are called, use Nz() around each of them in your
expression.

If you have 30 banks represented as 30 columns, your schema is not
normalized.

 

[John Spencer]

Are you trying to get the sum in a query?
What method do you have to identify the banks you don't want?

SELECT Sum(Amount) as Total
FROM SomeTable
WHERE Banks Not In ("First National","Bank of America")

Or use the VBA DSUM function
DSUM("Amount","SomeTable","Banks Not In ('First National','Bank of America')")

If you need more detail in the response you need to provide more detail on the
what you want.

By the way, in query design view
== Add your table
== Add the Banks and Amount fields
== Under banks enter criteria to exclude the banks you don't want
Not IN ('First National','Bank of America')
== Select View: Totals from the menu
== Change GROUP BY to WHERE under Banks field
== Change GROUP BY to SUM under the Amount field

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[enrico via AccessMonster.com]

thank you. that's exactly what i wanted. but can you add another condition to
the query? for example, aside from the banks that are excluded you add a
condition that it will only show those transactions with a field in my table
"walk-in"

 

[John Spencer]

You are a bit unclear on what you want. Hopefully this is what you want

SELECT Sum(Amount) as Total
FROM SomeTable
WHERE Banks Not In ("First National","Bank of America")
AND SomeField = "Walk-In"

== Add the field that contains Walk in to the query
== Enter the following in the criteria for that field
"Walk-In"

If that fails, then you need to take a look at the field and determine its
field type.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County