Unmatched query assistance required

[Kiron]

Hi Champs

I have a simple problem. And i tried access quer unmatched wizard but some reason its not giving the result as expected, hence am asking.

I have 2 tables in query to show unused worktypes per user.


Table1
User refno worktype
UserA 1 worktype1
UserB 2 worktype2
UserA 3 worktype2

Table2
Worktype1
Worktype2
Worktype3
Worktype4
Worktype5


Find unmatched
Result should be
User worktype
UserA worktype3
UserA worktype4
UserA worktype5
UserB worktype1
UserB worktype3
UserB worktype4
UserB worktype5

Please advise on how i should write this query.

Thanks a million

 

[arnelgp]

select table1.user, table2.worktype from table1, table2  
where  table1.worktype<>table2.worktype

 

[Kiron]

Hi

I Did as you mentioned but am not getting the desired result.
It seems to work for user B but skips User A.
So for user b it shows unused worktypes, but for user A it shows all worktypes. Not aure whats wrong.
Please advise.
Thank you

 

[arnelgp]

SELECT Table1.User, Table2.Worktype
FROM Table1, Table2
GROUP BY Table1.User, Table2.Worktype, [User] & [Table2].[Worktype]
HAVING ((([User] & [Table2].[Worktype]) Not In (SELECT User & Worktype FROM Table1)))
ORDER BY Table1.User, Table2.Worktype;

 

[CJ_London]

you need two queries, the first to list all possible options to tbl1 then left join to tbl1

Q1.

SELECT Tbl1.User, tbl2.worktype
FROM tbl1, tb2

Q2.

SELECT Q1.*
FROM Q1 LEFT JOIN tbl1 ON Q1.User=tbl1.User and Q1.worktype=tbl1.worktype
WHERE tbl1.worktype is null

or combine into 1 query

SELECT Q1.*
FROM (SELECT Tbl1.User, tbl2.worktype
FROM tbl1, tb2) Q1 LEFT JOIN tbl1 ON Q1.User=tbl1.User and Q1.worktype=tbl1.worktype
WHERE tbl1.worktype is null

 

[Kiron]

Awesome cj_london