help please, big project

[daniel]

IS THERE ANY WAY TO DO THIS DIFFERENT ?

DATABASE TO RECORD sTUDENT ATTENDENCE, RESCHEDULING, CANCEL OR VACATION.
TEACHER CONTROL OF FREE TIME, WORKED OR SUB STUDENT.

CONTINIOUS FORM:

MONTH DATE DAY HOURS TIME STUDENTNAME; ATT MU CC NS
VC; TEACHER WORKED FREE SUB
FEB 02-01 MON 12.00-12.30 . 30 JIM DEW
Y BILL N
FEB 02-01 MON 12.30- 1.00 . 30 JOE DOE
Y TIM Y

down to 8 pm. STUDENTS CHANGE EVERY HALF HOUR.
THEN TUESDAY, THEN WEN......

TABLES FOR EACH MONTH(JAN-DEC). ALL WEEKDAYS ( MON-SAT) FOR EVERY MONTH.
THAT'S A LOT OF TABLES, MANY STUDENTS, over 800, 51 TEACHERS. A LOT OF DATA.

MULTIUSER ENV, 5 PC's. TABLES ON SERVER.
I BELIEVE, THERE WOULD BE TO MUCH DATA COMING FROM THE SERVER, EVEN WITH
BEGIN DATE-END DATE.
IF I ASK FOR DATA OF ONE STUDENT, ALL OTHER DATA IN THAT TIME SPAN WOULD
COME OVER THE NETWORK.
I BELIEVE I AM CORRECT WITH THAT ASSUMPTION. I AM AFRAID IT WILL SLOW
EVERYTHING DOWN.

IS THERE ANY WAY IT COULD BE CONSTRUCTED DIFFERENT. OR ANY OTHER SOLUTION
???

CLIENT-SERVER WOULD BE GREAT BUT OWNER WON'T SPEND THE MONEY.
IT MIGHT BE TO MUCH DATA FOR MSDE. MAYBE.

WOULD SOMEBODY HAVE AN IDEA ?

 

[BruceM]

First, turn off the Caps Lock when posting. All caps is regarded as
shouting, which is rude.

It is difficult to know what fields such as ATT MU and CC NS mean, but here
are a few points:
The fields MONTH, DAY, and TIME should not be stored, as they can be derived
from other fields. They should not be field names in any case, as they are
reserved words. Hours should be in two fields: StartTime and EndTime. You
should not store names, but rather link to Student and Teacher tables. You
should not have a table for each month, weekday, or other unit of time. A
query can extract the needed information. Access can handle millions of
records, so that is not a problem, but the design is.

You need to be much clearer about what you are trying to do.

 

[daniel]

I am sorry, I did capitals so it is better to read. didn't mean to be rude.
well, this whole thing is such a mess. the company (Musicschool)worked with
excel for the last 5 years.
the datas for the students and teachers are all over place. they have to go
in 10 different
worksheets to look for or change one schedules or do payroll. I have seen
48 worksheets but there is more.
now they grew so much, it just doesn't work anymore.

what I did so far:

1.form: Student Info. account#, student name, address, parents, phones.
(tblStudentInfo)

from there go to 2.Form: Schedules: (done when student enrolls)
(tblSchedules)
studentID(auto number,key), account#, name, day(SDay), time(STime), teacher,
instrument, tuition (for Month or Year),


from form 1 goto Form 3: Reschedule (if student was sick on the lesson day
or could not come otherwise), (tblReschedule)
connects from Form1 or Daily, by studentID or, if there is a sibling which
is learning the same instrument, same hour,
connects with the account# so both can be seen (PgUp-Dn), account# is
filtered.

data for account#, name, instrument is now already in Form 3 (strSQL code.
as in all forms).
combo box for day (type m for monday, tu for tuesday, .., enter.)
calendar pop-up for reschedule day, click date, goes into date field.

to check, which teacher is available on the rescheduled day:

next to the Teachers combo is a button, opens Teachers schedules form
(continuous) and shows:
teacher name, instrument, day, date, free, hour from-to.

to find free teacher: click to filter:
instrument, day, date. (that data is coming from the Daily form/table.)

this will show if a teacher for that instrument is available on that day and
what hour.
if there is more then one teacher, choose one. click on teachers name, will
go into reschedule form.

