Solved

How do I do a duplicate count in one table and do a duplicate count in another table for those values only present in table1 in MS ACESS

Posted on 2011-03-18
10
328 Views
Last Modified: 2012-05-11
Super urgent question that I had to repost as my first question title was vague and confusing and no one was responding...if you can answer this feel free to snage the points off of the other question.

I have one table that I know is full of duplicate records and I want to do several things....I need to...

1.  do a duplicate count for this table (t1)
2. and then query only the lp values in table 2  (t2)that are in t1
3. and do a duplicate count for these values

for example lets say in t1 I have the following records

lp
ddd123
ddd123
ddd123
eee555
eee555

and in t2 I have the following records

lp
ddd123
ddd123
ddd123
ddd123
ddd123
www222
www222
eee555
eee555
eee555
eee555

I want the following output

lp                 t1dupcountlp   t2dupcountlp

ddd123                 3                    5
eee555                 2                    4

thanks
groovymonkey
0
Comment
Question by:groovymonkey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 35165260
something like this:
select t1.lp, t1.cnt t1dupcountlp, t2.cnt t2dupcountlp
  from ( select lp, count(*) cnt from table1 group by lp ) t1
  inner join ( select lp, count(*) cnt from table2 group by lp ) t2
   on ( t1.lp = t2.lp )

Open in new window

0
 
LVL 3

Expert Comment

by:cmgarnett
ID: 35165336
How about trying this?

select distinct Field1, (select count(1) from T1 where field1 = tbl1.field1) as T1Count, (select count(1) from T2 where field1 = tbl1.field1)as T2Count from T1 Tbl1
0
 

Author Comment

by:groovymonkey
ID: 35165579
Okay so sort of working but not there yet


SELECT DISTINCT lp, (select count(1) from t1 where lp = t1.lp) AS T1Count, (select count(1) from t2 where lp = t1.lp) AS T2Count
FROM t1;

I am getting the T2Count right but my T1Count is not right...I am sure that I subed something wrong...can you see the error?  It is adding the dup count where total and shwing each for each record...in this example the dupcounted for the first table is 580 so I have 580 going down the T1Count column for every record...
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35165598
what about:
SELECT DISTINCT lp
, (select count(1) from t1 tx where t1.lp = tx.lp) AS T1Count
, (select count(1) from t2 where t2.lp = t1.lp) AS T2Count
FROM t1;

Open in new window

0
 
LVL 3

Expert Comment

by:cmgarnett
ID: 35165865
ELECT DISTINCT lp, (select count(1) from t1 where lp = Tbl1.lp) AS T1Count, (select count(1) from t2 where lp = Tbl1.lp) AS T2Count
FROM t1 Tbl1;

Try using an alias for the controlling table as I think it's getting mixed up with the number of tables called T1 that it's working with.

Either that or alias the instance of T1 that you use for counting so it knows which T1 it's talking about.
0
 
LVL 3

Expert Comment

by:cmgarnett
ID: 35165895
Sorry, the "ELECT" at the beginning of my post should, of course, have been "SELECT".
0
 

Author Comment

by:groovymonkey
ID: 35169359
Okay I tried the following code and it worked for a small data set

SELECT DISTINCT lp
, (select count(1) from t1 tx where t1.lp = tx.lp) AS T1Count
, (select count(1) from t2 where t2.lp = t1.lp) AS T2Count
FROM t1;

but for a largee (90,000+) recordset my computer freezes...is this a db size issue or a query issue...sorry I do not know much about access...been a very long time since I have had to use it.  

If I have a table of 90,000 records if I delete all fields with the exception of the lp fields will it make it quicker?  Right now there are 10 fields per record (this is how I received it and I did not reduce the size of the dataset).

Thanks
groovymonkey
0
 
LVL 3

Accepted Solution

by:
cmgarnett earned 300 total points
ID: 35169472
If it works for a small set but struggles with a large one then I would think it's a problem with the size of the database you're working with and/or the machine you're working on.

Maybe reducing the number of instances of the tables from 3 to 2 might help.

SELECT field1, count(1) AS T1Count, (select count(1) from t2 where field1 = T1.field1) AS T2Count
FROM t1
GROUP BY Field1;

Sorry, you'll have to substitute your field and table names.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35169636
As noted in your previous question

Select temp2.lp, cnt1, cnt2,  cnt2 - nz(cnt1,0) as diff from
(Select t2.lp, Count(t2.lp) as cnt1 from t2 group by  t2.lp) as temp1
right Join
(Select t1.lp, Count(t1.lp) as cnt2 from t1 group by t1.lp ) as temp2
on temp1.lp =temp2.lp

should work.

This worked good in my db as I built it.
What I used for t1 has 140000 rows & 27 columns
What I used for t2 has 28000 rows & 58 columns

My substitute for lp was the foreign key of both tables, Long data type and indexed.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35169641
The result returns 15000+ rows almost immediately BTW, in my case
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question