Joining Tables Based on a STRING

[f5snopro]

I have a table with server ID's...I want to join it to a table containing applications. This application table has a string field with servers. How can I say "Give me the server information from Table #1 and the application information from Table #2 where the server ID is listed in the string field?

 

[mikey69]

You nered to define a relationship between the two tables, using the text field containing the server info as the link. You can then construct a select query. Make sure you get your join the right way round when defining the relationship.

Access makes all things possible. It even makes them intelligible

 

[f5snopro]

Well then...I probably have my syntax wrong.

SELECT IWA.*, Servers.*
FROM Servers LEFT JOIN IWA ON Servers.Server LIKE "*IWA.[Production Servers]*";

 

[OhioSteve]

I think what mikey69 missed is that one of the fields involved in this relationship contains more than just the relevant information. It would be like if you had a field called "cityState" with values like "SeattleWA" and "DetroitMI" and you wanted to relate that field to a field in another table, and that other field had values like "WA" and "MI".

The short answer is that you may not be able to fix the problem, because the problem field may be too messy. However, you may be okay if the data in that field is somewhat predictible. Is the part that you want to extract always at the end or start of the string? Is it always the same length? If so, you may be able to use a function like mid(), left(), or right() to get the data.

You will need to do two queries. The first one will just involve the table with the messed-up field. You will include all of that table's fields, except the messed up one. That field will be something like "var1:mid(5,2)". Like I said, you may also need left() or right().

Now, the second query will do what you really want to do. It will involve the query that you just made, and the table that is okay.

This is why you should always put data into meaningful units. Otherwise, it is hard to use the data.