If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sutraction after sum
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 |
#2
|
|||
|
|||
sutraction after sum
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? |
#3
|
|||
|
|||
sutraction after sum
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/For...eries/200912/1 |
#4
|
|||
|
|||
sutraction after sum
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? |
#5
|
|||
|
|||
sutraction after sum
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? |
#6
|
|||
|
|||
sutraction after sum
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/For...eries/200912/1 |
#7
|
|||
|
|||
sutraction after sum
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" |
Thread Tools | |
Display Modes | |
|
|