You are here:

Using MS Access/Count the number of records between the missing numbers

Advertisement


Question
QUESTION: I have a list of Registration numbers in a table. Certain roll numbers are deleted from the list. Could you help me with a query that calculates the number of records between the missing numbers?

Example of the records in the table are as follows:-
RegNo
MBS0952
MBS0953
MBS0954
MBS0956
MBS0957
MBS0978
MBS0959
MBS0961
MBS0962

Is it possible for me to get the query result as follows:-

MBS0952 to MBS0954 = 3
MBS0956 to MBS0959 = 4
MBS0961 to MBS0962 = 2

I have the missing numbers in another table. But I donít know how to work out with it.

ANSWER: Hi Prasad,

Many thanks for the question.

Note: I have only included the first step here, but it will be something to get you started.  I will finish it off in a second installment, coming soon. Running out of time to do it all in one sitting.

To achieve this try the following:

Create a query based on the table above, adding the field that contains the above information.

Then in the next available column on the top row add the following:

NumberPart: Mid([RegNo],4)

This will just display the numbers from the field.

Sort this field in Ascending order.

Then in the next column type the following:


NumberCount: fCount([NumberPart])

This runs some code, to return the count section.

To add the code.

Add a new module (not sure what version of Microsoft Access you are using, so if you do not know how to do this let me know and let me know what version of Microsoft Access you are using as the instructions will be slightly different.)

To this module add the following code:

'********Code Start

Option Compare Database
Option Explicit
Public NumberHolder As Double
Public CountHolder As Double
Public str1 As String
Public str2 As String



Public Function fCount(ThisNumber As Double, RegNo As String)

Dim LessOne As Double
Dim iCount As Integer

LessOne = ThisNumber - 1

If LessOne = NumberHolder Then
CountHolder = CountHolder + 1
str2 = RegNo
Else
CountHolder = 1
str1 = RegNo
End If

fCount = str1 & " to " & str2 & " = " & CountHolder

NumberHolder = ThisNumber

End Function

'*****Code End

Run your guery and you should get a result similar to this:

RegNo   NumberPart   NumberCount
MBS0952   0952   MBS0952 to MBS0962 = 1
MBS0953   0953   MBS0952 to MBS0953 = 2
MBS0954   0954   MBS0952 to MBS0954 = 3
MBS0956   0956   MBS0956 to MBS0954 = 1
MBS0957   0957   MBS0956 to MBS0957 = 2
MBS0959   0959   MBS0959 to MBS0957 = 1
MBS0961   0961   MBS0961 to MBS0957 = 1
MBS0962   0962   MBS0961 to MBS0962 = 2
MBS0978   0978   MBS0978 to MBS0962 = 1

Your numbers will be different to mine.

The next part will be to extract the highest count number for each set.

Regards







---------- FOLLOW-UP ----------

QUESTION: Thank you very much for sparing some of your precious time to give me the answer. I am sorry i did not mention that i am using access 2000.

When i try to run the query i get the message - (Undefined function 'fCount' in expression).

Then when i tried selecting Count in the total row of NumberCount column of the query I could run the query with NumberCount = 1 for all the records. I don't know if this is a correct step.

ANSWER: Hi Prasad,

Just checking you have added the code as per the instructions above.  If you have and are still getting the error let me know.

The query needs the code for it to run correctly otherwise you will get the message you mentioned.

To add the code, for Access 2000:

With the database in design view, under objects: click on 'modules'

Then click on 'New'

A blank window with the words 'Option Compare Database' will open

Copy and paste the code below to just under those words.

'********Code Start


Option Explicit
Public NumberHolder As Double
Public CountHolder As Double
Public str1 As String
Public str2 As String



Public Function fCount(ThisNumber As Double, RegNo As String)

Dim LessOne As Double
Dim iCount As Integer

LessOne = ThisNumber - 1

If LessOne = NumberHolder Then
CountHolder = CountHolder + 1
str2 = RegNo
Else
CountHolder = 1
str1 = RegNo
End If

fCount = str1 & " to " & str2 & " = " & CountHolder

NumberHolder = ThisNumber

End Function

'*****Code End

Click on the Save Icon to save the module, give the module a name, something that makes sense to you.

Then close the code window by clicking on the other most right hand red square with a cross in it.

Let me know how you go.  Then we will move on to the next bit.

---------- FOLLOW-UP ----------

QUESTION: As per your guidance i copied and pasted the module and saved it. Even then when i try to run the query i am getting the message - Wrong Number of arguments used with function in query expression 'fCount([NumberPart])'.

Answer
Hi Prasad,

You may need to send me the database if that is possible.

If you go to the 'Contact Me'  page of my website:

http://www.simply-access.com/CustomDatabase.html

and fill in your email address, and that you are from all experts, I will use your email address to share a public folder to you, where you can upload the database.

Regards

Julie

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.