Return values that are zero

[Zampa1885]

Hi All
I've read a couple of answers to this question but most of them talk about joins in tables where my questions is about data from single table.

We have a table where we record the number of new suppliers who join our network are recorded, what type of supplier they are and the date they commence on the network and leave as well of course.

As we begin to use this new toolkit there is obviously a need to get data out and one of those questions is about ...."how many suppliers and of what type joined us in a given time period"
I can do this easily enough and get the correct answer, BUT what I cant work out how to do is to report when particular supplier types have had no new joiners in the time period.
What I am getting returned is:
Type - Count:
Supplier Type 1 - 17
Supplier Type 3 - 4
Supplier Type 6 - 8

What i want is:
Type - Count
Supplier Type 1 - 17
Supplier Type 2 - 0
Supplier Type 3 - 4
Supplier Type 4 - 0
Supplier Type 5 - 0
Supplier Type 6 - 8

Query is very simple and attached as an image. in time I'll make the dates refer to fields on a report generator, but for now How can i get a full list of supplier types even if none have been recruited for a particular type in the reporting period?

Thanks for reading and for any responses I get

 

[Minty]

You need to create a Query with your complete Supplier list and join it to the query you have already with a left join. Something like this will get you a distinct list of suppliers;

Select SupplierType From TblSupplierList Group By SupplierType

If you get stuck post of the sql of the two queries.
You can also achieve this with a sub query - but lets start simply.

 

[Ranman256]

Yes, OUTER join on the count query.
Show all recs in the type table
Show some recs in your count query.

 

[Zampa1885]

Bingo - Thanks very much. Full list is now showing, including where there are no records to count. easy when you know how Thanks again

 

[arnelgp]

fist Create a dummy table (tblDummy, with just one field, whatever fieldname you like).
add just 1 record.

Next you'll need to create a hardcoded Query:

SELECT "Supplier Type 1" As Type, (Select Count("*") From table1 Where Type="Supplier Type 1") As [Count] From tblDummy
UNION
SELECT "Supplier Type 2" As Type, (Select Count("*") From table1 Where Type="Supplier Type 2") As [Count] From tblDummy
UNION
SELECT "Supplier Type 3" As Type, (Select Count("*") From table1 Where Type="Supplier Type 3") As [Count] From tblDummy
UNION
SELECT "Supplier Type 4" As Type, (Select Count("*") From table1 Where Type="Supplier Type 4") As [Count] From tblDummy
UNION
SELECT "Supplier Type 5" As Type, (Select Count("*") From table1 Where Type="Supplier Type 5") As [Count] From tblDummy
UNION
SELECT "Supplier Type 6" As Type, (Select Count("*") From table1 Where Type="Supplier Type 6") As [Count] From tblDummy

replace table1 with the name of your table.