Query to find count of open records

[BB_Bryan]

Hello
I have a table with 3 fields. Record ID / Date Created / Date Closed. What i am trying to do is count the total open cases at any given date. So essentially I want to know day by day how many cases were in an "Open State"
Thank you for any suggestions.

[pbaldy]

SELECT DateCreated, Count(*) As HowMany
FROM TableName
WHERE DateClosed Is Null
GROUP BY DateCreated

[BB_Bryan]

Thank you. Maybe I did not explain it correctly. If if just counted those files where date closed is not null I would get all cases that are open right now. I am looking to go back in time. Example how many cases were open on March 1 2017. So at this point in time (today) many of those files records would be closed.

[pbaldy]

Ah, try

SELECT DateCreated, Count(*) As HowMany
FROM TableName
WHERE DateClosed Is Null OR DateClosed > #3/1/2017#
GROUP BY DateCreated

[BB_Bryan]

That's great. Now that I am parsing through the data I see there can be multiple close and reopen. How can I account for that? Thanks in advance

[pbaldy]

Can you attach the db with sample data and your expected result?

[BB_Bryan]

Can you attach the db with sample data and your expected result?

I have a sample file attached. Basically what i am trying to do is say on a day by day basis going back in time how many cases were open on day x or y or z. Thank you

[rpeare]

There are no duplicate record numbers in your database which you seem to indicate is causing part of your issue, if you have just one record per record_ID the previous answers should have gotten you what you want

what was your expected result set from the data you submitted?

[ssanfu]

You should check your query. For 3/6/2015, I counted 5 records but the query returned a count of 8.
This is the query from your dB. Notice the date:

SELECT tbl_Data.Date_Created, Count(tbl_Data.Record_ID) AS CountOfRecord_ID
FROM tbl_Data
WHERE (((tbl_Data.Date_Closed) Is Null Or (tbl_Data.Date_Closed)>3/#1/1/2017#))
GROUP BY tbl_Data.Date_Created;

Changing the SQL to

SELECT tbl_Data.Date_Created, Count(tbl_Data.Record_ID) AS CountOfRecord_ID
FROM tbl_Data
WHERE (((tbl_Data.Date_Closed) Is Null Or (tbl_Data.Date_Closed)>#3/1/2017#))
GROUP BY tbl_Data.Date_Created;

provides a count of 5 for the created date of 3/6/2015......