How to Filter Numbers

I have 9 digits in form of "111020402". Then I have another 9 digits
in form of "111020403". I have 100,000 records of these two 9-digit
numbers.


I want to filter this as follows:

District: 111
Charge: 02
Circle: 04
Book Number: 02

For example, if somone asks me to filter 111020402 and 111020403, then
I need to filter in terms of Book Number and tell the person how many
records are there in each 9-digit numbers.

I hope you understand my problem. I am new in advance filtering.

Thanks in advance.

Re: How to Filter Numbers

"Abedin" wrote in message
news:a32ac247-999e-4284-961a-aa061536af19@w56g2000hsf.google groups.com...
>I have 9 digits in form of "111020402". Then I have another 9 digits
> in form of "111020403". I have 100,000 records of these two 9-digit
> numbers.
>
>
> I want to filter this as follows:
>
> District: 111
> Charge: 02
> Circle: 04
> Book Number: 02
>
> For example, if somone asks me to filter 111020402 and 111020403, then
> I need to filter in terms of Book Number and tell the person how many
> records are there in each 9-digit numbers.
>
> I hope you understand my problem. I am new in advance filtering.
>
> Thanks in advance.

You need to normalise your data and store each element of data in its own
field, thus you can query each field as necessary. Your fields:

District
Charge
Circle
BookNumber

can always be concatonated in a query if necessay to form the "big" number:

BigNumber: [District] & [Charge] & [Circle] & [BookNumber]

Keith.
www.keithwilby.com

Re: How to Filter Numbers

On Dec 5, 7:04 pm, "Keith Wilby" wrote:
> "Abedin" wrote in message
>
> news:a32ac247-999e-4284-961a-aa061536af19@w56g2000hsf.google groups.com...
>
>
>
>
>
> >I have 9 digits in form of "111020402". Then I have another 9 digits
> > in form of "111020403". I have 100,000 records of these two 9-digit
> > numbers.
>
> > I want to filter this as follows:
>
> > District: 111
> > Charge: 02
> > Circle: 04
> > Book Number: 02
>
> > For example, if somone asks me to filter 111020402 and 111020403, then
> > I need to filter in terms of Book Number and tell the person how many
> > records are there in each 9-digit numbers.
>
> > I hope you understand my problem. I am new in advance filtering.
>
> > Thanks in advance.
>
> You need to normalise your data and store each element of data in its own
> field, thus you can query each field as necessary. Your fields:
>
> District
> Charge
> Circle
> BookNumber
>
> can always be concatonated in a query if necessay to form the "big" number:
>
> BigNumber: [District] & [Charge] & [Circle] & [BookNumber]
>
> Keith.www.keithwilby.com- Hide quoted text -
>
> - Show quoted text -

Thanks alot for your help.

Can I break Big Number into small numbers and then later concatenate?
Because I would need to filter it on each individual small numbers.
For example, I might need to filter on District, Charge, Circle, or
Book Number.

Thanks Keith.

Re: How to Filter Numbers

On Wed, 5 Dec 2007 06:09:16 -0800 (PST), Abedin
wrote:

There is no need for that. If you follow Keith's advice, you have the
"small numbers" sitting in 4 fields in your table. You only
concatenate for display purposes, not for calculation purposes.

-Tom.


>On Dec 5, 7:04 pm, "Keith Wilby" wrote:
>> "Abedin" wrote in message
>>
>> news:a32ac247-999e-4284-961a-aa061536af19@w56g2000hsf.google groups.com...
>>
>>
>>
>>
>>
>> >I have 9 digits in form of "111020402". Then I have another 9 digits
>> > in form of "111020403". I have 100,000 records of these two 9-digit
>> > numbers.
>>
>> > I want to filter this as follows:
>>
>> > District: 111
>> > Charge: 02
>> > Circle: 04
>> > Book Number: 02
>>
>> > For example, if somone asks me to filter 111020402 and 111020403, then
>> > I need to filter in terms of Book Number and tell the person how many
>> > records are there in each 9-digit numbers.
>>
>> > I hope you understand my problem. I am new in advance filtering.
>>
>> > Thanks in advance.
>>
>> You need to normalise your data and store each element of data in its own
>> field, thus you can query each field as necessary. Your fields:
>>
>> District
>> Charge
>> Circle
>> BookNumber
>>
>> can always be concatonated in a query if necessay to form the "big" number:
>>
>> BigNumber: [District] & [Charge] & [Circle] & [BookNumber]
>>
>> Keith.www.keithwilby.com- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks alot for your help.
>
>Can I break Big Number into small numbers and then later concatenate?
>Because I would need to filter it on each individual small numbers.
>For example, I might need to filter on District, Charge, Circle, or
>Book Number.
>
>Thanks Keith.

