I need to use two criteria's before linking two tables in a third table

[SaraM]

I've two tables, the first one is tblObject, which contains around 5000~ buildings spread out over Paris and I've tblFiles, which contains pictures and reports about these buildings, we are speaking about roughly 143,000 files.

tblObject and tblFiles need to be linked together and inserted into in a third table, tblObjectFile, if there is a match. So for example, the Eifel Tower has the same Objectnumber "NB0001", and if there is a tblFile with "NB0001" or "0001" in it, it will automatically link them.

The problem is that now it will link automatically any file to the object as long as he finds the Objectnumber, and this causes mistakes as sometimes they don't belong together. Here's an example of what I mean:

This is correct, tblObject has found an exact match with the tblFile in \Rapporten\1981\1981_0001.pdf

And this is wrong, as "0001" is just part of the name name.




How can I make sure this does not happen while linking tblObject with tblFile?


A first criteria would be that for files in certain maps, like \\rapporten, it could use the standard code as the design is just year_month_ObjectNumber.pdf (see the first picture)
While in other directories, like \\Horst, it would have to find an exact match before linking to prevent bad links (see the last picture)

[CJ_London]

not following your requirement. If your first table contains

PS0001
NB0001

and you search for 0001 (presumably using criteria Like *0001*) of a table that contains a record with a value that contains 0001, then it will be returned to both records in the first table.

provide some clearer examples and define the rule required more clearly. For example if it is the case the search should only be searching the last say 10 characters of the target string, use the right function to only search that bit. Or perhaps it is the bit after the last underscore?

[isladogs]

You have 3 choices
1. Rename all files so the naming convention is consistent e.g. 0001 followed by a .
You can get batch rename utilities for free
2. Devise a pattern that works for all files
3. Devise a pattern that works for each file type then use Select Case code based on file type
Do whichever is easiest though perhaps 1 will be best in the long run

[SaraM]

not following your requirement. If your first table contains

PS0001
NB0001

and you search for 0001 (presumably using criteria Like *0001*) of a table that contains a record with a value that contains 0001, then it will be returned to both records in the first table.

provide some clearer examples and define the rule required more clearly. For example if it is the case the search should only be searching the last say 10 characters of the target string, use the right function to only search that bit. Or perhaps it is the bit after the last underscore?

I edited my post, all the objects start with NB followed by the objectnumber, maybe this clarifies it a bit

This is a screenshot of tblObject

As you can see, they all have the same structure
-------------------------------------------------------------
This is an example of tblFile

As you can see, there is also consistency in some directories, where the 4 digits are behind the year. This is ok, but you have some directories where there is no consistency and the numbers are part of the naming, and where I need to use an exact match, like in the following example:

The first record, 0400801 will be linked to tblObject NB0801 and that is not the point of it, so for files like in \schots, I need to have an exact match to correctly link it

[SaraM]

You have 3 choices
1. Rename all files so the naming convention is consistent e.g. 0001 followed by a .
You can get batch rename utilities for free
2. Devise a pattern that works for all files
3. Devise a pattern that works for each file type then use Select Case code based on file type
Do whichever is easiest though perhaps 1 will be best in the long run

I cannot rename them, all the files are on the company server and I only have read permission...

[CJ_London]

This is a screenshot

none of your attachments are valid and cannot be opened

Providing a single example for string searches ie often insufficient (as in this case).

To make the search more focused you need to be able to apply some rules - for example as I suggested, always in the last 10 chars, or after the last _. or it may be that the preceding value should not be a zero. It could be anything- what if it matches something in the bit you have blacked out?

From your example the two suggestions I made would work, but no idea if that is sufficient for your needs or whether you know how to apply them

Can't help further without more information except by guessing what it is you require

[SaraM]

none of your attachments are valid and cannot be opened

Providing a single example for string searches ie often insufficient (as in this case).

To make the search more focused you need to be able to apply some rules - for example as I suggested, always in the last 10 chars, or after the last _. or it may be that the preceding value should not be a zero. It could be anything- what if it matches something in the bit you have blacked out?

