By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,283 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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 <at> earthlink <decimal-point> 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" <me@privacy.com> 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 <at> earthlink <decimal-point> 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 <at> earthlink <decimal-point> 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 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.