Help Please

[DevilDog1978]

I am attempting to compare two tables (Table1) and (Table2).
Table 1 contains the field (Assets).
Table 2 contains the fields (Assets) and (Procedures).
Each Procedure in Table 2 contains multiple Assets. I want to identify the
Procedures that contain ONLY the Assets listed in Table1.

Any help would be appreciated.

 

[Jerry Whittle]

In the query design view, bring down both tables. Place any fields that you
want to return in the grid from Table2. Next link the two Assets fields
together by dragging and droping one on top of the other if Access did do
this automatically. If there are any other connecting lines between the
tables, remove them.

Wait a minute! Are you talking about a table structure in Table2 like:

Asset1 Asset2 Asset3 etc? If so that can be a problem.

 

[DevilDog1978]

Table1 Table2
Asset Procedure Asset
456 17-20bb 456
745 17-20bb 99999
289 17-50aa 959
5748 16-29gt 5541
964hb 14-78stg 745
65432d 894-sd 0251
6548d 87-tv 4
8446
468
4
In the above example, the query I want would only return only Procedure
87-tv because is is the only Procedure composed of only Assets in Table1.
Procedure 17-20bb would not be returned even though Asset 456 is listed in
Table1 Asset 99999 is not. Does this make more sense?

 

[John Spencer MVP]

If you table is fairly small then this query should work for you.
SELECT *
FROM Table2
WHERE Procedures Not IN (
SELECT Procedures
FROM Table2 LEFT JOIN Table1
On Table2.Asset = table1.Asset
WHERE Table1.Asset is Null)

If that is too slow, try

SELECT Table2.*
FROM Table1 LEFT JOIN
(SELECT Procedures, Table2.Assets
FROM Table2 LEFT JOIN Table1
On Table2.Asset = table1.Asset
WHERE Table1.Asset is Null) as x
ON Table1.Procedures= X.Procedures
WHERE X.Procedures Is Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Given your example and explaination, wouldn't you also want to see 745 /
14-78stg? It shows only one Procedure and is listed in the Asset table as
well.

Try this. You need to put in the proper table names for T1Assets and
T@Procedure.

SELECT T1Assets.Asset, T2Procedure.Procedure
FROM T1Assets, T2Procedure
WHERE T1Assets.Asset = T2Procedure.Asset
AND T1Assets.Asset IN
( SELECT DISTINCT T2Procedure.Asset
FROM T2Procedure
WHERE T2Procedure.Procedure
NOT IN(Select T2Procedure.Procedure
FROM T2Procedure
WHERE not EXISTS (SELECT T1Assets.Asset
FROM T1Assets
WHERE T1Assets.Asset = T2Procedure.Asset))) ;

 

[Pieter Wijnen]

why use IN at all (since it doesn't work well in ACC, and using a distinct
in the subquery does not help in any way), even worse is using NOT IN (that
is *always* the slower alternative regardless of backend), when you can use
exists/not exists all the way?
This meant as constructive critisism, with no offense intended

Pieter
(In my disguise as an Oracle DBA)

 

[Jerry Whittle]

Hi Pieter,

I see your point and appreciate constructive critisism. I got something to
work, but maybe not all that well. Maybe I should have done an Explain Plan,
whoops, I mean Show Plan on it.

(I'm letting my Oracle DBA day job show!)