sub totals in query

[green fox]

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

[MGFoster]

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

[Alan Webb]

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" <[e-mail]> 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?

[green fox]

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.

[MGFoster]

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)

[MGFoster]

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)