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
301 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
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 142

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
 
LVL 142

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now