Enrol Date counts

[Guest]

I have a student database with an Enrolled date. I would like to create a
form where it displays how many students enrolled each week.
I would like to have text boxes that display the student count for each
program in this manner:
Music Dance Art
Drama
Jan 1, 2007 - Jan 7, 2007 7 5 1 0
Jan 8, 2007 - Jan 15, 2007 1 2 2 1
Jan 16, 2007 - Jan 23, 2007 2 0 1 2
ect.

is this or some thing similar possible
best regards

 

[Guest]

For us to tell you how to output your data in that fashion we need to know
how you are storing the data. What are the table & field names, datatype,
and relationships. Also post sample data.

 

[Guest]

thanks for your reply Karl, I apreciate it.
I have a table were all the fields are stored. There are two fields that I
want to use; the Enrolled Date (date/time field; mm/dd/yyyy) and a Program
field (Combo box with 5 possible values)
I would just like to count the number of records/students that fall within
in Program and break it down by week or at least by month.

regards

 

[Guest]

This does the basic but you need a field that has the number of students
unless you have a records for each student --
TRANSFORM Sum(visidro.Students) AS SumOfStudents
SELECT visidro.[Enrolled Date], Sum(visidro.Students) AS [Total Of Students]
FROM visidro
GROUP BY visidro.[Enrolled Date]
PIVOT visidro.Program;

 

[Guest]

This will count if you have individual records on the students.
TRANSFORM Count(visidro.Program) AS CountOfProgram
SELECT visidro.[Enrolled Date], Count(visidro.Program) AS [Total Of Students]
FROM visidro
GROUP BY visidro.[Enrolled Date]
PIVOT visidro.Program;

 

[Guest]

By the month --
TRANSFORM Sum(visidro.Students) AS SumOfStudents
SELECT Format([Enrolled Date],"mmmm yyyy") AS Enrollment,
Sum(visidro.Students) AS [Total Of Students]
FROM visidro
GROUP BY Format([Enrolled Date],"yyyymm"), Format([Enrolled Date],"mmmm yyyy")
PIVOT visidro.Program;

 

[Guest]

Thanks Karl, I do have individual records for each student.
Could you explain where/how I use this code?
Do I need to build a querie? I have dever used "TRANSFORM"

By the month --
TRANSFORM Sum(visidro.Students) AS SumOfStudents
SELECT Format([Enrolled Date],"mmmm yyyy") AS Enrollment,
Sum(visidro.Students) AS [Total Of Students]
FROM visidro
GROUP BY Format([Enrolled Date],"yyyymm"), Format([Enrolled Date],"mmmm yyyy")
PIVOT visidro.Program;

--
KARL DEWEY
Build a little - Test a little

thanks for your reply Karl, I apreciate it.
I have a table were all the fields are stored. There are two fields that I
want to use; the Enrolled Date (date/time field; mm/dd/yyyy) and a Program
field (Combo box with 5 possible values)
I would just like to count the number of records/students that fall within
in Program and break it down by week or at least by month.

regards

 

[Guest]

Create a new query. In design view select no tables, click on menu VIEW -
SQL View, copy my post and paste into the SQL view window. Edit out any hard
return that may be after "Enrollment," to have one line (true wrap ok) for
the SELECT statement.

--
KARL DEWEY
Build a little - Test a little

Thanks Karl, I do have individual records for each student.
Could you explain where/how I use this code?
Do I need to build a querie? I have dever used "TRANSFORM"

By the month --
TRANSFORM Sum(visidro.Students) AS SumOfStudents
SELECT Format([Enrolled Date],"mmmm yyyy") AS Enrollment,
Sum(visidro.Students) AS [Total Of Students]
FROM visidro
GROUP BY Format([Enrolled Date],"yyyymm"), Format([Enrolled Date],"mmmm yyyy")
PIVOT visidro.Program;

--
KARL DEWEY
Build a little - Test a little

thanks for your reply Karl, I apreciate it.
I have a table were all the fields are stored. There are two fields that I
want to use; the Enrolled Date (date/time field; mm/dd/yyyy) and a Program
field (Combo box with 5 possible values)
I would just like to count the number of records/students that fall within
in Program and break it down by week or at least by month.

regards

:

For us to tell you how to output your data in that fashion we need to know
how you are storing the data. What are the table & field names, datatype,
and relationships. Also post sample data.
--
KARL DEWEY
Build a little - Test a little


:

I have a student database with an Enrolled date. I would like to create a
form where it displays how many students enrolled each week.
I would like to have text boxes that display the student count for each
program in this manner:
Music Dance Art
Drama
Jan 1, 2007 - Jan 7, 2007 7 5 1 0
Jan 8, 2007 - Jan 15, 2007 1 2 2 1
Jan 16, 2007 - Jan 23, 2007 2 0 1 2
ect.

is this or some thing similar possible
best regards

 

[Guest]

That's great Karl, worked like a charm. I can now export it to Excel and
create my reports. Thank you very much for your help
take care
Victor.

Create a new query. In design view select no tables, click on menu VIEW -
SQL View, copy my post and paste into the SQL view window. Edit out any hard
return that may be after "Enrollment," to have one line (true wrap ok) for
the SELECT statement.

--
KARL DEWEY
Build a little - Test a little

Thanks Karl, I do have individual records for each student.
Could you explain where/how I use this code?
Do I need to build a querie? I have dever used "TRANSFORM"

By the month --
TRANSFORM Sum(visidro.Students) AS SumOfStudents
SELECT Format([Enrolled Date],"mmmm yyyy") AS Enrollment,
Sum(visidro.Students) AS [Total Of Students]
FROM visidro
GROUP BY Format([Enrolled Date],"yyyymm"), Format([Enrolled Date],"mmmm yyyy")
PIVOT visidro.Program;

--
KARL DEWEY
Build a little - Test a little


:

thanks for your reply Karl, I apreciate it.
I have a table were all the fields are stored. There are two fields that I
want to use; the Enrolled Date (date/time field; mm/dd/yyyy) and a Program
field (Combo box with 5 possible values)
I would just like to count the number of records/students that fall within
in Program and break it down by week or at least by month.

regards

:

For us to tell you how to output your data in that fashion we need to know
how you are storing the data. What are the table & field names, datatype,
and relationships. Also post sample data.
--
KARL DEWEY
Build a little - Test a little


:

I have a student database with an Enrolled date. I would like to create a
form where it displays how many students enrolled each week.
I would like to have text boxes that display the student count for each
program in this manner:
Music Dance Art
Drama
Jan 1, 2007 - Jan 7, 2007 7 5 1 0
Jan 8, 2007 - Jan 15, 2007 1 2 2 1
Jan 16, 2007 - Jan 23, 2007 2 0 1 2
ect.

is this or some thing similar possible
best regards