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>

 

[FryGirl]

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

What about ID101 A01?

Maybe

SELECT DISTINCTROW [Table2].ID, Left([Item],2) AS Expr1
FROM Table2
GROUP BY [Table2].ID, Left([Item],2)
HAVING (((Left([Item],2)) Not In ("B0","C0")));

 

[CliveButler]

What about ID101 A01?

Maybe

SELECT DISTINCTROW [Table2].ID, Left([Item],2) AS Expr1
FROM Table2
GROUP BY [Table2].ID, Left([Item],2)
HAVING (((Left([Item],2)) Not In ("B0","C0")));

Thanks for the suggestion Fry Girl, but not working unfortunately. The problem is some ID contains mixed Item eg ID101 where it have both A0% and B0%

The query seems to have trouble identifying ID that have mixed Item and include in the result anyway.

so the answer I got is something like

ID101 - because it contains both A0% and B0%
ID103 - correct
ID104 - same reason as ID101
ID105 - correct


ID101 would not be in the answer as it contains B01 and B02.

 

[Kyle123]

I don't think you can do this in raw Access SQL, you could in MS-SQL but I don't think access has the required functionality without making the query unnecessarily rigid.

 

[Kyle123]

Actually, I'm being slack, you want this I think (you may need to fiddle to get the parentheses in the right place):

SELECT v1.id 
FROM   (SELECT id, 
               Count(item) itm 
        FROM   table2 
        GROUP  BY id) v1 
       LEFT JOIN (SELECT id, 
                         Count(item) itm 
                  FROM   table2 
                  WHERE  item NOT LIKE 'B0%' 
                         AND item NOT LIKE 'C0%' 
                  GROUP  BY id) v2 
              ON v2.id = v1.id 
WHERE  v1.itm = v2.itm

By way of an explanation, the first subquery counts the number of items for each id, the second counts the number of items where the value doesn't begin with 'B0' or 'C0', if the count of items in the first query is equal to the count of items excluding the items you don't want in the second query, then those ids do not contain the excluded items

 

[Micron]

how about
SELECT tbl1.ID, tbl2.Item FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID
WHERE (((tbl2.Item) Not Like "B0*" And ((tbl2.Item) Not Like "C0*")));
Worked for me.

 

[Kyle123]

That'll return id101 which is incorrect, so it's unfortunately not quite that simple

 

[xenou]

Using EXISTS:

SELECT DISTINCT t1.ID 
FROM 
	Table1 t1 
		INNER JOIN 
		Table2 t2
		ON 
		t1.ID = t2.ID
WHERE 
	NOT EXISTS (SELECT * FROM Table2 WHERE t1.ID = ID AND Item LIKE 'B0*')
	AND  NOT EXISTS (SELECT * FROM Table2 WHERE t1.ID = ID AND Item LIKE 'C0*')

I did add ID105 to Table1 because it wasn't in the table!

 

[Micron]

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

Ahh, so the requirement was to not return an ID if in any record it had a corresponding Item that started with B0 or C0.
I didn't take it that way - my fault. Love your solution, xenou! Now I just hope I can retain the concept if ever needed.

 

[CliveButler]

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-C]*')  AS T2 ON T1.ID = T2.ID
WHERE 
    (((T2.ID) Is Null));