Re: How to Filter Numbers

"Keith Wilby" wrote in message
news:4756ac17$1_1@glkas0286.greenlnk.net...
> "Abedin" wrote in message
> news:a32ac247-999e-4284-961a-aa061536af19@w56g2000hsf.google groups.com...
>>I have 9 digits in form of "111020402". Then I have another 9 digits
>> in form of "111020403". I have 100,000 records of these two 9-digit
>> numbers.
>>
>>
>> I want to filter this as follows:
>>
>> District: 111
>> Charge: 02
>> Circle: 04
>> Book Number: 02
>>
>> For example, if somone asks me to filter 111020402 and 111020403, then
>> I need to filter in terms of Book Number and tell the person how many
>> records are there in each 9-digit numbers.
>>
>> I hope you understand my problem. I am new in advance filtering.
>>
>> Thanks in advance.
>
> You need to normalise your data and store each element of data in its own
> field, thus you can query each field as necessary. Your fields:
>
> District
> Charge
> Circle
> BookNumber
>
> can always be concatonated in a query if necessay to form the "big"
> number:
>
> BigNumber: [District] & [Charge] & [Circle] & [BookNumber]
>
> Keith.
> www.keithwilby.com

I agree with Keith, however, couldn't you also use a query to separate the
"big" number into a normalized split?

SELECT BigNumber, _
Mid([BigNumber],1,3) AS District, _
Mid([BigNumber],4,2) AS Charge, _
Mid([BigNumber],6,2) AS Circle, _
Mid([BigNumber],8,2) AS Book _
FROM OrigTable;

You could then link this query to OrigTable to create a new query to use.
This preserves your original structure and provides the new flexibility for
easy filtering. It might not be as fast as a re-written table, but might
save you from having to re-write a lot of your application....
Fred Zuckerman

Re: How to Filter Numbers

On Dec 5, 7:14 pm, Tom van Stiphout wrote:
> On Wed, 5 Dec 2007 06:09:16 -0800 (PST), Abedin
> wrote:
>
> There is no need for that. If you follow Keith's advice, you have the
> "small numbers" sitting in 4 fields in your table. You only
> concatenate for display purposes, not for calculation purposes.
>
> -Tom.
>
>
>
> >On Dec 5, 7:04 pm, "Keith Wilby" wrote:
> >> "Abedin" wrote in message
>
> >>news:a32ac247-999e-4284-961a-aa061536af19@w56g2000hsf.goog legroups.com...
>
> >> >I have 9 digits in form of "111020402". Then I have another 9 digits
> >> > in form of "111020403". I have 100,000 records of these two 9-digit
> >> > numbers.
>
> >> > I want to filter this as follows:
>
> >> > District: 111
> >> > Charge: 02
> >> > Circle: 04
> >> > Book Number: 02
>
> >> > For example, if somone asks me to filter 111020402 and 111020403, then
> >> > I need to filter in terms of Book Number and tell the person how many
> >> > records are there in each 9-digit numbers.
>
> >> > I hope you understand my problem. I am new in advance filtering.
>
> >> > Thanks in advance.
>
> >> You need to normalise your data and store each element of data in its own
> >> field, thus you can query each field as necessary. Your fields:
>
> >> District
> >> Charge
> >> Circle
> >> BookNumber
>
> >> can always be concatonated in a query if necessay to form the "big" number:
>
> >> BigNumber: [District] & [Charge] & [Circle] & [BookNumber]
>
> >> Keith.www.keithwilby.com-Hide quoted text -
>
> >> - Show quoted text -
>
> >Thanks alot for your help.
>
> >Can I break Big Number into small numbers and then later concatenate?
> >Because I would need to filter it on each individual small numbers.
> >For example, I might need to filter on District, Charge, Circle, or
> >Book Number.
>
> >Thanks Keith.- Hide quoted text -
>
> - Show quoted text -

Got it. Thank you guys. Love you all.

Re: How to Filter Numbers

