Another matching data query question

[HiTekRedNek]

I am working with two spreadsheets that I have imported and would like to know if there is a way of finding matching data between two columns. The tricky part here (well for me anyways) is that the columns have similar data but one has a period separator with an extension value that I want to ignore. I want to exclude the period and everything afterward and simply have the query find the matching text.

Example
Column A from table 1 would display the text like this:
EXAMPLE

Column A from table 2 would display the text like this:
EXAMPLE.new

How can I put together a query that will match both entries across the 2 tables. If found I would like to insert a column in table 1 that would display a "yes" if found or "no" if not found?

​

 

[JHB]

Try the below query it find all matching values:

SELECT Table1.ColumnA
FROM Table1, Table2
WHERE Table1.ColumnA=Left([Table2].[ColumnA],InStr([Table2].[ColumnA],".")-1);

If you want to set a "Yes" in Table1, then add a field to Table1, call it "YesNo".

UPDATE Table1, table2 SET Table1.[YesNo] = "Yes"
WHERE (([table1].[ColumnA]=Left([table2].[ColumnA],InStr([table2].[ColumnA],".")-1)));

 

[HiTekRedNek]

Thanks for giving me some hints. I've been unable to get this to work but I think I'm on the right track.

Here is my SQL code with the correct names of the tables and columns:

SELECT Prod_This_Week.[System Name], Inventory.[CI Name]
FROM Prod_This_Week LEFT JOIN Inventory ON Prod_This_Week.[System Name] = Inventory.[CI Name]
WHERE Prod_This_Week.[System Name]=LEFT(Inventory.[CI Name],InStr(Inventory.[CI Name],".")-1);

A few things to point out
- From design view I figured out that I want a left join as I want all records from the Prod_This_Week table displayed and only those records in the Inventory table that come back as a match.
- An example entry that I want to match would look like this. In the Prod_This_Week table I would have a system called "LONDON", and in the Inventory table it would appear as "LONDON.DOMAIN1".

What modifications do I need to make to the code to find my match?

​

 

[JHB]

Here is my SQL code with the correct names of the tables and columns:

SELECT Prod_This_Week.[System Name], Inventory.[CI Name]
FROM Prod_This_Week LEFT JOIN Inventory ON Prod_This_Week.[System Name] = Inventory.[CI Name]
WHERE Prod_This_Week.[System Name]=LEFT(Inventory.[CI Name],InStr(Inventory.[CI Name],".")-1);

A few things to point out
- From design view I figured out that I want a left join as I want all records from the Prod_This_Week table displayed and only those records in the Inventory table that come back as a match.
- An example entry that I want to match would look like this. In the Prod_This_Week table I would have a system called "LONDON", and in the Inventory table it would appear as "LONDON.DOMAIN1".

What modifications do I need to make to the code to find my match?

The problem is here the (Left) JOIN where you'll find all the data which are equal in Prod_This_Week.[System Name] and Inventory.[CI Name]. None of them are equal because Inventory.[CI Name] has a period separator with an extension value!

Try the below query, it should give you what you are looking for:

SELECT Prod_This_Week.[System Name]
FROM Prod_This_Week LEFT JOIN Inventory ON Prod_This_Week.[System Name] =Left([Inventory].[CI Name],InStr([Inventory].[CI Name],".")-1);

 

[HiTekRedNek]

I'm getting "Invalid Procedure Call" when executing the query.

​

 

[HiTekRedNek]

A colleague of mind suggested just to normalize the data in excel before importing by using "Find and Replace" on the extension, replacing it with a blank value. A simple but effective solution, however it would still be cool to be able and figure out how to do this in Access without having to touch the Excel spreadsheet.

Thank you JHB for your time & suggestions.

​

 

[JHB]

I'm getting "Invalid Procedure Call" when executing the query.

It sounds like a broken references.

.... however it would still be cool to be able and figure out how to do this in Access without having to touch the Excel spreadsheet.

I've made a example in the attached database for you, (run the only query in it).