MS Access SQL query creation

Question

I have two tables as you can see in the attached database file (I attached an old ms access file .mdb and a new version .accdb of the same file)

One table has a numerical primary key field and a reference field.

The other table has the references (ex. 1.1.1 or 1.1.2) as the primary key field.

I need to make a sql query that will fetch the corresponding references in the fields of the first table and paste the description of those references from the references table.

So for example the first entry of the "TableToBeQueried" table is:

RecordID References
       1 1.1.1; 1.1.2

So the sql query would in theory return 1.1.1 and 1.1.2 reference descriptions from the references table which are:

This is the first reference
This is the second reference

Download: http://www.mediafire.com/?6ppbztsx82mvmal,ny93tln8f097jpp

Answer 1

TableToBeQueried is not normalized, which makes your question extra challenging. It would be easier with a junction table which includes a row for each combination of RecordID and Paragraph:

RecordID Paragraph
       1 1.1.1
       1 1.1.2

However, if you're stuck with what you have now, it can be done.

SELECT
    t.RecordID,
    t.Description,
    t.References,
    r.Paragraph,
    r.Description
FROM
    TableToBeQueried AS t,
    [References] AS r
WHERE
         '; ' & [t].[References] & ';'
    Like '*; ' & [r].[Paragraph] & ';*'
ORDER BY
    t.RecordID,
    r.Paragraph;

That works for a query run inside an Access application session (SQL89 mode). However, if you are using OleDb to run this query from outside Access (SQL92 mode), change the wild card characters from * to %.

These are the first 2 rows returned by that query:

RecordID t.Description References    Paragraph r.Description
       1 Description 1 1.1.1; 1.1.2  1.1.1     This is the first reference
       1 Description 1 1.1.1; 1.1.2  1.1.2     This is the second reference