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?

-- 
Message posted via http://www.accessmonster.com

In query design, type an expression like this into the Field row:
    Nz([a],0) + Nz([b],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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"enrico via AccessMonster.com" <u41845@uwe> wrote in message 
news:a00ffd9cbffe9@uwe...
> 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?
 

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?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"enrico via AccessMonster.com" <u41845@uwe> wrote in message 
news:a011d04a3d313@uwe...
> 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? 

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 wrote:
> 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?
> 

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"

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

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

enrico via AccessMonster.com wrote:
> 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"
>