Query to find duplicates

opazzo

Board Regular
Joined
Dec 21, 2005
Messages
65
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 ?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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

Board Regular
Joined
Dec 21, 2005
Messages
65
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,067
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 21, 2005
Messages
65

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

Well-known Member
Joined
Feb 26, 2007
Messages
6,067
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

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

Board Regular
Joined
Dec 21, 2005
Messages
65
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
So why do you have 5 tables?
 

opazzo

Board Regular
Joined
Dec 21, 2005
Messages
65
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

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
Top