SQL querie

[Guest]

Would someone please explain why this does not work!! I have a table with
different values in it and all I want to do is write a query that tells me
the number of entries between $0 & 100, $101 & 500 and so on. There is some
basic concept that I grossly misunderstand.

Thank You

SELECT Count(*) AS Expr1
FROM [tbl-Bond]
WHERE (((Sum([BondAmount]))>1000000));

 

[Marshall Barton]

Would someone please explain why this does not work!! I have a table with
different values in it and all I want to do is write a query that tells me
the number of entries between $0 & 100, $101 & 500 and so on. There is some
basic concept that I grossly misunderstand.

SELECT Count(*) AS Expr1
FROM [tbl-Bond]
WHERE (((Sum([BondAmount]))>1000000));

I think the missing concept is the Group By clause. Note
that a query with a Group By clause is also known as a
Totals type query in the query design window.

In order to use it though, you need to have a way to specify
the divisions to group on. For nice evenly spaced
divisions, you could use the Partition function, but for
irregularly spaced divisions, you need a more general
approach.

Create a table with at least three fields:
table: BondDivisions
RLow Currency
RHigh Currency
RDesc Text

Populate the table with records like:
0 100 0 - 100
101 500 101 - 500
. . .

Now create the query:

SELECT B.RDesc, Count(*) AS DivisionCount
FROM BondDivisions As B LEFT JOIN [tbl-Bond] As T
ON T.BondAmount Between B.RLow And B.RHigh
GROUP BY B.RDesc
ORDER BY B.RLow

 

[Guest]

This table has amounts in it from $100 to 5million. I want to know the
number of entries that are between $100 and $1000, are you saying I have to
have a table for each division that I want??

Would someone please explain why this does not work!! I have a table with
different values in it and all I want to do is write a query that tells me
the number of entries between $0 & 100, $101 & 500 and so on. There is some
basic concept that I grossly misunderstand.

SELECT Count(*) AS Expr1
FROM [tbl-Bond]
WHERE (((Sum([BondAmount]))>1000000));

I think the missing concept is the Group By clause. Note
that a query with a Group By clause is also known as a
Totals type query in the query design window.

In order to use it though, you need to have a way to specify
the divisions to group on. For nice evenly spaced
divisions, you could use the Partition function, but for
irregularly spaced divisions, you need a more general
approach.

Create a table with at least three fields:
table: BondDivisions
RLow Currency
RHigh Currency
RDesc Text

Populate the table with records like:
0 100 0 - 100
101 500 101 - 500
. . .

Now create the query:

SELECT B.RDesc, Count(*) AS DivisionCount
FROM BondDivisions As B LEFT JOIN [tbl-Bond] As T
ON T.BondAmount Between B.RLow And B.RHigh
GROUP BY B.RDesc
ORDER BY B.RLow

 

[Marshall Barton]

Abolutely not. The single table I proposed would provide
the counts for all the divisions in the BondDivisions table
in one fell swoop. The output from the query I posted would
look like:
0 - 100 1234
101 - 500 5678
. . .

Are you saying that that's not what you want and you do not
have a relatively fixed set of division boundaries? You
only want a query to calculate the count for one arbitrary
division? If so, then, you were close with your original
query:

SELECT Count(*) As DivisionCount
FROM [tbl-Bond] As T
WHERE T.BondAmount Between 100 And 1000

But that's a different question than I thought you were
asking before. Sorry, but I just don't see where this query
is very useful.
--
Marsh
MVP [MS Access]

This table has amounts in it from $100 to 5million. I want to know the
number of entries that are between $100 and $1000, are you saying I have to
have a table for each division that I want??

Would someone please explain why this does not work!! I have a table with
different values in it and all I want to do is write a query that tells me
the number of entries between $0 & 100, $101 & 500 and so on. There is some
basic concept that I grossly misunderstand.

SELECT Count(*) AS Expr1
FROM [tbl-Bond]
WHERE (((Sum([BondAmount]))>1000000));

I think the missing concept is the Group By clause. Note
that a query with a Group By clause is also known as a
Totals type query in the query design window.

In order to use it though, you need to have a way to specify
the divisions to group on. For nice evenly spaced
divisions, you could use the Partition function, but for
irregularly spaced divisions, you need a more general
approach.

Create a table with at least three fields:
table: BondDivisions
RLow Currency
RHigh Currency
RDesc Text

Populate the table with records like:
0 100 0 - 100
101 500 101 - 500
. . .

Now create the query:

SELECT B.RDesc, Count(*) AS DivisionCount
FROM BondDivisions As B LEFT JOIN [tbl-Bond] As T
ON T.BondAmount Between B.RLow And B.RHigh
GROUP BY B.RDesc
ORDER BY B.RLow