Query to count duplicates and non duplicate 2

[julesl]

Hi

I've built an access database and I've been asked to build the query below, but I'm not sure how to, as my access and SQL skills are limited.

I have the following list of items and I need to list all the non duplicates from column 1 (REF), then count the duplicates from column 1 (REF) and count the non duplicates from column 3 (CODE) for each REF e.g

REF NAME CODE
CR9999 happy birthday WLBIR
CR9999 happy Christmas WLEAC
CR9999 happy anniversary WLANN
CR9999 happy Easter WLEAC
CR8764 Thank you WLTHN
CR8764 With Compliments WLTWC
CR4545 Get well soon WLGWS
CR4545 Get well soon Mum WLGWS
CR4545 Get well soon Nan WLGWS

Result:-

CR9999 4 3
CR8764 2 2
CR4545 3 1

This would tell me that for CR9999 there are 4 entries with that reference and there are 3 different sets of code that cover those 4 entries.

Any help would be gratefully appreciated.

Thanks

 

[r937]

select REF
, count(*) as refcount
, count(distinct CODE) as distinctcodes
from yourtable
group
by REF


rudy

http://r937.com/

SQL Consulting

 

[Golom]

Try
[blue][tt]
Select [Ref], Count(*) As [CountOfRef],

(Select Count(*) From tbl X
Where X.[Ref] = T.[Ref]
Group By X.

)       As [CountOfCode]

   From tbl T

   Group By T.[Ref]
[/tt][/blue]

 

[scriverb]

For those of you using Pre-A2K try this. Some of the techniques listed above are not available in A97. This requires 3 saved queries. Copy, Paste SQL, and Save as directed. Run the last query only.

First Saved Query: qryREFCounts

SELECT A.Ref, Count(A.Ref) AS Count_RefNonDups
FROM [red][i]yourtablename[/i][/red] AS A
GROUP BY A.Ref
HAVING (((Count(A.Ref))>1));

Second Saved Query: qryCodeCounts

SELECT A.Ref, A.Code, 1 AS UniqueCodes
FROM [red][i]yourtablename[/i][/red] as A 
GROUP BY A.Ref, A.Code;

Third Saved Query: qryREF_CODE_counts

SELECT CR.Ref, First(CR.Count_RefNonDups) AS Count_Dup_REF, Sum(CC.UniqueCodes) AS Count_NonDup_Codes 
FROM qryCountRef as CR INNER JOIN qryCodeCounts AS CC ON CR.Ref = CC.Ref
GROUP BY CR.Ref
ORDER BY CR.Ref DESC;

Now just run the query qryREF_CODE_counts

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[julesl]

Thank you for all your replies. I've tried the first two suggestions as I am using access 2000 but I cant get either to work.

I've amended the code as follows and have attached the results:-

(1)
select CATREF, count(*) as catrefcount, count(distinct DOCDEF) as distinctdocdef
from legacy tracker
group by CATREF;

Result:
Syntax error (missing operator) in query expression 'count(distinct DOCDEF)'

(2)
SELECT [CATREF], Count(*) AS CountOfCATREF, (Select Count(*) From [legacy tracker]
Where [legacy tracker].[CATREF] = [legacy tracker].[CATREF]
Group By [Legacy Tracker].[DOCDEF]) AS CountOfDOCDEF
FROM [Legacy Tracker]
GROUP BY [legacy tracker].[CATREF];

Result:
This asks me to Enter Parameter Values for Legacy Tracker.Catref and catref

Could you provide me with any more help please as my SQL knowledge is extremely limited?

Thanks

 

[scriverb]

In the first query add the red Square Brackets as the field names has a space in it:

select CATREF, count(*) as catrefcount, count([red][b][[/b][/red]distinct DOCDEF[red][b]][/b][/red]) as distinctdocdef
from legacy tracker
group  by CATREF;

In the second query You have used the same table name for both the main query and the select. ACCESS could be getting confused here. Try using Alias's like Golom recommened. Note the A and B table alias references.

SELECT A.[CATREF], Count(*) AS CountOfCATREF, (Select Count(*) From [legacy tracker] as B Where B.[CATREF] = A.[CATREF]
           Group By B.[DOCDEF]) AS CountOfDOCDEF
FROM [Legacy Tracker] as A
GROUP BY A.[CATREF];

See if this works now.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[julesl]

Thanks for your reply.

I've tried both of the queries and get the following problems:-

(Query 1) it doesn't recognise distinct docdef and asks for a parameter value to be entered

(Query 2) It runs the query but then comes up with a message 'At most one record can be returned by this sub query'. It then turns every field to #Name?
It also gives an incorrect value in the count DOCDEF column as it counts all entries for a given catref and not just the unique one.

Could you tell me what I'm doing wrong please?

Thanks

Jules

 

[scriverb]

I don't have an answer for you on the errors that you are experiencing. The second query looks just fine and I have duplicated your table and query here and am getting the same results. Maybe Golom can jump in here and help fix this up. I do know that the response that I gave you above will in fact give you the results that you requested. Copy and paste the queries with the appropriate table and field name changes and run the last query. It will give you the recordset requested.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ClemsonSeth]

Access has a really easy query wizard for finding duplicates in a table. This will get you part of the way through your problem without any code.

Try this: With Access open to your queries window, look at for a button that has the icon for a new query (different from the 'Create query by using Wizard' in the list of queries). It just says "New" next to it.

When you click on that you get a window asking what type of query you want - choose the 'duplicates' one and follow the wizard.

Very easy. Now you've got a good chunk of your problem licked and can either modify that query to also find the number of unique records or create another query that does that for you.

Maybe oversimplified, but it works for us newbies.