Querying a query - vlookup??/inner join??

[Pegrum1]

Hi all,
Ill apologise immediately if the details are a bit sketchy, I'm completely new to SQL queries in access.
The current situation:
1st table "log" contains a column "msg" this contains loads of info that is all separated by commas. I have managed to create a query that separates all of the info into separate columns - this query is named "Findmessage" the Column I need in this query for my new query is called "phonenumber"
2nd table "links" contains 2 columns "user" and "contact". "User" contains a 5 digit alphanumeric company code and a persons name in a single field. "Contact" contains a phone number (sometimes 01234 012340 / sometimes 01234012345).
What I need is a new query/table (something!) that cross references "phonenumber" from "log" with "contact" from "links", and if the number/value is there, then it outputs the results into a query/table that displays the "phonenumber"/"contact" (same value at this point) AND the values in "user".
This all needs to be done within a SQL select statement.
I'm really sorry if this is vague, but I havn't a clue where to start!
Thanks in advance for any help

 

[Mr Smin]

First, you should take the Findmessage query and turn it into a make table query. This will reduce the work the database will have to do, assuming that the data the populates the msg column in the log table is not updated too frequently and that you will be running the subsequent query more than once. I'll assume you will call it log_new (but you don't have to).

It sounds like your data is not clean enough yet. The phone numbers sometimes have spaces and sometimes don't. If it were clean, the next step would be a query like

SELECT links.contact, log_new.phonenumber
FROM links RIGHT JOIN log_new ON links.contact = log_new.phonenumber;

This gets all the phone numbers from log_new and any that match from link. But this won't work where the numbers are the same *but for* a space.

 

[Pegrum1]

Thanks for the quick response, I can easily clean the data to remove the space between phone numbers, I'm back in work tomorrow so will try out your suggestion then.
Thanks again