Query to find duplicates

opazzo

Board Regular
Joined
Dec 21, 2005
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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) ?
 
Upvote 0
In your criteria for each of the fields, put "Is not null". Putting this in both fields requires that you have two revs.

Alan
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top