chkboxes in daily form:
Att = Student Attended, Y/N
MU = Makeup-Reschedule Y/N
CC = Canceled for this day, Y/N
NS = did not come, did not call, No show.
VC = Vacation Y/N (weeks or even 3 months, other student will fill in)
Sb = was student substituted with another student.

This is needed to show, if the scheduled student attended class. If not,
then the teacher is free
in that half hour and he can accept one of his other student if he wants to
come earlier or later.
it always happens.
it also shows if the teacher has worked in that half hour or not which goes
into his payroll (report).

Teacher: 1. did he work that hour Y/N
2. was he free Y/N, meaning, if yes, he did not have any other student
because none could come. he'll get paid for that hour.
3. was it a sub student Y/N (If yes, click chkbox SUB(now true) in daily
form, opens
Form 4: Substituted (connected by studentID),
data for account#, name, instrument are already there, like the others.
almost same form as Reschedule.

other tables: Tuitions, Zipcodes, Instruments, Teachers, A/R, Help.
(monthly payments overdue (10 days)in reports)
all datas from the tables are connected into needed fields in forms (or
combos).


And now, here is my big problem where I am stuck.

the owner wants to see the form Daily, the same way he has it in excel.

Form Daily: (continious)

Year-----2007
Month----JAN
----------------------Teacher--Tim Collins
Week Number 1

DDate----DDay----DHour-----DTime---STdtName---Att--Mu---Cc---Ns---Vc---Teacher----Worked---Free---Sub

02-01----MON---12.00-12.30---30-----JIM
DEW----Y----n----n----n-----n-----TC---------Y-----n-----n
02-01----MON----1.00--1.30---30-----JOE
DOE----n----Y----n----n-----n-----TC---------Y-----n-----n

then Tue, Wen, ...

every day, Mon to Fri, from 12 to 8 pm, different student every half hour
for this teacher.
only the DTime is totaled, then /60 to show the hours worked for the
teacher. usually 8 hrs, into payroll.

then Week Number 2, the same, plus 3 and 4. all for the same month. all 12
months. whole month on the same form.
would have to PgDn for Week 3 and 4.

the week numbers can be default, same with the hours(as Text) and
times(Number), they are always the same.
maybe the Months too.

But:
one form for each teacher, for each month with 4 weeks, 51 teachers * 12 =
612 forms. impossible.

well, something is not working in my brain. I know there is a whole
different solution.
It still will be a lot of data, because of the 'every day'.

I hope I wrote this so it's understandable. I didn't intend it to be so
much.

any great ideas ?

and thank you for the last reply

 

[daniel]

I got it.
don't know why I had that problem. it was simple.
thanks for the help

 

[BruceM]

Glad to hear it worked. Yup, it's a big project with a lot of details. I
looked through the previous posting (with all the details), but was having
trouble sorting it out. I would have needed to ask a number of additional
questions.

 

[daniel]

Thank you, Bruce. yes it is a "medium" project. It's somewhat hard to
explain, one would have to
know the whole story. I just had a "block" in my head. Tsk tsk tsk.
it does happen sometimes.

anyway, maybe you can help me with one other question.

in Delphi, when you do a continious form, you can have certain lines in
different colors.
say you have a chkbox for 'Approved'. now the last name would have a red
background.
if it's not approved, it would stay white.
when you look at it, you would see, maybe 5 rows red, 3 white, then 2 red
again, etc...
I tried this in Access but, either all rows are red or all white.
is there a way to do this the way I needed in Access ?

 

[BruceM]

Conditional formatting should do what you need. Click a text box in design
view, then click Format > Conditional formatting. Use Expression Is in the
leftmost box of the Conditional Formatting dialog, and use something like:
[CheckBoxName] = True
for the expression (substitute the actual check box name), then set the
background color to your choice.
For a more sophisticated approach, see
http://www.lebans.com/formatbycriteria.htm. There is another sample
database at lebans.com that shows how to change the color for alternate
rows. Take a while to browse around the web site while you're there. It is
well worth bookmarking. While looking at newsgroup postings, try following
the links that point the way toward solutions to specific problems. The web
sites often contain a wealth of information beyond the answer to the
question at hand.