asked on

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

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmgarnett

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

groovymonkey

ASKER

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...

Guy Hengel [angelIII / a3]

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

cmgarnett

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.

cmgarnett

Sorry, the "ELECT" at the beginning of my post should, of course, have been "SELECT".

groovymonkey

ASKER

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

ASKER CERTIFIED SOLUTION

cmgarnett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick67

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.

Nick67

The result returns 15000+ rows almost immediately BTW, in my case