From your example the two suggestions I made would work, but no idea if that is sufficient for your needs or whether you know how to apply them

Can't help further without more information except by guessing what it is you require

I updated the pictures, maybe you can see them now? And I've no idea how to apply them, is it with a "If Instr" statement?

[CJ_London]

OK, but you still have not made clear what constitutes a correct 'match'. All you are providing is what it does not match

[SaraM]

OK, but you still have not made clear what constitutes a correct 'match'. All you are providing is what it does not match

A correct match is when the four digits are the same, so when the Object is lets say "NB0001", I've put in my query that for tblFile, the criteria is Like *0001*
It matches a lot of them correct for sure, but it also accidentally matches sometimes, where those four digits are part of the name in tblFile, for example 0480001 would be matched with object NB0001

[CJ_London]

sorry, you are still not answering my questions.

can a valid match be in any part of the target string? are there any identifying characters around the string such as preceded by an underscore or occurs as part of the file name

If you can't answer these questions, I can't help you

[SaraM]

sorry, you are still not answering my questions.

can a valid match be in any part of the target string? are there any identifying characters around the string such as preceded by an underscore or occurs as part of the file name

If you can't answer these questions, I can't help you

Yes, it can be part of any part of the target string and yes, most are preceded by an underscore

[orange]

Sara,

It seems you are looking for an algorithm/method (singular) to do the matching. But it is your "most" (read that to include -many, very often, almost all....) that suggests there is no single method for this.

If there are other rules to match specific patterns, then perhaps there is a need for a small number of algorithms to do the matching. One algorithm for each identified pattern.

[CJ_London]

to capture the ones preceded by an underscore use

like "*_" & mid([objectnummer],3) & "*"

agree with Orange, but you aren't providing any data for us to help you with that

[SaraM]

Sara,

It seems you are looking for an algorithm/method (singular) to do the matching. But it is your "most" (read that to include -many, very often, almost all....) that suggests there is no single method for this.

If there are other rules to match specific patterns, then perhaps there is a need for a small number of algorithms to do the matching. One algorithm for each identified pattern.

to capture the ones preceded by an underscore use

like "*_" & mid([objectnummer],3) & "*"

agree with Orange, but you aren't providing any data for us to help you with that

I sent you guys a message with my data

[orange]

Please be explicit--what does this mean?

This is the file itself, the linking button is located in "cmdKoppelPaul", tblbestanden = tblfile

I also note that there are no relationships in your database.
This is the caption for cmdKoppelPaul Koppel alle bestanden

I'm looking/reviewing at the click event

Private Sub cmdKoppelPaul_Click()
Dim vDB As DAO.Database
Dim vRST As DAO.Recordset
Dim strSQLObject As String
Dim strSQLBestand As String
Set vDB = CurrentDb
strSQLObject = "SELECT * from tblobjecten order by objectnummer"
Set vRST = vDB.OpenRecordset(strSQLObject, dbOpenDynaset, dbSeeChanges)
vRST.MoveFirst
Do While Not vRST.EOF
    strSQLBestand = "INSERT INTO tblobjectbestand (fldobjectid,fldbestandid)VALUES (1068, 688999)"
    strSQLBestand = strSQLBestand & " SELECT " & vRST!objectid & ", fldbestandid FROM tblbestand where instr(fldbestand," & Chr(34) & vRST!Objectnummer & Chr(34) & ") >0"
    'DoCmd.SetWarnings False
    'DoCmd.RunSQL strSQLBestand
    Debug.Print strSQLBestand
    'CurrentDb.Execute strSQLBestand, dbFailOnError
    vRST.MoveNext
Loop
End Sub

I tried using the debug.print output as a query to see if it would be accepted by Access.

INSERT INTO tblobjectbestand (fldobjectid,fldbestandid)VALUES (1068, 688999) SELECT 1068, fldbestandid FROM tblbestand where instr(fldbestand,"NB0001") >0

It fails saying no semicolon at the end, but I don't believe that's an issue(yet).

You would not need a VALUES and Select in the same query sql???
I am confused on what exactly is your intent with this code??????????????