CODEWITHSUNDEEP
sqlms-accesscount
sebastien leblanc
sebastien leblanc

Reputation: 675

SQL count query not working

I have problem with this query:

SELECT Ordre.Objet, Count(Ordre.Objet) AS CompteDeObjet
FROM Ordre INNER JOIN Avis ON Ordre.[Ordre SAP] = Avis.[Ordre SAP]
GROUP BY Ordre.Objet, Avis.[Date Appel], Ordre.Objet
HAVING (((Avis.[Date Appel])>#8/1/2011# And (Avis.[Date Appel])<#10/20/2011#) AND ((Ordre.Objet) Is Not Null));

That I generated using Access 2003. it should count the number of items of each kind in the Object column but it only shows a count of one per different item... Can't seem to figure out how to make this work.

[EDIT]

Considering the first two answers, I changed my code to the following, but I still get the same result:

SELECT Ordre.Objet, Count(Ordre.Objet) AS CompteDeObjet
FROM Ordre INNER JOIN Avis ON Ordre.[Ordre SAP] = Avis.[Ordre SAP]
WHERE (((Avis.[Date Appel])>#8/1/2011# And (Avis.[Date Appel])<#10/20/2011#) AND ((Ordre.Objet) Is Not Null))
GROUP BY Ordre.Objet;

[EDIT #2] here is a sample of my data:

Ordre SAP Objet

11147212 Simplex

11147214 Simplex

11147215 Simplex

11147216 Simplex

11147225 Simplex

11147240 Auto Level

11147243

11147247 CANOPY

11147259 Capteur

And here is what the query from the last edit gives me:

Auto Level 1 CANOPY 1 Capteur 1 Simplex 1

All of my data is included in the date range specified in the query. Sorry, i don't know how to show this in a proper table, I'm new here...

Upvotes: 0

Views: 1680

Answers (3)

sebastien leblanc
sebastien leblanc

Reputation: 675

Ok, so I figured out what my problem was:

I have way more Ordres than I have Avis and not all avis have an ordre attached. My query somehow only counts the objects that have an Avis attached to it (because of the JOIN clause?) so while testing, I just randomly placed lots of different values to random record in the order table. it just so happened that I put one of each on Ordres that had corresponding Avis...

Silly me :)

Thanks everyone though, help is always apreciated

Upvotes: 0

Brimstedt
Brimstedt

Reputation: 3140

The query will give one row / count per item you GROUP BY.

You are grouping on:

 GROUP BY Ordre.Objet, Avis.[Date Appel], Ordre.Objet

So, you will get one cound for each Objet / Date Appel combination

Upvotes: 2

Len
Len

Reputation: 542

Try moving what is in the HAVING clause to the WHERE clause. HAVING filters on grouped items, it may be stopping the query from counting the items properly.

Upvotes: 0

Related Questions