Abedin wrote:
> I have 9 digits in form of "111020402". Then I have another 9 digits
> in form of "111020403". I have 100,000 records of these two 9-digit
> numbers.
>
>
> I want to filter this as follows:
>
> District: 111
> Charge: 02
> Circle: 04
> Book Number: 02
>
> For example, if somone asks me to filter 111020402 and 111020403, then
> I need to filter in terms of Book Number and tell the person how many
> records are there in each 9-digit numbers.
>
> I hope you understand my problem. I am new in advance filtering.
>
> Thanks in advance.

If you ever needed to split the 9 digit code, (and the field name is
Code) in the query builder you can do something like
District : Left(Code,3)
Charge : Mid(Code,4,2)
Circle : Mid(Code,6,2)
Book : Mid(Code,8)
This will create columns with those 4 column names. You can then filter
on them individually if you want to.

If you have a form and you need just those with district, charge, and
circle, you could use the word Like
Select * From Table Where _
Code Like '" & Forms!Main!District & _
Forms!Main!Charge
Forms!Main!Circle.
This is basically saying select all records where Code begins with 1110204.

Now, lets say you have a continuous form with a text box named "Search"
for entering your 9 digit code. When you press Enter, the AfterUpdate
event of the field could be
Me.Filter = "Code = '" & Me.Search & "'" 'stored as string
or
Me.Filter = "Code = " & Me.Search 'stored as number
Me.FilterOn = True

There's many ways to go about this. Depends on what you are doing.

Re: How to Filter Numbers

On Dec 5, 7:25 pm, Salad wrote:
> Abedin wrote:
> > I have 9 digits in form of "111020402". Then I have another 9 digits
> > in form of "111020403". I have 100,000 records of these two 9-digit
> > numbers.
>
> > I want to filter this as follows:
>
> > District: 111
> > Charge: 02
> > Circle: 04
> > Book Number: 02
>
> > For example, if somone asks me to filter 111020402 and 111020403, then
> > I need to filter in terms of Book Number and tell the person how many
> > records are there in each 9-digit numbers.
>
> > I hope you understand my problem. I am new in advance filtering.
>
> > Thanks in advance.
>
> If you ever needed to split the 9 digit code, (and the field name is
> Code) in the query builder you can do something like
> District : Left(Code,3)
> Charge : Mid(Code,4,2)
> Circle : Mid(Code,6,2)
> Book : Mid(Code,8)
> This will create columns with those 4 column names. You can then filter
> on them individually if you want to.
>
> If you have a form and you need just those with district, charge, and
> circle, you could use the word Like
> Select * From Table Where _
> Code Like '" & Forms!Main!District & _
> Forms!Main!Charge
> Forms!Main!Circle.
> This is basically saying select all records where Code begins with 1110204.
>
> Now, lets say you have a continuous form with a text box named "Search"
> for entering your 9 digit code. When you press Enter, the AfterUpdate
> event of the field could be
> Me.Filter = "Code = '" & Me.Search & "'" 'stored as string
> or
> Me.Filter = "Code = " & Me.Search 'stored as number
> Me.FilterOn = True
>
> There's many ways to go about this. Depends on what you are doing.- Hide quoted text -
>
> - Show quoted text -

Wow, this is all useful information. I will definitely use it. Thanks
again y'all.

Re: How to Filter Numbers

On Dec 5, 6:45 pm, Abedin wrote:
> I have 9 digits in form of "111020402". Then I have another 9 digits
> in form of "111020403". I have 100,000 records of these two 9-digit
> numbers.
>
> I want to filter this as follows:
>
> District: 111
> Charge: 02
> Circle: 04
> Book Number: 02
>
> For example, if somone asks me to filter 111020402 and 111020403, then
> I need to filter in terms of Book Number and tell the person how many
> records are there in each 9-digit numbers.
>
> I hope you understand my problem. I am new in advance filtering.
>
> Thanks in advance.

Hi...

Use substring function of Sql, with starting index and the last index.



Syntx:-select SUBSTR(column_name,String index,length) As Name from
table.

For Ex..
SQL> select SUBSTR(meterserialno,1,3) As CODE, SUBSTR(meterserialno,
4,3) AS NAME from tb_locationmst;

COD NAM
--- ---
RSX 091
RSX 092
RSX 091
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 092
RSX 091


Pankaj Singh Yadav