query for data range and one-to-many structure

[brainstorms]

I have a database structured as follows:

tblcases (caseid, name, date) where date is the date the 'case' was first opened

tblclosed (closedid, caseid, closedate) where closedate is the date the 'case' was closed

tblreopen (reopenid, caseid, reopendate) where reopendate is the date the 'case' was reopened

tblclosed and tblreopen have a one-to-many relationship with tblcases

All 'cases' can be opened, then closed and reopened any number of times.

I need to create a query which will tell me how many 'cases' were open on a specific date. I cannot work out how to structure this and end up with multiple instances of records where they have several closed and re-opened dates. Getting how many cases are open today is easy. But how to get how many were open at a specifi date or within a range of dates I cannot figure.
Is the basic table structure wrong?

This is really urgent (aren't they all!) I'm trying to complete a job with this.

Any help greatly appreciated.

 

[Zonie32]

If you want to get a count of all cases that are Open, then just create a new query using the tblcases table.

Choose CaseID and Date. In the Date criteria section type Between [StartDate] and [EndDate] and in the CaseID field choose Count in the Totals section. Make sure you click the totals button in the toolbar, (looks like an E).

 

[brainstorms]

Thanks, but that would only count those that have been opened (i.e have a start date) and does not account for closed or re-opened dates in the other tables. They are in other tables becuase there can be more than one closed or reopened date for each case.
If a case opened on Jan 1st, closed on Jan 10th, then reopened on Jan 30th, the query, when run for the date range 01 - 10 Jan should show it as open, but if run for 10 - 30 Jan show it as closed, then if run for date range 01-15 Feb shown it as open.