Searching for consequential numbers in a dataset

[Sharkman1885]

Title should say NON sequential numbers
Hey all,
I am trying to figure out how to get my query to search for numbers that are not in sequential order and are separated by a "/".

A little background: The database I am working with is for searching crocodiles that are marked with an external tag, an internal tag, and a clipping of dorsal scutes (The ridges on the dorsal (top) side of the crocodiles) that give the animal unique, searchable characteristics. It is these characteristics that are giving me issues. To do the search for the pattern I use 3 areas on the animal but for simplicity sake I will just say single (S).

The pattern is S:7/8/10/12 The query is only searching for 7/8/10/12. Unfortunately sometimes the animal doesnt cooperate and in the field you can only see some of the clippings eg S:7/10. This is the root of my issue. Is there a way to run a query for 7/10 that will also return 7/8/10/12? I use a form for the query and my code is in SQL. below is the current code that works perfect as long as only one number is entered or the numbers are sequential

((Crocodile_ID.) Like "*" & Forms![Search Crocodiles]!S & "*" Or Forms![Search Crocodiles]!S Is Null)

any suggestions would be greatly appreciated

NOTE: wasn't sure if this should be posted in Queries or Forms

 

[CJ_London]

it sounds like your tables are not structured in a way that is fit for purpose. From your brief description, I would expect to have at least 4 fields (animal id, external tag, internal tag, dorsal scutes).

Beyond that is guesswork at the moment - how does the pattern with 5? elements equate to 3 areas?

Also, can you have a number range of random order e.g. S:8/7/12/10?

