How to use a substring of one field as a key to records in a 2nd table 1

[andyswen]

The tricky issue is the substring varies in location in the primary field. The part I need to strip off has a variable length but uses one of two characters as a delimeter - or : in this case. I just need to strip off the leading characters up to and including the delimeter. The balance of the field is the key for the 2nd table.

I am pretty new to Access, Thanks for any help.

 

[scriverb]

This will require two queries. The first to select all of your fields to just be passed forward plus creating a new column with just this parsed substring. The second one takes in the query and links to your secondary table using the new column.

Select A.PrimaryKeyField, Trim(Mid$(A.PrimaryKeyfield, IIF(NOT IsNull(Instr(1,A.PrimaryKeyField,&quot;-&quot), Instr(1,A.PrimaryKeyField,&quot;-&quot, Instr(1,A.PrimaryKeyField,&quot;:&quot)+1)) as NewKey, A.Field2, A.Field3, . . .<finish out all of your fields here>
FROM tblYourTableName as A

Name this query qryKeyField.

New Query with this SQL:
Select *
FROM qryKeyField as A LEFT JOIN tblTable2 as B ON A.NewKey = B.PrimaryKeyField;

You are going to have to update this example SQL with your table names and field names. Also, I have not tested this so first pass may have some syntax errors. Just get back and we can figure this out.



Bob Scriver

 

[scriverb]

I have modified the first query:
Select A.PrimaryKeyField, Trim(Mid$(A.PrimaryKeyfield, Trim(Mid$(A.[PrimaryKeyField],Switch((InStr(1,A.[PrimaryKeyField],&quot;-&quot)>0,InStr(1,A.[PrimaryKeyField],&quot;-&quot+1,(InStr(1,A.[PrimaryKeyField],&quot;:&quot)>0,InStr(1,A.[PrimaryKeyField],&quot;:&quot+1,True,1))) AS NewKey, A.Field2, A.Field3, . . .<finish out all of your fields here>
FROM Table1 as A
Order By A.PrimaryKeyField;

I used a LEFT JOIN to match the new key field with Table2 not wanting to eliminate any Table1 records.

Let me know how this works for you.




Bob Scriver

 

[andyswen]

Thank you. That was a great help. I am working on the 1st query and did have to make a couple of changes to get it working for me. I'd like your feedback on why it was not working for me. The initial change was to remove the first Trim(Mid$(A.PrimaryKeyfield to get rid of a syntax error I was getting. I tried to balance the parens, etc but that did not work so I dropped it and was able to run and save the query. It now works to an intermediate set.

The second problem I am having is that the query would not link correctly to the table and prompted for each field input ie:input dialog box with table.field. I have replaced all table names and field names and double checked for typo's. After playing with a duplicate query I was able to get it to work when I dropped the alias AS A. Not sure why and I wanted to know your thoughts.

Thanks

 

[scriverb]

First of all the Mid$ in the first query must be kept in there. That new field is the key to be able to link to your table in the second query. Why don't you send me a small database with the queries and the two tables only. You can remove all the data if there is a security issue.

I will fix it for you and send it back. I will then repost the final result in this thread for others to see how we did this. See my email address in my profile.

Bob Scriver

 

[scriverb]

I see what the problem is. In the cut and paste operation to fix the previous query I left a little too much in the code. Here is the correct version.
Select A.PrimaryKeyField, Trim(Mid$(A.[PrimaryKeyField],Switch((InStr(1,A.[PrimaryKeyField],&quot;-&quot)>0,InStr(1,A.[PrimaryKeyField],&quot;-&quot+1,(InStr(1,A.[PrimaryKeyField],&quot;:&quot)>0,InStr(1,A.[PrimaryKeyField],&quot;:&quot+1,True,1))) AS NewKey, A.Field2, A.Field3, . . .<finish out all of your fields here>
FROM Table1 as A
Order By A.PrimaryKeyField;

You can remove the Alias &quot;as A&quot; and use the table name through the query if you like. This was just done to consolidate the code and make it a little easier to read.

I am glad that this is running correctly for you now. The nice thing about queries is if you don't have a field with the desired data in it you can create a Select query to create a column and through expressions create the data needed. That is what I did here for the data necessary to connect to the secondary tables Primary Key field.

Good luck.


Bob Scriver

 

[andyswen]

Thanks again for the help. It is working like a champ.