Counting Objects not summing numbers

R

Ripper

I have a string of objects with numbers that I must "Count"
IE. 101, 102, 103, 104, 105 There are 5 objects in this string. The
numbers are different for each string and can vary from 0001 to 9999.

I want to count the number of objects that I have. I do not store all the
numbers, I only store the start and end numbers of each string and then use a
calculated field with =[EndNum]-[StartNum]+1

I will have multiple strings of objects in 1 box and to calculate the total
number in a box would be difficult.

Is there a function that I can use in place of this calculation? It would
make things easier for me.
 
M

Michel Walsh

If you have 5 objects per "row", that would be a matter to count the number
of rows and to multiply by 5,

unless you are interested by the number of DIFFERENT objects.


Untested, but a first query like:

SELECT SPLIT(fieldName ,"," )(0) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & "," , "," )(1) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",," , "," )(2) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,," , "," )(3) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,,," , "," )(4) AS objName FROM myTable

to be saved under the name, say, qu1, then


SELECT COUNT(objName) FROM qu1

or

DCOUNT("objName", "qu1")


would return the number of different objects.

You need Access 2003 or later, though, to have the VBA predefined function
SPLIT working well inside a query.



Hoping it may help,
Vanderghast, Access MVP
 
K

KARL DEWEY

I understood you to have five numbers in the same field. If this is how you
have your data stored then you need to change it so that each is in a
separate record. By using a separate record it will be easy to count or sum.
--
KARL DEWEY
Build a little - Test a little


Michel Walsh said:
If you have 5 objects per "row", that would be a matter to count the number
of rows and to multiply by 5,

unless you are interested by the number of DIFFERENT objects.


Untested, but a first query like:

SELECT SPLIT(fieldName ,"," )(0) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & "," , "," )(1) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",," , "," )(2) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,," , "," )(3) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,,," , "," )(4) AS objName FROM myTable

to be saved under the name, say, qu1, then


SELECT COUNT(objName) FROM qu1

or

DCOUNT("objName", "qu1")


would return the number of different objects.

You need Access 2003 or later, though, to have the VBA predefined function
SPLIT working well inside a query.



Hoping it may help,
Vanderghast, Access MVP




Ripper said:
I have a string of objects with numbers that I must "Count"
IE. 101, 102, 103, 104, 105 There are 5 objects in this string. The
numbers are different for each string and can vary from 0001 to 9999.

I want to count the number of objects that I have. I do not store all the
numbers, I only store the start and end numbers of each string and then
use a
calculated field with =[EndNum]-[StartNum]+1

I will have multiple strings of objects in 1 box and to calculate the
total
number in a box would be difficult.

Is there a function that I can use in place of this calculation? It would
make things easier for me.
Click to expand...
Click to expand...
 
R

Ripper

My table is set up to contain a start number and end number of a series of
objects. To calculate the number of objects in the string I have to subtract
the start number from the end number and add 1 to "count" the number of
objects in the string of numbers.

ie. packages 101, 102, 103, 104, 105 etc.

tblContents stores boxNum (from user) the start number 101 and end number
105.

I just wondered if there was a way to count the books without actually
creating a calculated field formula. I wanted to know if there was a
function dedicated to counting like =Count([StartNum] through [EndNum]) or
something like that.

I can't use Sum because I am not adding the numbers together, I am counting
them.

Let me know if I make sense.


Rip


KARL DEWEY said:
I understood you to have five numbers in the same field. If this is how you
have your data stored then you need to change it so that each is in a
separate record. By using a separate record it will be easy to count or sum.
--
KARL DEWEY
Build a little - Test a little


Michel Walsh said:
If you have 5 objects per "row", that would be a matter to count the number
of rows and to multiply by 5,

unless you are interested by the number of DIFFERENT objects.


Untested, but a first query like:

SELECT SPLIT(fieldName ,"," )(0) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & "," , "," )(1) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",," , "," )(2) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,," , "," )(3) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,,," , "," )(4) AS objName FROM myTable

to be saved under the name, say, qu1, then


SELECT COUNT(objName) FROM qu1

or

DCOUNT("objName", "qu1")


would return the number of different objects.

You need Access 2003 or later, though, to have the VBA predefined function
SPLIT working well inside a query.



Hoping it may help,
Vanderghast, Access MVP




Ripper said:
I have a string of objects with numbers that I must "Count"
IE. 101, 102, 103, 104, 105 There are 5 objects in this string. The
numbers are different for each string and can vary from 0001 to 9999.

I want to count the number of objects that I have. I do not store all the
numbers, I only store the start and end numbers of each string and then
use a
calculated field with =[EndNum]-[StartNum]+1

I will have multiple strings of objects in 1 box and to calculate the
total
number in a box would be difficult.

Is there a function that I can use in place of this calculation? It would
make things easier for me.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this ---
Sum([EndNum]-[StartNum]+1)
--
KARL DEWEY
Build a little - Test a little


Ripper said:
My table is set up to contain a start number and end number of a series of
objects. To calculate the number of objects in the string I have to subtract
the start number from the end number and add 1 to "count" the number of
objects in the string of numbers.

ie. packages 101, 102, 103, 104, 105 etc.

tblContents stores boxNum (from user) the start number 101 and end number
105.

I just wondered if there was a way to count the books without actually
creating a calculated field formula. I wanted to know if there was a
function dedicated to counting like =Count([StartNum] through [EndNum]) or
something like that.

I can't use Sum because I am not adding the numbers together, I am counting
them.

Let me know if I make sense.


Rip


KARL DEWEY said:
I understood you to have five numbers in the same field. If this is how you
have your data stored then you need to change it so that each is in a
separate record. By using a separate record it will be easy to count or sum.
--
KARL DEWEY
Build a little - Test a little


Michel Walsh said:
If you have 5 objects per "row", that would be a matter to count the number
of rows and to multiply by 5,

unless you are interested by the number of DIFFERENT objects.


Untested, but a first query like:

SELECT SPLIT(fieldName ,"," )(0) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & "," , "," )(1) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",," , "," )(2) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,," , "," )(3) AS objName FROM myTable
UNION
SELECT SPLIT(fieldName & ",,,," , "," )(4) AS objName FROM myTable

to be saved under the name, say, qu1, then


SELECT COUNT(objName) FROM qu1

or

DCOUNT("objName", "qu1")


would return the number of different objects.

You need Access 2003 or later, though, to have the VBA predefined function
SPLIT working well inside a query.



Hoping it may help,
Vanderghast, Access MVP




I have a string of objects with numbers that I must "Count"
IE. 101, 102, 103, 104, 105 There are 5 objects in this string. The
numbers are different for each string and can vary from 0001 to 9999.

I want to count the number of objects that I have. I do not store all the
numbers, I only store the start and end numbers of each string and then
use a
calculated field with =[EndNum]-[StartNum]+1

I will have multiple strings of objects in 1 box and to calculate the
total
number in a box would be difficult.

Is there a function that I can use in place of this calculation? It would
make things easier for me.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count the numbers in a Word document? 0
Retrieve all entries in table1 eventho they're not matched in tabl 5
auto index 1
index 1
Source and Destination 1
numbers in a column are not sorting ascending 2
counting from different tables 1
Counting unique instances of a text string 2

Top