And can the range include 1,2 etc (i.e. your code searching for a single number 1 would return 1, 10, 21 etc?

Is there a way to run a query for 7/10 that will also return 7/8/10/12?

My best guess for your scenario is to try

((Crocodile_ID.[S]) Like "*" & [COLOR=red]replace([/COLOR]Forms![Search Crocodiles]!S[COLOR=red],"/","*")[/COLOR] & "*" Or Forms![Search Crocodiles]!S Is Null)

which should find S:7/8/10/12 if searching for 7/10

 

[Mark_]

To second CJ_London's response, I'd look at how you have your data stored.

7/8/10/12 does sound like 4 fields (or 4 entries in a child table) rather than what one would store. For entry/display I can see it, especially if you have those pesky touch typists entering the data...

 

[Sharkman1885]

CJ I will try that out tomorrow when I get back to the database

Mark as far as how the data is stored, as the data is an individual pattern on the crocodile it is stored in one table under the column S in this case there would be an individual with the scute clip pattern 8/7/12/10. Problem is when searching the database you dont always know the full pattern

 

[CJ_London]

Problem is when searching the database you dont always know the full pattern

that is understood. What you haven't explained is what are the permutations for a full pattern.

Your second post you have used the random order as an illustration. My solution is based on there not being a random order, but always incremental i.e. searching for 7/10 will still work for 8/7/12/10, but searching for 7/8 won't, nor would it for a pattern of 10/8/7/12.

 

[jdraw]

Try
SELECT tblCroc.*
FROM tblCroc
WHERE (((tblCroc.croctag) Like "S:7/*10*"));

ID	croctag
1	S:7/8/10/12
3	S:7/10
6	S:3/7/23/10
7	S:8//7/10

You could try setting a regular expression (regex) once all the conditions are determined.

 

[Sharkman1885]

ok I think I understand the question. So if I have the patterns
7/8/10/12
8/12
8/10/12
7/12

I want to be able to run a query where if I only know 8/12 it will return
7/8/10/12
8/12
8/10/12

but will not return 7/12. the numbers in the table will always be in order, 8/7/12/10 should have read 7/8/10/12. I am sorry if Im talking in circles I am really new to Access.
based on your description CJ your code should do the job. Unfortunately I cannot try it out until tomorrow

 

[Sharkman1885]

jdraw That could work but I am using a form for the query and I think my typo earlier screwed people up. That being where the pattern is always incremental instead of completely random. I may only know random pieces of the pattern and thus have to search by that (eg 8/12)but the table has the whole pattern entered incrementally (eg 7/8/10/12/16)

 

[Mark_]

For your data, what is the lowest value in the first position?

Would you ever have a case where you would be searching for 1/12?

 

[Sharkman1885]

Mark, Yes I could potentially have a 1/12 I could also have a 1/4/8/10/12. A few of them were entered with 0s as well for some reason. So 0/5/7 is also a possibility. I think Ill have to remove the zeroes from the table later

I inherited this table from my predecessor

 

[jdraw]

Because of the variation in patterns and not knowing clearly what you are looking for, I don't think a one size fits all is the answer. You may have to identify a group of patterns, then set up criteria for each. Then assemble the final set.

Do you have some sample(real) data o show the variation?

 

[Sharkman1885]

jdraw, As I am currently not at the office (8 pm est here) I cannot access my data but the examples I gave were real examples. I can get more examples tomorrow however. The variation can go from a null all the way up to 5 or 6 numbers. No one number will be repeated in a sequence and the sequence will always be in ascending order.

I can further explain why they will always be in ascending order and why no number will be repeated if need be.

 

[Mark_]

ok I think I understand the question. So if I have the patterns
7/8/10/12
8/12
8/10/12
7/12

I want to be able to run a query where if I only know 8/12 it will return
7/8/10/12
8/12
8/10/12

but will not return 7/12. the numbers in the table will always be in order, 8/7/12/10 should have read 7/8/10/12. I am sorry if Im talking in circles I am really new to Access.
based on your description CJ your code should do the job. Unfortunately I cannot try it out until tomorrow

Just to make sure, 8/12 would return 8/10/12. Would it also need to return 8/10? or 10/12?

 

[Sharkman1885]

Mark, It would not have to return 8/10 or 10/12. so if the patterns Im running the query on are:
7/8/10/12
8/12
8/10/12
7/12
8/10
10/12

then running the query using 8/12 will still just return

7/8/10/12
8/12
8/10/12

However if I only know 8 then the query should return everything with an 8 in it. So it would return
7/8/10/12
8/12
8/10/12
8/10

 

[CJ_London]

Mark, Yes I could potentially have a 1/12 I could also have a 1/4/8/10/12. A few of them were entered with 0s as well for some reason. So 0/5/7 is also a possibility. I think Ill have to remove the zeroes from the table later

since you can also be searching for single digits, to avoid searching for say 1/12 and finding 7/8/10/12 modify your code to

(([COLOR=red]"/" &[/COLOR] [COLOR=red]mid([/COLOR]Crocodile_ID.[S][COLOR=red],3) & "/"[/COLOR]) Like "*[COLOR=red]/[/COLOR]" & replace(Forms![Search Crocodiles]!S,"/","[COLOR=red]/[/COLOR]*") & "[COLOR=red]/[/COLOR]*" Or Forms![Search Crocodiles]!S Is Null)

this

"/" & mid(Crocodile_ID.,3) & "/"

changes S:7/8/10/12 to /7/8/10/12/

and this

"*/" & replace(Forms![Search Crocodiles]!S,"/","/*") & "/*"

changes 7/10 to */7/*/10/*

Perhaps you can start to see that your data is not set up for searching effectively. This will be a slow search for a large database since indexing cannot be used, but should be OK for a few hundred records.

 

[Sharkman1885]

Ok I just got into the office. Here is an example of data from the table:

4/10/12
7/8/10/12
5/9
4/11
11
3/10/12
7/10/12
2/10/12
9
9/13

I am unsure of another way to store this data as each number has to be separate as in 2/10/12 cannot read 21012. The data will then be combined with two other columns to make a full pattern. I have figured out how to combine the columns just fine. Here is the final pattern output (S3/10/12 DL6 DR3/5/7) As you can see each number is distinct.

CJ just tried out your first code that you sent me last night and that seems to have done the trick! Thank you soooo much!!! All of you guys rock. Any other suggestions would be greatly appreciated

EDIT: for some reason I now get errors with null values. I dont understand why though as the Null statement at the end of the line of code is unchanged
((Crocodile_ID.) Like "*" & replace(Forms![Search Crocodiles]!S,"/","*") & "*" Or Forms![Search Crocodiles]!S Is Null)

unsure how to embed this as code in the forum

 

[CJ_London]

for some reason I now get errors with null values

the criteria will still try to complete both sides of the OR statement

assuming you have a null 'S' control on your form then you need to use the nz function

.....nz(Forms![Search Crocodiles]!S,"")......

but suggest adopt the method in my last post because of single digits

I am unsure of another way to store this data as each number has to be separate as in 2/10/12

You store in a separate table vertically. something like

ID AnimalID TagValue
1...1............7
2...1............10
3...1............12
4...2............8
5...2............12

 

[Sharkman1885]

CJ I have also looked at your second code that you gave me and tried it without the mid function but it doesnt seem to work. Oddly enough your first code works on everything except 1/x (and nulls). Speaking of nulls the error I get when the field is left blank is "the expression typed is incorrect or too complex"

 

[Sharkman1885]

ID AnimalID TagValue
1...1............7
2...1............10
3...1............12
4...2............8
5...2............12

The problem with doing this is that the data for the animal is all in one line GPS location other identification information as well as location info and notes.As well as other instances where the animal was recorded which would be a separate line with different information such as GPS ect. Unfortunately I have found that there is no easy way to link multiple tables because the scute patterns are between three columns S, DL and DR.

 

[Minty]

Just to jump in - It might be helpful to upload a sample of your original core data as you get it - remove anything sensitive, but leave enough to we can see all eventualities.

You may be missing a trick in breaking it down and storing it locally in a more sensible format.