Count of Students in Class

[Guest]

I have tblIssIn that has records of IssInID(auto#),LocID, StartDate and
EndDate.

I need to have a daily count of students in ISS as well as a count for the
next 14 days as well.
Realistically I need a subform that lists today's date and then the next 14
days with a count of students in ISS for each of the days. How can I do that?

 

[Guest]

According to your table structure, you have no students. Apparently you
aren't tellling us near enough about your table structure, what you are
counting, some sample data, and desired display in the query.

 

[Guest]

tblIssIn is described below.

AutoNumber LocalID StartDate EndDate
### 12345 10/15/2007 11/15/2007
### 54321 10/12/2007 10/31/2007
### 24680 9/29/2007 10/31/2007
### 13579 11/5/2007 11/15/2007


And on and on.

What I need to determine how many of those students are in ISS from Today's
date until 2 weeks from today. I just don't really know how to do that. See
below.
There are duplicate LocalIDs as the students will have ISS assignments
throughout the year.

10/31/2007 - 3
11/1/2007 - 1
11/2/2007 - 1
11/3/2207 - 1
11/4/2207 - 1
11/5/2207 - 2
~~~~~
11/14/2007 - X

 

[Guest]

I would create a table with all dates within your range of interest:

tblAllDates
=========
TheDate date/time

You can then create a query with tblAllDates and tblIssIn. Don't use any
join lines and try a sql of:

SELECT TheDate, Count(*) as NumOf
FROM tblAllDates, tblIssIn
WHERE TheDate Between StartDate and EndDate
GROUP BY TheDate;

 

[Guest]

Worked like a charm. Thanks Duane.