Query to find duplicates

[opazzo]

Hi all,

Noob in Access and I can't find my way around this one.

Here is my problem :

I have 5 tables. All of them have 3 columns: Doc Number / Rev / Title

I would like to run a query to list all docs in table 1 and corresponding revs - if any - in other tables.

And another for table 2, then for table 3..... up to 5

Any ideas ?

 

[CT Witter]

How do you know when you have a revision? Some sample data of how your data looks and what you are trying to accomplish would help.

CT

 

[opazzo]

How do I know when I have a revision ? Well that is when the field is populated.

I can not post my Tables here but that's how they look like :

DOC REV TITLE
XXX 1 BLABLA
XYX 2 BALBAL
XYY P LBALBA

Table 2 would be like

DOC REV TITLE
XXX D BLABLA
CCC 2 BALBAL
XYY 6 LBALBA

and so on

What I would like to get is something like this :
DOC - REV FROM TABLE 1- TITLE - REV FROM TABLE 2 - REV FROM TABLE 3 .......
XXX 1 BLABLA D Blank


A doc can be listed in one or two table only, I still would like to get this reported by hving the blank revision listed where applicable.

 

[alansidman]

It looks like a simple left join on the first table is all you need. Create a query, join the tables on the DOC field. Right click on the join link and change it to option 2 in the radio buttons. That should work if I am understanding you correctly.

Alan

 

[opazzo]

ADVERTISEMENT

Thanks Alan, that is working now.

This leads me to a new question :

I have now a single consolidated table with doc number and rev number from 5 tables (one by column)

How do you I filter this list to make only appear those records with at least 2 rev number (ie two colums populated) ?

 

[alansidman]

In your criteria for each of the fields, put "Is not null". Putting this in both fields requires that you have two revs.

Alan

 

[Norie]

ADVERTISEMENT

opazzo

Why do you have 5 separate tables in the first place?

They all have the same data structure, so combining them into one table would make what you want to do far easier.

 

[opazzo]

Alan,

I can't do that because it will return results where only all fields are pouplated, what I want is two at least but not necessarily all of them.


Norie,

I consolidated all documents number within one single table but not the revisions as I want to be able to identify the source for each revision

 

[Norie]

So why do you have 5 tables?

 

[opazzo]

Each table reflects the status of a different project.

Some docs are shared across multiple projects.

What I want is to get the rev number from each project for these shared docs.

My material is :

- 1 Consolidated table of all doc number (no duplicates)
- 5 tables with doc numbers and rev number (1 for each project)

What I want is to get the listing of shared docs and correspoding revisions, ie docs appearing in at least 2 tables.

What I 've done so far is making a query to return the rev number from each project against the consolidated list.

This gives something like this :

Doc Rev_ Proj1 Rev_ Proj2 Rev_Proj 3
XXX 1 5 10

Doc Rev_ Proj1 Rev_ Proj2 Rev_Proj 3
XXY 12

Doc Rev_ Proj1 Rev_ Proj2 Rev_Proj 3
XYY 1 0


Which is good.

Now I want to refine this list to get only those docs as in the first and third example.
That is to say docs with at least 2 rev, but not only docs with 5 rev.
I can't find the trick to do that.

I may be on the wrong way to achieve that. Please let me know

Thanks