Finding Unique entries & Deleting Them....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi There,

I had a database with duplicates with following structure:-

Company, Door, Street, Area, Pin, STD, TELNO1, TELNO2, TELNO3, FAX, CLASS,
BCCODE, PTC

Now I want to match the database with Company + TELNO1 + CLASS. and wanted
to have all the fields. Also I wanted to have that entry which do have
maximum Phone numbers.

For Example: - If There are 4 entries where Company + TELNO1 + CLASS are
common, Out of which one entry had 3 Phone numbers. I wanted to have that
particular entry.

Kindly let me know step by step as how I can do that in MS Access.

I hope above is clear. Please get back for any clarification.

Thanks.

Nimesh
 
Hi,



A first query like:


SELECT Company, Telno1, class , MAX(Telno1 IS NULL + telno2 IS NULL +
telno3 IS NULL) As MaxTelNumbers
FROM mytable
GROUP BY Company, Telno1, class



then, if you saved it as q1, in another query:


SELECT mytable.Company, mytable.telno1, mytable.class, LAST(mytable.telno2),
LAST(mytable.telno3)
FROM mytable INNER JOIN q1 ON mytable.company=q1.company AND
mytable.telno1=q1.telno1
WHERE ( mytable.Telno1 IS NULL + mytable.telno2 IS NULL + mytable.telno3
IS NULL) = q1.MaxTelNumbers
GROUP BY mytable.Company, mytable.telno1, mytable.class


should do the job, thanks to LAST (which can be replaced by FIRST, if you
prefer).


Note that this assumes telno1 is not likely to be null.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top