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="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Table 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]ID[/TD]
[/TR]
[TR]
[TD]ID101[/TD]
[/TR]
[TR]
[TD]ID102[/TD]
[/TR]
[TR]
[TD]ID103[/TD]
[/TR]
[TR]
[TD]ID104[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Table2[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]ID[/TD]
[TD="class: xl65"]Item[/TD]
[/TR]
[TR]
[TD]ID101[/TD]
[TD]A01[/TD]
[/TR]
[TR]
[TD]ID101[/TD]
[TD]A01[/TD]
[/TR]
[TR]
[TD]ID101[/TD]
[TD]B01[/TD]
[/TR]
[TR]
[TD]ID101[/TD]
[TD]B02[/TD]
[/TR]
[TR]
[TD]ID102[/TD]
[TD]B02[/TD]
[/TR]
[TR]
[TD]ID102[/TD]
[TD]B02[/TD]
[/TR]
[TR]
[TD]ID103[/TD]
[TD]D01[/TD]
[/TR]
[TR]
[TD]ID103[/TD]
[TD]E01[/TD]
[/TR]
[TR]
[TD]ID103[/TD]
[TD]E02[/TD]
[/TR]
[TR]
[TD]ID104[/TD]
[TD]C01[/TD]
[/TR]
[TR]
[TD]ID104[/TD]
[TD]B04[/TD]
[/TR]
[TR]
[TD]ID104[/TD]
[TD]C03[/TD]
[/TR]
[TR]
[TD]ID105[/TD]
[TD]A01[/TD]
[/TR]
[TR]
[TD]ID105[/TD]
[TD]A02[/TD]
[/TR]
[TR]
[TD]ID105[/TD]
[TD]A03[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.