Counting one table based on data from another table?

[Noozer]

I have the following tables:

Choices:
ChoiceKey: autonumber
Category: Text
Issue: Text

Choices2Ticket:
ChoiceKeyLink: number
TicketKeyLink: number

Tickets:
TicketKey: autonumber
Created: date
Description: text

I have the following query:

SELECT COUNT(ChoiceKeyLink) AS ChoiceQty, Issue, Category FROM (Choices LEFT
JOIN Choices2Ticket ON Choices.ChoiceKey=Choices2Ticket.ChoiceKeyLink) GROUP
BY ChoiceKeyLink, Category, Issue;

....that returns a list of all choices, and a count of how many times they
appear in the Choices2Ticket table.

Results would look like this:

ChoiceQty | Issue | Category
0 | No Gas | Auto
2 | Fuse | Auto
8 | Lost Key | Home
0 | Doorbell | Home

I need to filter the count so that it only counts Issues when the ticket was
created between two dates. I have the following query, which works, except
that it doesn't return any rows with a zero count (ie Issue wasn't used
between the dates specified):

SELECT COUNT(ChoiceKeyLink) AS ChoiceQty, Issue, Category FROM (Choices LEFT
JOIN Choices2Ticket ON Choices.ChoiceKey=Choices2Ticket.ChoiceKeyLink) LEFT
JOIN Tickets ON Choices2Ticket.TicketKeyLink=Tickets.TicketKey WHERE Created
BETWEEN #2005/01/01# AND #2006/01/01# GROUP BY ChoiceKeyLink, Category,
Issue;

Results look like this:

ChoiceQty | Issue | Category
2 | Fuse | Auto
8 | Lost Key | Home

....notice that the 0 quantity rows are now missing.

How can return all issue names, all category names, and the quantity of
times an issue exists for tickets between two dates?

 

[[MVP] S.Clark]

You need to perform an Outer Join from the Category table... but it appears
you don't have a category table. So, make a category table, then outer join
it.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting