Count-IF

[CoffeeGuru]

Hi

I am trying to count how many plans a retail chain has over its stores.

So
WeekNo | StoreNo | Product |PlanNo
1 | 10001 | Dog | A
1 | 10001 | Cat | B
1 | 20008 | Dog | B
1 | 30005 | Dog | B

Query/report to show
Product | PlanNo A | PlanNo B
Dog | 1 | 2
Cat | 0 | 1

Numbers in Query/report are a count of Stores selling the product
One of the issues is that the data is duplicated over the weeks, I'm thinking to use max on week number so It only looks at the last available data but open to any ideas that will work better.
And how to write the query.

Currently I'm doing this
PlanA: Sum(IIf([PlanNo]="A",1,0)
PlanB: Sum(IIf([PlanNo]="B",1,0)
.
.
.

But it looks at the whole of the table for all the weeks

Can anyone come up with how to get around this.

 

[Mihail]

The DCount function will return the desired results.

 

[pr2-eugin]

Try the Max function in the SubQuery..

 

[CoffeeGuru]

The DCount function will return the desired results.

Hi Mihail

reading up on DCOUNT I believe you are correct and it should do what I want. However all my results are 0. Attempt to show Design View below

 

[pr2-eugin]

Do not use DCount or any Domain functions in a Query.. Their efficiency will be very very bad.. Use SubQueries instead..

 

[CoffeeGuru]

Do not use DCount or any Domain functions in a Query.. Their efficiency will be very very bad.. Use SubQueries instead..

Hi Paul

You are of course 100% correct.
The speed is awful, but I have yet to get my head around subqueries. I still struggle with left/right/inner and outer joins

I understand what they are and am reading about them on http://www.geeksengine.com/database/subquery/subquery-in-from-clause.php.

But still got to get mine to work in any way shape or form.

Sorry guys I'm new at this!!! and have been thrown in at the deep end.
Waiting for my course to be agreed and financed.
Red tape :banghead:

 

[pr2-eugin]

NOT Tested, try this..

SELECT [COLOR=Blue]whateverFieldsYouWant[/COLOR], Sum(IIf([PlanNo]="A",1,0) As PlanA 
FROM [COLOR=Blue]theTable[/COLOR]
WHERE theWeekNumber = (SELECT Max([COLOR=Blue]theWeekNumber[/COLOR]) As MaxOfWeek FROM [COLOR=Blue]theTable[/COLOR])
GROUP BY [COLOR=Blue]whateverFieldsYouWant[/COLOR];

Change the above to suit your needs. Technically, the Where of the Week number should have the SubQuery.

(SELECT Max([COLOR=Blue]theWeekNumber[/COLOR]) As MaxOfWeek FROM [COLOR=Blue]theTable[/COLOR])

Ofcourse changing the field names and table names to match your design..

 

[CoffeeGuru]

Hi Paul

It appears to trip up on the AS PlanC in the SELECT
at least that is what is highlighted

the msg box says...
Syntax error (missing operator) in query expression 'Sum(IIF([PlanNo] = "C",1,0) AS PlanC
FROM tblStoreMaster
WHERE [CM-DATA].Week = (SELECT Max([CM-DATA].week) As MaxOfWeek FROM [CM-DATA])
GROUP BY Family;'.

SELECT [CM-DATA].Chain, [CM-DATA].UID, [tblLive].Family,  Sum(IIf([PlanNo]="C",1,0) AS PlanC 
FROM tblStoreMaster
WHERE [CM-DATA].Week = (SELECT Max([CM-DATA].week) As MaxOfWeek FROM [CM-DATA])
GROUP BY Family;

 

[pr2-eugin]

Are you doing this in VBA? If so, wrap C inside Single quotes..

 

[namliam]

How about implementing a crosstab query?

 

[CoffeeGuru]

Are you doing this in VBA? If so, wrap C inside Single quotes..

no not VBA, Design View and stumbelling through SQL

Sorry Guys I must appear so thick

 

[pr2-eugin]

Wait the Code involves two tables, and you have not JOINED them together? Do the normal JOIN as you have shown in the IMG in Post#4.

Then in the Criteria section of the Week add the SubQuery..

PS : Try namliam's suggestion of Crosstab Query, it might be simpler on your side.

 

[CoffeeGuru]

How about implementing a crosstab query?

Paul, that looks like sound advice...

Hi namliam

Ive looked at the crosstab, its like a spreadsheet and I can see why you suggest it.

I'm getting results but the numbers do not add up.
I think this is because I need a distinct count somehow.

TRANSFORM Count(tblStoreMaster.StoreNo) AS NbrStoresSelling
SELECT tblLive.Family, [CM-DATA].UID
FROM ((tblLive INNER JOIN tblProducts ON tblLive.Family = tblProducts.SYMBOL) INNER JOIN [CM-DATA] ON (tblProducts.SKU = [CM-DATA].UID) AND (tblLive.Chain = [CM-DATA].Chain)) INNER JOIN tblStoreMaster ON (tblStoreMaster.Chain = [CM-DATA].Chain) AND (tblProducts.[COUNTRY CODE] = tblStoreMaster.CountryCode) AND ([CM-DATA].StoreNo = tblStoreMaster.StoreNo)
WHERE ((([CM-DATA].CountryCode)="DE") AND (([CM-DATA].Chain)="EXPERT"))
GROUP BY [CM-DATA].CountryCode, [CM-DATA].Chain, tblLive.Family, [CM-DATA].UID
PIVOT tblStoreMaster.PlanNo;

A store sells many products so
if there are 5 stores and they all sell 3 products the answer I'm getting is 15
However I am looking for 3 basing the query on the unique store number.

Getting close now that I am getting values for Store though, so thanks.

 

[namliam]

Then you need to do a sub-select, aka query on query.
Make a normal select first to do your unique thing, then base the crosstab on top of that.

 

[CoffeeGuru]

Sorry guys I'm posting my code so I can look back and laugh at my stupiditty later.


This does the Unique thing and appears to work

SELECT tblStoreMaster.CountryCode, tblStoreMaster.Chain, tblStoreMaster.PlanNo, Count(tblStoreMaster.StoreNo) AS CountOfStoreNo
FROM tblStoreMaster
GROUP BY tblStoreMaster.CountryCode, tblStoreMaster.Chain, tblStoreMaster.PlanNo
HAVING (((tblStoreMaster.CountryCode)="DE") AND ((tblStoreMaster.Chain)="EXPERT"));

This is the crosstab
listening to what the guys are saying here I need to slot the above code into the FROM bit of the code below somehow.

TRANSFORM Count(tblStoreMaster.StoreNo) AS count_of_stores
SELECT tblLive.Family, [CM-DATA].UID
FROM [COLOR=darkorange](tblLive INNER JOIN ([CM-DATA] INNER JOIN tblStoreMaster ON ([CM-DATA].StoreNo = tblStoreMaster.StoreNo) AND ([CM-DATA].CountryCode = tblStoreMaster.CountryCode) AND ([CM-DATA].Chain = tblStoreMaster.Chain)) ON tblLive.Chain = tblStoreMaster.Chain) INNER JOIN tblProducts ON (tblProducts.SKU = [CM-DATA].UID) AND (tblProducts.[COUNTRY CODE] = [CM-DATA].CountryCode) AND (tblLive.Family = tblProducts.SYMBOL)[/COLOR]
WHERE (((tblStoreMaster.CountryCode)="DE") AND ((tblStoreMaster.Chain)="EXPERT"))
GROUP BY tblStoreMaster.CountryCode, tblStoreMaster.Chain, tblLive.Family, [CM-DATA].UID
PIVOT tblStoreMaster.PlanNo;

Martin you're a plonker pull yourself together....There thats me told.

 

[pr2-eugin]

Sorry that just did not make sense to me. Did you solve it? Or are you still having problems? If it is the latter, use the Crosstab Query wizard, makes life so much easier..

If it is the former; Kudos !

 

[namliam]

What you do is make the first query, hit save and give the thing some logical name

Then for your crostab query you simply take your THING as input instead of your source table.