query two tables and returns only matches that have same values

[stabilo]

I'm using Access 2003.
I have two resutling tables (importing data from other systems) :

Table1
-------
Field1 : J1
Field2 : S1
Field1 : J1
Field2 : S2 so I have the following for Table1 :

J1 S1
J1 S2
J1 S5
J2 S2
J2 S3
J2 S5
......

Table 2
---------
U1 J1 S1
U1 J1 S2
U2 J1 S1
U2 J1 S2
U2 J1 S5
....

I have to query the two tables that should return U2 only, because it is
the only that has S1, S2, S5 for J1. U1 is not returned because it has only
S1 and S2 for J1.

Does it make sens ? do I have towrite some VBA ? any help would be welcome.

 

[Amy Blankenship]

I would think that you could get that by opening the query builder and
showing table1 and table2. If you don't have a relationship set up already,
drag the column that contains J1 in table1 to the column containing J1 in
table2, then do the same for the columns containing the S's. Now drag the
column that contains the U's down to the query grid.

Run the query, and you should get U2.

HTH;

Amy

 

[stabilo]

Thanks,

I have tried this butI gert all U's, not only U2. Any thing I have done
wrong ?

 

[John Spencer]

Don't know if this will work or not, but you could try one of the following.
SELECT Table2.*
FROM Table2
WHERE Table2.Field1 In
(SELECT Table2.Field1
FROM Table2 LEFT JOIN
(SELECT Table2.Field1, Table2.Field2, Table2.Field3
FROM Table2 INNER JOIN Table1
ON Table2.Field2 = Table1.Field1 AND
Table2.Field3 = Table1.Field2) as Matches
ON Table2.Field2 = Matches.Field1 AND
Table2.Field3=Matches.Field2
WHERE Matches.Field1 IS NULL)

Two query version
Save this query as NoMatch
SELECT Table2.Field1, Table2.Field2, Table2.Field3
FROM Table2 LEFT JOIN Table1
ON Table2.Field2 = Table1.Field1 AND
Table2.Field3 = Table1.Field2
WHERE Table1.Field1 IS NULL


SELECT Table2.Field1
FROM Table2 INNER JOIN NoMatch
ON Table2.Field2 = NoMatch.Field1 AND
Table2.Field3=NoMatch.Field2

 

[stabilo]

Thanks for your help. it is no working

The version with one query returns the same contains as for Table2 et the
version with two queries return nothing. Do you think it is something that
could work ?

 

[Amy Blankenship]

OK, I was misunderstanding your question.

I think you're going to have to build 3 queries. Follow the directions for
the table below, the click the "Totals" button in the toolbar (should be
right next to a drop down containing the word "All"). Now, click the "Group
By" under the S column and change it to Count. Click the "Group By" in the
J column and change it to "Where" and below the empty check box, type "J1".

Now, close and save.

Create a new query in design view, and bring in the query you just made as a
table. Double-click CountOfS to add it to the query. Click Totals again,
and change "Group By" to Max. Now, in front of CountOfS, type MaxS:, so it
looks like this "MaxS: CountOfS" (without the quotes).

Now, start a third query. Bring in the second and third queries you made.
Add U and CountofS to the query window, then below CountOfS type in the
Criteria type qryTmp3.MaxS.

And that should do it. There's probably a way to do it in fewer queries,
but hey I have paying work I was supposed to be doing ;-)

HTH;

Amy

 

[stabilo]

thanks, this is just perfect. If you want chocolate from Switzerland to be
paid let me know

 

[Amy Blankenship]

No, thanks, I usually pick that up going through Amsterdam. But I might
wind up in Lake Constance next year...maybe you can buy me a beer LOL

Have a good one;

Amy