Hi Weiers,
From reading all the things you want to accomplish I think you should
consider using a database of some sort. You're correct that managing a
very wide spreadsheet is difficult.
What computer infrastructure is available to you? Do all the students
and staff have access to a computer on the Internet or at least a campus
wide LAN/WAN? The reason I ask is because, for me the hardest part of
maintaining the data in a system is the data entry. If I have a paper
form that someone fills out then I have to retype it into my system. My
solution has always been to use a web based system where everybody can
update their own data. In addition, I automatically send them email if
they have some new piece of data that they must enter. If you have the
basic infrastructure already in place then that's half the battle.
So if you have the basic infrastructure then you could have each student
log into the system and fill out the required form on-line. If a staff
approval is required for something then the staff member could log into
the same system and indicate their approval or rejection. If the
cafeteria wanted to know how many students are registered in the dorm
they they could log in and go to a report page. All these things would
then be updated in real time. With only 400 students and 70 staff I
think a small server would be sufficient.
The problem with access control is getting folks to use it. Are the
smart cards you referred to remotely sensed without user action? If so
then I think that would work. If not then getting folks to take some
action like swiping a card through a reader is difficult. Even if you
have electric door locks with access cards, people often "tailgate" in.
Tailgating is the term used when one person triggers the door lock with
their access device and another person follows them in before the door
shuts. Of course for safety, everybody can leave without using any
access device so you never know when someone leaves.
I guess we've gone a bit off of the OpenOffice subject of this group.
You can email me directly if you wish or we can keep working through
this group until somebody complains. I don't want to ware out my welcome
here. My obfuscated email address is below.
Peace,
Rob:-]
rshaver AT austin DOT rr DOT com
Weiers Coetser wrote:
Robert Shaver wrote:
ADVICE .............their mail follows below (I cannot quite figure
out this list's preference for either top posting or bottom posting)
Hi Robert and Peter...
Thank you very much for the insights... I find them extremely useful.
I work at a small private College. We have about 400 students and 70
staff. I am in the men's residence.
My experimentation with the data-management is driven by several needs...
1. I want to learn how to get the most out of programmes like calc,
base, plone... whatever. I find it extremely interesting, so I do not
mind if it is sometimes a slightly more difficult route.
2. My information management has until recently (the day before
yesterday) been a purely paper-based system. I photocopied a
registration form for the students and they filled it in and I placed
it in a file. The problem is I just have no motivation to go and open
that file and find out when the student's birthday is, or what work
his parents does ... unless the student lands in serious trouble. Then
it is sometimes too late ... a month ago I had to take a student to
the doctor and only then did I realise that I had no information on
his medical insurance - he just did not fill it in, and I never
checked that I had received it. Students have to declare their
electronic equipment (some or other health department regulation)...
I've never really been able to control the information that they gave
me (I am not sure that having the information electronically will give
me much more control). When a student leaves the dormitory he fills in
a form, which I quickly misplace, and one day somebody is looking for
the student and I have no idea where he is.... Or what if I want to
contact the student in 10 years to donate a laptop or a video
projector to the dorm... then I have nothing. I think I am therefore
right to decide to go electronically at least.
3. From a larger perspective, I have a slightly grandiose dream to
begin developing a system where different departments in the College
can share information in stead of duplicating things. A student who
wants to register in the dormitory currently needs to run around to
get signatures proving that he is accepted academically at the
college, then he needs to prove that his finances are sorted out
(another signature), then he needs to register at the cafetaria
(another signature) ... At the end of it all he brings the form to me
and then at the end of the day the Cafetaria still calls me to find
out how many students I have in my dormitory (They never kept track of
the signatures they signed)... and I am still required to submit a
list of students in my dorm to the finance office, even though they
signed for them to be accepted. There is terrible duplication of
data. ------ So somewhere I need to begin developing skills in
datamanagement in order to at least make positive suggestions.
---------------OK... so I am writing too much now and probably
irrelevant to you.
What are my main needs at the moment:
1. I want to be able to access information about the student easily:
If I want to call/e-mail/visit the student - how will I do that.
In the same vain I want to be able to know what to do in the case
of an emergency. Should he go to a government hospital or private
hospital. Who will foot the bill etc.
(I agree that this is probably best captured in a simple spreadsheet -
very little data gets manipulated. What I did yesterday and today was
to design a registration form in Writer and inserted the spreadsheet
fields. I mailmerged the fields together and printed out
individualised forms for each student to check and correct where
necessary - much better than asking them to write it out all over
again each semester)
2. Since I need to provide leadership in the dormitory, and keep the
students happy, and guide/mentor them... I would like to be able to
gather and manipulate other forms of data. Simplest is to print a list
of birthdays for each month. Slightly more complicated is to develop a
"citizenship barometor" in which I try to make regular observations
about certain issues to try to get an objective picture of what is
happening: Here is some of what I have in mind: I want to give myself
(and my assistants) a score on some of the following questions: Do we
know this student (sometimes we can't put a face to the name). Does
the student attend dorm socials? Does the student attend common
meetings? If he attends - how regularly? Does he contribute to the
meetings? Do we see him at religious functions? Have we had any
conflicts with him? Are there issues related to discipline.
By manipulating the figures that I get from this I should be able to
analyse who the key players are in the dormitory. I can then identify
their strengths and focus on building their capacity to make an even
greated contribution to the dorm. I will be able to see those who just
do not fit in and then I can take various approaches to evaluate what
the problem is. Is the student just quiet? Is he unhappy about
something? Does he have a problem. ----------- I am not sure if a
"quantitative" analysis like this will work... perhaps I should use my
intuition. After all, I can find out which students are slippign
through the cracks without a big formal analysis. But who knows ... it
might give me some tools to address an issue ... and to measure
progress, that I don't currently have.
3. There are some control issues: I need to be able to give an account
of who is in my dormitory at a given time. If a student leaves for a
week, I should be able to know, and trace him. When students leave for
the vacation the cafetaria calls almost on an hourly basis to find out
how many students are in the dorm. I have been getting quotes for
smart-card readers to install at the dorm entrance. In this way I can
regulate access to registered dorm students. I will also to an extent
be able to monitor their movements.
If a student withdraws from the dormitory, I would like to at least
have a record of when he left and more or less where he went to.
4. I would like to be able to look at the issue from the perspective
of the physical building. Which rooms have which students in them. If
I check a room during the holiday and I find the furniture missing or
vandalised, I want to be able to trace who lived in that room and for
what period of time.
5. I often need to generate a list (sometimes the chaplain wants to
know which rooms the students are in, othertimes we need students to
tick of if they are going on a college outing, sometimes a sports
co-ordinator wants to set up teams - first year students playing
against second years etc.) A week before the holiday students are
sometimes asked to indicate who will be leaving, and when they will be
leaving etc.
It is here that I sometimes find the full table with 60 or 70 fields
very cumbersome to work with.
-------------------------
I don't know if this is of any interest to anybody else... but if you
have some insights to give on how I could best manage the data, I
would be very grateful.
Thanks Weiers
Hi Weiers,
ADVICE
As you have discovered, creating a complex spreadsheet application
can be complex and error prone. Most people don't realize that they
are developing software when they are using formulas in a
spreadsheet, but they are. One of the most important decisions in any
software development project is choosing the right tools for the job.
The wrong tools can make a good outcome unlikely.
The first step in developing a new project is requirements. If your
project is easy then the requirements will be easy to write down. So
write them down. You might be surprised at how much this will help
you clarify what you are trying to do. Once you have written down the
requirements, ask yourself this question about each one; "If the
system didn't have this feature would the project still be worth
doing?" If the answer is yes then leave that feature for later.
Implement only the absolute bare essential features first and get
something that works sooner.
MY OPINION
Unless you can reduce the problem to a single sheet, I don't think
it's the right tool for the job. I have done a few multi-sheet
spreadsheets that interact and they were hard to get working and
harder to keep working.
Now some specific questions about what you are doing
QUESTIONS
1. Why do you need three sheets? Why not just add columns to the
first sheet? If you limit yourself to one sheet then you won't have
to worry about having to update the same values in multiple sheets.
(This is where some development experience can help. You're running
into issues that have been around for a long time and have been
solved in many different ways. Each solution has advantages and
disadvantages.)
2. Have you considered a simple manual system? It's quite amazing
what you can do with a graphite text editor; paper, pencil and
eraser. Computers don't always make things easier.
I have created literally dozens of systems just like this using Lotus
Notes. Mostly this is because I have used Notes on a daily basis for
the last eight years, developed large and complex systems with it and
I know that it is ideal for small projects like this as well. The
closes open source systems to Notes is Zope or Plone (which is built
on Plone). I haven't used either one so I can't swear to it, but what
I've read makes them look like good candidates. The setup and
learning curve would be steep, however.
I hope this is helpful. Most of it is just my opinion after about 25
years of software development. YMMV
Peace, Love, Laughter,
Rob:-]
Weiers Coetser wrote:
Hi
Please send me in the right direction if my question is off topic. I
am new to spread-sheets in general and need some advice:
I am in the process of setting up a rather sizeable spreadsheet
(that should probably become a fully fledged database) to manage the
information I have for students who live in my dormitory. (I am the
dormitory dean).
The First Sheet contains all the names of the students, contact
details, parents information, medical insurance information, postal
addresses etc. It has over 40 columns.
The second Sheet contains the names of these students and their room
placements.
The third sheet contains the anmes of the students and some
citizenship records.
At the moment I am just copying the Names and Surnames from Sheet
one and Pasting them into sheet 2 and 3.
The problem is that if a new student comes I have to update all
three sheets by hand. If one name is spelled incorrectly, I have to
physically go and spell all three differently.
Is there a way that I can link the three sheets so that when I add a
new student or when I update information for a student, all the
relevant columns in the other sheets are updated?
I thank you for giving me some advice. (Probably I should learn how
to work MySQL and programme PHP to create queries... but I am not a
developer and it will take months to learn to do that.)
Thanks
Weiers
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
