Query for number within a range saved as a string.

[Richard Horne]

Hey guys, hoping someone can help.

I have a field (string/varchar) in a table, called carton_number that stores carton numbers in various ways.

1) They could be single cartons so 2 or 10 or 25
2) They can be comma separated to indicate multiple cartons so 1,2,3,4,5 or 10,20,30
3) They can be indicated by a range so 1-5 or 1-100 or 25-30

I'm trying to build a single query that will allow me to filter records using all 3 of the above methods.

1) and 2) can be queried using the LIKE operator with wildcards, e.g. '%STRING%' but if given the range 1-5 and you queried LIKE '%2%' then 1-5 would not show in the results because there's no 2 in the string.

Is there a way anyone can think of to cover all three options?

I considered exploding the ranges out into an invisible text box so 1-5 would become 1,2,3,4,5 but of course these exploded strings can't be queried as part of the form's recordsource, but also 1-150 would produce too big a string, so I'm at a bit of a loss.

 

[Josef P.]

Errors in the data model eventually become noticeable. Now the time has come.

Is there also something like this: 1,2,5,10-30,35,40-100
How do you want to filter? - Only for 1 number?

It is an Access table?
.. Then I would fill a 1:n table with the individual numbers. (Probably most efficiently using VBA, if 2) and 3) are mixed.)
If in the client the input should remain in the text field, you could also normalize its content with a data macro.

/edit:
Example file see attachment.
TestTable1 .. use VBA
TestTable2 .. use data macros only (usable in the backend)

 

[ebs17]

A way out of the unsuitable data structure could be to create an additional table with ranges:
1,2,5,10-30,35,40-100 ... becomes

Cartoon_IDref Start  End
23                1    1
23                2    2
23                5    5
23               10   30
23               35   35
23               40  100

Now you could filter as expected:

SELECT Cartoon_IDref FROM TableX
WHERE 27 BETWEEN Start AND End

Now you would have to decide whether you want to create such a normalization step once and then use the table that way, or whether you want to create and use such a table each time with each filter operation on the original text field.

 

[Pat Hartman]

Seems like a penny wise pound foolish way to define the carton originally. Are all cartons of the set acted on as if they were one? Because if a single carton of the set can be sold or moved or consumed, then you really need to store each carton as a single entity. If they are always handled as a set, then all need the same identification number and you need a count to say how many are in the set.

 

[ebs17]

Hi Josef, your solution is really awesome, I have not seen anything like it.

A function in the WHERE part fills a table that is already loaded and linked by the FROM part. That is bold.

I already realize that criteria in the FROM (from JOIN) and WHERE are first of equal importance and thus elements from the WHERE part can be executed before JOIN operations. But it is amazing to get the SQL Optimizer to execute a self-defined VBA function first in practice before a JOIN between indexed key fields.
How do you get it to do that? For me, this is great cinema. It opens the door to a new world.

 

[Josef P.]

@Eberhard:
The 'trick' is the left join, with which one 'persuades' the DBEngine to apply the filter first to the 1st part, because with it the amount of data for the join becomes smaller. Since the engine is basically 'data lazy' (wants to process as little data as possible), this usually works .

For the use case in this thread, I would prefer the variant with trigger (data macro).
BTW: Creating the data macros feels strange to me since I usually use T-SQL.

 

[ebs17]

Thank you for the clarifying note.

this usually works

All right: case-by-case review. No one knows what schedule the SQL optimizer will come up with.

Surely we agree that the generated normalized structure should better be general content of the table than to be generated live by query.
Creating such strings as shown above for viewing from the normalized structure is much easier and safer than the reverse way.

For a one-time transformation, a "usual" VBA-SQL-combined solution would not be the problem.

 

[Josef P.]

All right: case-by-case review. No one knows what schedule the SQL optimizer will come up with.

Exactly. I was pretty sure that the execution plan will fit in this case, but I would not rely on it.
I don't use an Access backend productively, so I don't have much experience with it anymore. But it's also fun to play with Jet a little bit.

Surely we agree that the generated normalized structure should better be general content of the table than to be generated live by query.
Creating such strings as shown above for viewing from the normalized structure is much easier and safer than the reverse way.

Absolutely!

The trigger variant would be for the transition period, as long as the frontend is not yet adapted to 1:n data acquisition.