446,283 Members | 1,709 Online
Need help? Post your question and get tips & solutions from a community of 446,283 IT Pros & Developers. It's quick & easy.

# sub totals in query

 P: n/a In a query, a category has a number of products identified by numbers, i.e. 240, 300, 560 etc. Each of these products can have any number of items each with its own value. I need the sum of various groups of products. Say products from 240 to 330, sum of the values. Sum of values from 340 through 456, AND 990 through 1240. If there is a simple solution that I'm just missing, please point me in the right direction. Is it possible to do this in a simple query? Thanks, Andy Fox Nov 13 '05 #1
5 Replies

 P: n/a -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Use something like this: SELECT Switch(Product Between 240 And 330, "A", Product Between 340 And 456, "B", Product Between 990 And 1240, "C") As ProductGroup, Sum(value_column) As ValueTotal FROM ... WHERE ... GROUP BY Switch(Product Between 240 And 330, "A", Product Between 340 And 456, "B", Product Between 990 And 1240, "C") If the Between doesn't work use: Switch(240 <= Product And Product <= 330, "A", 340 <= Product And Product <= 456, "B", 990 <= Product And Product <= 1240, "C") -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQlSKMIechKqOuFEgEQI2gQCeIG/537dKVRhQ4+DWtxzWHpvD7CEAoOF0 iK43JQQ3lfl6S9DiSPGkArTl =lvSc -----END PGP SIGNATURE----- green fox wrote: In a query, a category has a number of products identified by numbers, i.e. 240, 300, 560 etc. Each of these products can have any number of items each with its own value. I need the sum of various groups of products. Say products from 240 to 330, sum of the values. Sum of values from 340 through 456, AND 990 through 1240. If there is a simple solution that I'm just missing, please point me in the right direction. Is it possible to do this in a simple query? Nov 13 '05 #2

 P: n/a MGFoster, Switch? Like TransactSQL CASE? Neat. Didn't know that was supported in Jet SQL. -- Alan Webb kn*******@SPAMhotmail.com "It's not IT, it's IS "MGFoster" wrote in message news:SS*****************@newsread2.news.pas.earthl ink.net... -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Use something like this: SELECT Switch(Product Between 240 And 330, "A", Product Between 340 And 456, "B", Product Between 990 And 1240, "C") As ProductGroup, Sum(value_column) As ValueTotal FROM ... WHERE ... GROUP BY Switch(Product Between 240 And 330, "A", Product Between 340 And 456, "B", Product Between 990 And 1240, "C") If the Between doesn't work use: Switch(240 <= Product And Product <= 330, "A", 340 <= Product And Product <= 456, "B", 990 <= Product And Product <= 1240, "C") -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQlSKMIechKqOuFEgEQI2gQCeIG/537dKVRhQ4+DWtxzWHpvD7CEAoOF0 iK43JQQ3lfl6S9DiSPGkArTl =lvSc -----END PGP SIGNATURE----- green fox wrote: In a query, a category has a number of products identified by numbers, i.e. 240, 300, 560 etc. Each of these products can have any number of items each with its own value. I need the sum of various groups of products. Say products from 240 to 330, sum of the values. Sum of values from 340 through 456, AND 990 through 1240. If there is a simple solution that I'm just missing, please point me in the right direction. Is it possible to do this in a simple query? Nov 13 '05 #3

 P: n/a I've done next to nothing with SQL, so it took me a bit to figure out your solution. Botton line, it worked great. Thanks for your quick response. Can anyone recommend a source for Access Jet SQL info...where I can learn a bit more about syntax? Andy F. Nov 13 '05 #4

 P: n/a green fox wrote: I've done next to nothing with SQL, so it took me a bit to figure out your solution. Botton line, it worked great. Thanks for your quick response. Can anyone recommend a source for Access Jet SQL info...where I can learn a bit more about syntax? Try the Access Help. Look under the Contents tab at "Microsoft JET SQL Reference." -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) Nov 13 '05 #5

 P: n/a Alan Webb wrote: MGFoster, Switch? Like TransactSQL CASE? Neat. Didn't know that was supported in Jet SQL. All VBA functions are usable in JET SQL. -- MGFoster:::mgf00 earthlink net Oakland, CA (USA) Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.