• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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

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
groovymonkey
Asked:
groovymonkey
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
cmgarnettCommented:
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
 
groovymonkeyAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
cmgarnettCommented:
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
 
cmgarnettCommented:
Sorry, the "ELECT" at the beginning of my post should, of course, have been "SELECT".
0
 
groovymonkeyAuthor Commented:
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
 
cmgarnettCommented:
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
 
Nick67Commented:
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
 
Nick67Commented:
The result returns 15000+ rows almost immediately BTW, in my case
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now