Query for identifying Docs with different owners

[@MeDaveT]

Wondering if anyone can help



I have an Access db with two tables which detail documents and there owners (up to 10 owners).

I am trying to come up with a query which will show me the names of the documents which don't have matching owners (Note owners could be in different order as per the attachment).

[CJ_London]

Your data is not normalised each table should be structured as

DocID autonumber
DocName text
Owner Text

then it would be easy for a simple comparison - to find owners in table1 who are not in table2

SELECT *
FROM table1 left join table2 on table1.docname=table2.docname and table1.owner=table2.owner
where table2.docid is null

and owners in table2 not in table1

SELECT *
FROM table1 right join table2 on table1.docname=table2.docname and table1.owner=table2.owner
where table1.docid is null

With your data as you have it structured you would have to compare each column in table1 with 10 columns in table2 and then the reverse - 200 comparisons

[@MeDaveT]

Thanks but what I am looking for is a query which will simply return a list of documents where the owners don't match i.e. using the data in the attachment the query should return BlueberryPieRecipe

[CJ_London]

OK - in that case you just need 100 queries - take the first of my queries as a model then instead of just comparing owner to owner you need

table1-owner1 match to table2 - owner1
table1-owner1 match to table2 - owner2
....
....
table1-owner2 match to table2 - owner1
table1-owner2 match to table2 - owner2

you would probably want to connect these 100 queries in a union query

[@MeDaveT]

100 queries !!!!!

On that basis using Access might not be a feasible option.

[CJ_London]

It is only because you have your data set up like Excel rather than as a database. Set your data up as I suggested and you just need the two queries

Excel data is short and wide, databases are tall and narrow

How would you do it in Excel?

[CJ_London]

If your owners are always in the same order for any given docname then you could do something like

Select table1.*
FROM table1 left join table2 on table1.docname=table2.docname and table1.owner1=table2.owner1 and table1.owner2=table2.owner2....
where table2.docname is null

which is just one query, but you said

Note owners could be in different order as per the attachment

so I presumed this would not work

[orange]

It isn't Access that is the issue. You are not using Access(relational database) as it was intended.
it's a little bit like using a monkey wrench as a hammer.

You really should do some research Normalization, Relational database design principles.

Don't blame the tool if you don't know how to use it.