Query question

[j2curtis64]

I have data in the following table.

MonthStatusCount01-Nov-10Closed1201-Dec-10Closed1201-Dec-10Open201-Jan-11Closed1201-Jan-11Open101-Feb-11Closed1201-Feb-11Open301-Mar-11Closed501-Mar-11Open601-Jun-11Open5
I want to create a report where I can display the following columns:



Month Open Issues Closed Issues Totals Issues

I would use the data provided in the table above. Should I just create two seperate queries (Open and closed) and then join them together? I know an equal join will not work because there are months where i'd have only closed issues but not open (i.e, 11/1/2010)

Thx

[June7]

You can have closed but no open issues for a date and appears the opposite is also true. Then need to:

1. A query for just all distinct dates in the table

2. Two queries, closed and open

3. Join the 3 queries.

[j2curtis64]

Actually, it is possible to have months that only have open or closed issues. See below.

If i create a query that joins Open and Closed and use Closed as the primary the Month of June will need return records since it only contains Open issues.
MonthStatusCountOfStatus11/1/2010Closed1212/1/2010Closed1212/1/2010Open21/1/2011Closed121/1/2011Open12/1/2011Closed122/1/2011Open33/1/2011Closed53/1/2011Open66/1/2011Open5

[June7]

Yes, I noticed that, and offered a solution. Have you attempted it?

[j2curtis64]

When I run the query you suggested, the month of June is missing (Open issues only).

[June7]

Post SQL statments for analysis.

[j2curtis64]

SELECT [Closed Issues by month].Month, [Open issues by month].[Issues Identified], [Closed Issues by month].[Issues Identified]
FROM ([Open issues by month] RIGHT JOIN Months ON [Open issues by month].Month = Months.Month) RIGHT JOIN [Closed Issues by month] ON Months.Month = [Closed Issues by month].Month;

[June7]

SELECT [Months].Month, [Open issues by month].[Issues Identified], [Closed Issues by month].[Issues Identified]
FROM [Closed Issues by month] RIGHT JOIN ([Open issues by month] RIGHT JOIN Months ON [Open Issues by month].Month = Months.Month) ON [Closed Issues by month].Month = Months.Month;

[j2curtis64]

That did it.

Thx for you help.