Count books with start and end numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ripper - 06 Nov 2007 17:45 GMT

How do I count a series of books with start and end book numbers?
I have a query, qryBooks, that has a box number and a start and end value
for a series of books (100000 - 100010) 11 books.

How do I get a query to count the books in the series and return a total to
me?

Signature

Thanks As Always
Rip

Reply to this Message

Michel Walsh - 06 Nov 2007 18:14 GMT

DCount("*", "tableNameHere", "BookSerialNumber BETWEEN
FORMS!formName!StartingSerialNumber AND FORMS!formName!EndingSerialNumber" )

Hoping it may help,
Vanderghast, Access MVP

> How do I count a series of books with start and end book numbers?
> I have a query, qryBooks, that has a box number and a start and end value
[quoted text clipped - 3 lines]
> to
> me?

Reply to this Message

Marshall Barton - 06 Nov 2007 18:25 GMT

>How do I count a series of books with start and end book numbers?
>I have a query, qryBooks, that has a box number and a start and end value
>for a series of books (100000 - 100010) 11 books.
>
>How do I get a query to count the books in the series and return a total to
>me?

SELECT box, endnumber - startnumber + 1
FROM sometable

or a total of all boxes

SELECT Sum(endnumber - startnumber + 1)
FROM sometable

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Spencer - 06 Nov 2007 18:58 GMT

Use the expression

EndValue-StartValue +1

to calculate how many books in the box.

If you want to get the sum of those numbers
SUM(EndValue-StartValue +1 ) as CountBooksInGroup

You didn't say how or even if you were grouping items.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> How do I count a series of books with start and end book numbers?
> I have a query, qryBooks, that has a box number and a start and end value
[quoted text clipped - 3 lines]
> to
> me?

Reply to this Message