SQLTeam.com | Weblogs | Forums

Access query on northwind database


#1

Hi, I have been practicing access using this database. This is probably something really simple but.....
I am wanting to bring back a result that will show how many of the products are packaged in tins/cans or bottles.
I can get the results but I cant group them into just bottles or cans it groups them according to their size also ie 24x150g can.
the sql for what I have now and I have been doing it via the query wizard ( sorry my version is in Finnish) but basic translation....
tuotteet is product, tuotenimi is product name, Määrä yksikössä is number of units ( which is where it says if tin or packet etc) Pullo is bottle and tölkki is can/tin
.
SELECT Count(Tuotteet.[Määrä yksikössä]) AS [CountOfMäärä yksikössä], Tuotteet.[Määrä yksikössä]
FROM Tuotteet
WHERE (((Tuotteet.[Määrä yksikössä]) Like "*pullo" Or (Tuotteet.[Määrä yksikössä]) Like "*tölkki"))
GROUP BY Tuotteet.[Määrä yksikössä]
ORDER BY Count(Tuotteet.[Määrä yksikössä]);

I have tried fiddling with the sql to get what I want but it just wont work. I cant help but think I'm missing something really simple.

Ideally I would like it to say
tölkki and then the count
pullo and then the count

Any help appreciated.


#2

You might want to create a temporary table that has the type as a column then do your count on that.

Even though this is a SQL Server forum, I did Access years ago and would create a query for the temporary table then query against the first query.