Access query with one to many relationship

[CliveButler]

Hi Everyone,

I'm trying to workout the following. I have 2 tables see below.

There is a one to many relationship between table 1 ID to table 2 ID

What I want is to create a query to select ID number that does not contain any Item starting with B0 or C0

The answer this query should give me is ID103 and ID105

Can anyone help? I spend most of the day on this but not really getting anywhere.

Thanks

Table 1

ID

ID101

ID102

ID103

ID104

<tbody>
</tbody>

Table2
ID	Item
ID101	A01
ID101	A01
ID101	B01
ID101	B02
ID102	B02
ID102	B02
ID103	D01
ID103	E01
ID103	E02
ID104	C01
ID104	B04
ID104	C03
ID105	A01
ID105	A02
ID105	A03

<tbody>
</tbody>

 

[mathematician]

Hi everyone,
Thanks for all the suggestions. Here is what ended up using that seems to be working ok

SELECT DISTINCT T1.ID
FROM TABLE1 AS T1 
LEFT JOIN (SELECT ID, ITEM FROM TABLE1 WHERE ITEM LIKE '[B][COLOR=#ff0000][B-C]*[/COLOR]'[/B])  AS T2 ON T1.ID = T2.ID
WHERE 
    (((T2.ID) Is Null));

This will exclude all items beginning with B or C. If you mean that its alright but as per your writing you wanted to exclude those IDs with items beginning with B0 or C0 which are two different things.
Try editing your query by inserting zero before the star (see red bolded above and below) OR you may consider trying this query below

SELECT DISTINCT T1.ID
FROM T1 INNER JOIN T2 ON T1.ID = T2.ID
WHERE T1.ID NOT IN (SELECT DISTINCT T1.ID FROM T1 INNER JOIN T2 ON T1.ID = T2.ID WHERE T2.Item Like '[B-C]0*')

 

[mathematician]

This will exclude all items beginning with B or C. If you mean that its alright but as per your writing you wanted to exclude those IDs with items beginning with B0 or C0 which are two different things.
Try editing your query by inserting zero before the star (see red bolded above and below) OR you may consider trying this query below

SELECT DISTINCT T1.ID
FROM T1 INNER JOIN T2 ON T1.ID = T2.ID
WHERE T1.ID NOT IN (SELECT DISTINCT T1.ID FROM T1 INNER JOIN T2 ON T1.ID = T2.ID WHERE T2.Item Like '[B-C]0*')

Sorry! The first join bolded here is insignificant and can be omitted. No problem.