Search on part of field (1 Viewer)

[XPS35]

I have a large table (200,000+ records) of products. I can therefore not use a combo box to select a product. I have created an extra form where the user can select the first 5 positions of the product code via a combo box. Then the user selects a product whose product code starts with the selected 5 positions in the next combo box.
Building up the list of unique 5 position product code takes a bit long. The sql I am using is:
SELECT DISTINCT Left(ProductCode,5) FROM tblProduct

I thought to speed up building up the combobox with an index on the table. However, it is not possible to create an index on part of a field. Even if I define a calculated field in the table, I cannot create an index on that field. I prefer not to use a calculated field that needs to be maintained via code.

Does anyone have a (clean) solution to speed up the build-up of the combobox?

 

[Gasman]

Search here for fayt by @MajP
That should at least get you started.

 

[plog]

What does your product code look like? Does it have parts? Do the first 5 digits represetn something? For example:

12345-ABC-2022

12345 = product type
ABC = product number
2022 = year put into service

Can you give us a few examples?

 

[isladogs]

I had a similar situation where users needed to type in postcodes into a combo box. With 2.6 million postcodes that wasn't viable so I split the postcode into sections: area, district, sector and 'zone' to make it manageable, then used a series of 5 cascading combos.
If you want to see how that worked, an example app is available at

Cascading Combo Boxes

This explains how cascading combo boxes are used to filter large datasets in Access

www.isladogs.co.uk

 

[XPS35]

What does your product code look like? Does it have parts? Do the first 5 digits represetn something? For example:

12345-ABC-2022

12345 = product type
ABC = product number
2022 = year put into service

Can you give us a few examples?

The first 5 positions do not represent anything.
Examples:

ProcuctCode
BE_0010027/1
BE_0010027/100
BE_0010032
BE_0010034/1
BE_00100424/1
BE_00100424/100
BE_00100444
BE_00100644/1
BE_00100644/100
BE_00100644/50
BE_00100644/500
BE_00100654/1
BE_00100664/1
BE_00100664/100
BE_0010068/100
BE_0010076/1
BE_0010086/1
BE_0010087/1
BE_0010087/100
BE_00100883/1
BE_00100893/1
BE_0010091/1
BE_0010100/100
BE_0010100/50

BTW I did not design the database. Just trying to help someone.

 

[XPS35]

I had a similar situation where users needed to type in postcodes into a combo box. With 2.6 million postcodes that wasn't viable so I split the postcode into sections: area, district, sector and 'zone' to make it manageable, then used a series of 5 cascading combos.
If you want to see how that worked, an example app is available at

Cascading Combo Boxes

This explains how cascading combo boxes are used to filter large datasets in Access

www.isladogs.co.uk

I do not understand how to use cascading combo boxes when only one field is involved.

 

[Pat Hartman]

When you define "meaningful" unique identifiers, you do yourself no service by mushing everything into a string and you end up with issues like these. Essentially, you are preventing the database engine from making use of indexes when you use LIKE. That means each search results in a full table scan. That means that the ENTIRE table needs to be brought down from the server and read RBAR (Row by Agonizing Row).

The best solution is to break the code apart into its constituent parts. That allows you to build indexes and allows your search to actually use those indexes when searching for parts.

You can leave a mushed version with a unique index. You can then define several pieces with multi-field indexes. I would need to understand the rules behind the construction of the field to be specific. Start by breaking up the mushed field into individual fields and let them be used in the cascading combos.

 

[isladogs]

I do not understand how to use cascading combo boxes when only one field is involved.

I only had one field (postcode) and split that into sections as already stated.
I am suggesting that you do the same - even more so now I've seen part of the dataset

 

[arnelgp]

i have my own FART here (post #5):
Solved - Dropdown list | Access World Forums (access-programmers.co.uk)

 

[isladogs]

i have my own FART here (post #5):
Solved - Dropdown list | Access World Forums (access-programmers.co.uk)

I do hope that's a typo. It doesn't smell too good....

 

[Gasman]

i have my own FART here (post #5):
Solved - Dropdown list | Access World Forums (access-programmers.co.uk)

I was going to say, perhaps it means something different where arnel lives? as the Y is two keys away from the R.

That reminds me of a humourous story when I was in the Merchant Navy.
My company Bibby Lne went into a merger with two other companies, one Belgian I think to create a container ship company.
It was called DART Lines. DART being an acronym for Direct And Reliable Transport. The four letters were to be plastered all over the containers.
The Belgians thought Fast And Reliable Transport would sound better, and took some convincing to leave it as DART.

 

[XPS35]

Thanks for all the reply's. I will do some research on "FAYT". I don't have any idea what it is yet

 

[isladogs]

Find As You Type

 

[MajP]

I am not sure if the FAYT (or now better known as the Fart), solves the original issue. I thought the issue had to do with the speed of loading the rowsource. I can attest that my version of the FAYT was built for easy reuse and not efficiency. This approach is especially inefficient if using a backend database.
However on a local table of 50k it runs very fast so I am surprised that your query is that slow with 200k
SELECT DISTINCT Left(ProductCode,5) FROM tblProduct

All the FAYT versions look at the characters typed in the combobox then build a "Like *Text*" or "Like *Text" query. Any query built on a Like is not efficient since it can not use and index search. One way you can modify the FAYT is to only filter after a set amount of characters are input. So the user has to type in say 3 or more characters before it applies the filter. You can find version of that if you search.
You can still use a FAYT, but I think you want that to be part of a larger solution of splitting the data into multiple fields as suggested by @isladogs.

 

[Pat Hartman]

@Gasman I was working at a company that liked to assign acronyms and then put them on things. So our original system name was Plant Management Information System Or PMIS. Being engineers, they had these cool metal 6" rulers made that they could carry around in their pocket protectors. When "management" saw the guys whipping out the rulers and asking each other if they measured up - we were asked to turn in our rulers. They got a lot fewer back than were distributed And the system name became something else that was much less fun

 

[moke123]

I worked for a company that used software called Policy Management System. It was a very moody program.