show all data in a totals query

  • Thread starter Thread starter Wei
  • Start date Start date
W

Wei

In a totals query, I want to group my dada by ID, which
has a range of 1-10; and sum the premiums. Say my dada
only has IDs 1, 2 and 8. So when I run query, it's going
to give me stuff like:

ID SumOfPremium
1 1000
2 200
8 678

Is there a way to make the query give me the other IDs and
show the sums as zeros? ie, something like this:

ID SumOfPremium
1 1000
2 200
3 0
4 0
5 0
6 0
7 0
8 678
9 0
10 0

Thanks for any suggestions!

Wei
 
Wei said:
In a totals query, I want to group my dada by ID, which
has a range of 1-10; and sum the premiums. Say my dada
only has IDs 1, 2 and 8. So when I run query, it's going
to give me stuff like:

ID SumOfPremium
1 1000
2 200
8 678

Is there a way to make the query give me the other IDs and
show the sums as zeros? ie, something like this:

ID SumOfPremium
1 1000
2 200
3 0
4 0
5 0
6 0
7 0
8 678
9 0
10 0
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you don't tell us what type of query (joined or on a single table)
this is only a guess:

SELECT ID, Sum(Premium) As PremiumTotal
FROM TableName
GROUP BY ID

For a joined query:

SELECT T1.ID, Sum(T2.Premium) As PremiumTotal
FROM TableName1 As T1 LEFT JOIN TableName2 As T2
ON T1.ID = T2.ID
GROUP BY T1.ID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIQrrIechKqOuFEgEQLbJgCfVtN/wdOD8R6AnAoOIvTOMGAdKhUAnRWQ
x9G26MsuQZyHJiXHX+jZOc7g
=1lvM
-----END PGP SIGNATURE-----
 
well, that cannot work...because it's only grouping and
couldn't give me all the items in the range...

now I'm thinking maybe there isn't a way to do this...

Wei
 
Wei said:
well, that cannot work...because it's only grouping and
couldn't give me all the items in the range...

now I'm thinking maybe there isn't a way to do this...
Click to expand...

< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

As I said in my reply to your original post, we don't have much info to
go on. Perhaps if you posted your query we could figure out a solution
to your problem.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIWbR4echKqOuFEgEQIYYACglU9+scfJWHBiQQsu2cnW+mgED/QAoKU4
tNGml3MmhXyzyO5Q/4idJV9t
=jtjF
-----END PGP SIGNATURE-----
 
Back
Top