Counting Active Records Between 2 Dates

[catat]

Hello Everyone,



I am trying to create a report that will calculate the number of children served within a period of two dates (a quarter). The issue I am having is that within the Children table, there is no date field, so I am not sure how to determine which children were "Active" during this period.

I have tried taking information from three different tables. All three tables are connected through the FuID (Family Unit ID).
The first is the Family Unit table which consists of FuID , FamName , Active (Yes/No).
The second is the Case Status table which consists of CsID, FuID, Status (Waitlist, Active, Closed, etc.), and StatusEffective (date).
The third is the Children table which consists of ChID , FuID, LastName, FirstName, DOB, and Active (Yes/No).
The first two tables are not really necessary for the report, but I tried using the StatusEffectiveDate from the Case Status to get the Between dates, but this only pulls children that have had their Case Status updated within that time frame.

Ultimately, I would like the query to pull .... What children were checked off as Active between Date 1 and Date 2, regardless if they became inactive between these dates. Is this possible without a date stamp in the table?

For my first database, everything had seemed to be set up correctly (thanks to you guys) until this problem!

Thanks in advance!

[TheShabz]

Tables dont have a "hidden" datestamp on record creation or modification so unless there are dates to work with, there really isnt anything you can do... at least not that I know of. Unless Microsoft came out with a GoBackInTime() function, I think you're stuck.

[Rawb]

Oh man, a GoBackInTime() function would be AWESOME!

You should write one Shabz!

[catat]

Ha! Yes, that would be awesome...

So... is there anything I can do now to pull this information in the future?! Meaning is there something I should add to the Children table?...

Thanks for replying anyways..

[Rawb]

I would just add a (Short) Date field to it with a Default Value of =Date().

Just be sure to set this field to the day before your last quarter started on all of your pre-existing Records so they don't show up in any of your queries erroneously.

[catat]

I managed to figure it out by using a date from a fourth table.. It works perfectly so far.

The parameter was set for,,
[Start Date (dd/mm/yyyy)]
[End Date (dd/mm/yyyy)]

The criteria within the query was..

Case Status StatusEffectiveDate ServiceStartDate
Criteria: "Active" <=[End Date (dd/mm/yyyy)]
Or: "Closed" <=[End Date (dd/mm/yyyy)] Or >=[Start Date (dd/mm/yyyy)]


Thanks!