count DISTINCT

[CoffeeGuru]

Hi Guys

I have seen this discussed in various places but I cannot for the life of me make any of the suggestions i've seen solve my problem.

I have a query that pulls out first and last week number.
I wanted to add a count of distinct week numbers to see if there are any weeks missing.

This is my SQL so far

SELECT Combined.Country, Combined.Chain, Combined.Year, First(Combined.WK_No) AS FirstOfWK_No, Last(Combined.WK_No) AS LastOfWK_No, Count(Combined.WK_No) AS CountOfWK_No
FROM Combined
GROUP BY Combined.Country, Combined.Chain, Combined.Year
ORDER BY Combined.Country, Combined.Chain, Combined.Year;

Unfortuately as it stands it just counts around 10688667 weeks where it should for my data show around 37 and

Count(DISTINCT(Combined.WK_No)) AS CountOfWK_No

does not work either.

I am using Access 2010

 

[pr2-eugin]

Not entirely sure if I follow what you mean, could you give a shot at explaining again? Have you considered GroupBy? That would auto eliminate the Duplicates..

Please could you show the links where you have discussed this issue as it will save everyone's time and effort 'reinventing the wheel'.

 

[namliam]

Count(Distinct column) as such doesnt exist in access unfortunately

It is possible but you have to do it in two parts:
1) Select distinct Week from yourtable
2) Select count(*) from 1)

 

[CoffeeGuru]

Hmm
maybe an example will help, as I dont have enough credit I cannot attach a link to dropbox so here goes:

Combined
Country Chain Year WeekNo ProductID
GB ABC 2013 1 A
GB ABC 2013 1 B
GB ABC 2013 1 C
GB ABC 2013 2 A
GB ABC 2013 3 A
GB ABC 2013 10 A

My Query needs to show
Country Chain Year Week (start) Week (last) Weeks (total)
GB ABC 2013 1 10 4


I have tried and tried and tried to create proper columns here but it just wont do it, so I hope the example makes sense. :banghead:

From this I can see there are some weeks of data missing.
I already have a report that uses DISTINCTROW so I can scan through to find the missing data.
but I am working with 5 different countries and 300 different chains

Is this explaination better?
BTW I have not posted anywhere else about this, just googled the topic

 

[pr2-eugin]

Try if this works for you.. Although this will require another saved Query..

SELECT sampleTable.Country, sampleTable.Chain, sampleTable.YearFieldName, Min(sampleTable.WeekNo) AS MinOfWeekNo, Max(sampleTable.WeekNo) AS MaxOfWeekNo, Q.CountOfWeekNo
FROM (SELECT subQry.Country, Count(subQry.WeekNo) AS CountOfWeekNo FROM subQry GROUP BY subQry.Country)  AS Q INNER JOIN sampleTable ON Q.Country = sampleTable.Country
GROUP BY sampleTable.Country, sampleTable.Chain, sampleTable.YearFieldName, Q.CountOfWeekNo;

While the subQuery would be..

SELECT sampleTable.WeekNo, sampleTable.Country 
FROM sampleTable 
GROUP BY sampleTable.WeekNo, sampleTable.Country;

Replace sampleTable with your table name, and other field names..

 

[CoffeeGuru]

Hi Paul
Thanks for your time.
I am getting the same result for everything and I know that is not correct.
I had to ammend your code a tad so maybe thats my problem.

[COLOR=red]SELECT Combined.Country, Combined.Chain, Combined.Year, First(Combined.WK_No) AS FirstOfWK_No, Last(Combined.WK_No) AS LastOfWK_No, Q.CountOfWeekNo[/COLOR]
[COLOR=red]FROM (SELECT SubQry.Country, Count(SubQry.Wk_No) AS CountOfWeekNo FROM SubQry GROUP BY SubQry.Country)  AS Q INNER JOIN Combined ON Q.Country = Combined.Country[/COLOR]
[COLOR=red]GROUP BY Combined.Country, Combined.Chain, Combined.Year, Q.CountOfWeekNo;[/COLOR]

 

[pr2-eugin]

So first you created a Query that will be..

SELECT Combined.Wk_No, Combined.Country 
FROM Combined 
GROUP BY Combined.Wk_No, Combined.Country;

Saved it by giving a name SubQry, then created the next Query..

SELECT Combined.Country, Combined.Chain, Combined.Year, First(Combined.WK_No) AS FirstOfWK_No, Last(Combined.WK_No) AS LastOfWK_No, Q.CountOfWeekNo
FROM (SELECT SubQry.Country, Count(SubQry.Wk_No) AS CountOfWeekNo FROM SubQry GROUP BY SubQry.Country) AS Q INNER JOIN Combined ON Q.Country = Combined.Country
GROUP BY Combined.Country, Combined.Chain, Combined.Year, Q.CountOfWeekNo;

I worked with some sample data, it seems to get the result.. Anyway, could you upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)