How can I get the number of occurences from 2 queries to put them in a chart in Access-CodePudding

I am new using access and sql so I might be organising my data wrongly.

I am trying to create some charts based the number of occurrences of documents opened and closed per month.

The data comes from a query that I have previously created that looks like the following:

query with dates opened and closed

I have tried 2 options:

One is making 1 query that counts the number of occurrences, however, since the closure date and opening date are related to the ID I end up with something like the following table. The count for the number of closures are related to the date opened and not the date closed.

Count of number opened and closed per month

I have tried making two different queries, one for count opened and one for count closed. But I don't know how to combine them to get a single bar chart in a report. Furthermore, the query for the closed count gives an additional row because not all documents are closed so some records are left empty.

closed count query

What I'm getting at is, how can I change the queries above to put the data in a bar chart to compare the number of documents opened and closed per month in the past year. I would also like to get the cumulative amount of documents opened and closed by the end of the month.

CodePudding user response：

Working from the query dataset you provided, consider:

Query1:

SELECT NCRNo, DateIssued AS TransDate, "Issued" AS TransType FROM DaysOpen
UNION SELECT NCRNo, ClosureDate, "Closed" FROM DaysOpen WHERE NOT ClosureDate IS NULL;

Chart RowSource:

TRANSFORM Count(*) AS [Count] 
SELECT (Format([TransDate],"MMM 'YY")) 
FROM [Query1]   
GROUP BY (Year([TransDate])*12   Month([TransDate])-1),(Format([TransDate],"MMM 'YY")) 
PIVOT [TransType];

Note that I have Access 2010 so this is using classic MSGraph, not Modern Chart.