Before I Begin . . . Again

[Guest]

I created a simple database a few years ago for the nurse in my school. The
design has worked up until now. Other nurses have heard about this and I
have been asked to do something which I cannot figure out how to do.

I have a table, Students, in which the following is stored:
ID: text, key
FirstName: text
LastName: text
Homeroom: text
Fluoride: yes/no

I have a query of the Students table so I can create a list by homeroom of
those who receive fluoride.

In a report, I call the query, enter the homeroom name and have a list of
that homeroom's students who receive fluoride.

Simple and no problems.

Now I am being asked for two other features:
1. the report to include a "spreadsheet-like" grid to be used as a checklist
by the teachers. The idea is that the report will now contain a checkbox for
each week of the school year that the teacher will mark (on paper) whether or
not the student received his fluoride.

2. each year students are promoted to another grade and assigned a different
homeroom. Rather than re-enter all this, I somehow need to get the fluoride
flag in on the new data.

#1. I seem to be inept in creating a report that contains information other
than what is culled from the query. How can I add checkboxes to print on the
report that are not bound to anything?

#2: What I have done up to this point is import a new table of students each
September. This contains many of the same students as last year, but each
has a different homeroom assigned. I have then, because it is easy enough
for me to rip through, added the flag for each student from last year's data.
Obviously, this is not a good way of doing this.

I imagine an update query of some sort would work, but I do not know how to
go about that. Would someone be able to walk me through this process? I am
a beginner without a good handle with Access.

Thank you.

 

[Guest]

I solved #1, kind of. I figured out to get a series of checkboxes in without
the labels. This will work, but isn't quite what I had originally thought I
would do.

Is there any way to un-shade the check box?

I am still interested in knowing if a grid can be created in a report.

 

[Guest]

The more I think about the new school roster each year, the more baffled I
become.

I was going to import the new roster into a different table and then run an
append query to get the fluoride flag from the original Students table. That
didn't work and the more I think about it, the more I don't think it will
solve my problem.

All the queries, reports, and forms are tied to the Students table.
Therefore, I need to work from the Students table each year.

So the question becomes how to overwrite the Students table with the new
students, but not lose the fluoride flag that has already been set.

Apparently, once a family agrees to the administration of fluoride, that
remains with the student throughout his career, unless otherwise stated by
the parents. So, if we set John's fluoride flag this year when he is in
first grade to "Yes", then next year he should already be flagged as "Yes"
when ihe is in second grade.

Mary may also be set to "Yes" this year, but she may not be in our school
next year, so her record would be deleted (backed up to another table?).

Sam does not attend our school this year, but next year he does. He should
have his fluoride flag set to "No" until paperwork comes which states he can
receive it.

I am open to suggestions as to how to make this happen.

 

[Guest]

Well Bob, I can only point you in a direction but you need to create another
table that organizes the room numbers and teachers.

Then you need to look at your current database and the fields that keep
track of flouride treatments.

Using a make table query break out the fields in a new table copying your
primary key field also. in the new table the id field you copied will become
the foriegn key, ad a new autonumber primary key to your new table. This way
you can relate your student records to many fluoride treatments.

If you search "Parameter Query" and "Moving an Expression generated value
into a table through a form" several MVPs walked me step by step through
this process.

I now have a subform on my main employee record which I display as a
datasheet (ie: your spreadsheet display) which I use to keep track of
innoculations. It works great and in the process I got to know access much
better thanks to these guys. I now have a true Realtional database. Good
Luck. Ask more

 

[Guest]

Well Bob, I can only point you in a direction but you need to create another
table that organizes the room numbers and teachers.

I actually have such a table.

Then you need to look at your current database and the fields that keep
track of flouride treatments.

Just one field called 'Fluoride'. I used the lookup wizard and typed in the
values 'Yes' and 'No'.

Using a make table query break out the fields in a new table copying your
primary key field also.

The primary key is the student ID#.

in the new table the id field you copied will become
the foriegn key, ad a new autonumber primary key to your new table. This way
you can relate your student records to many fluoride treatments.

I am not certain about this step. Foreign key?

Ideally, I would like to build in the importing of data at the beginning of
the year. Somehow, other schools in the district heard about this little
database and want to use it. If it goes outside this building, I cannot
manage the project so everything will need to be automatic without me fudging
it along as I do now.

If you search "Parameter Query" and "Moving an Expression generated value
into a table through a form" several MVPs walked me step by step through
this process.

I've been looking but I haven't figured anything out yet. I did find your
little edit to close Access completely from the switchboard. That was
helpful.

As for parameter queries, I get lost in what I am reading.

If I were maintaining this database, I would update the homeroom field for
each student each year and the fluoride flag would remain. But this database
is kept by someone else who just uses the data, but does not really do
anything other than set the fluoride flags.

 

[Guest]

Okay, I think I have a workable solution. I have broken the students table
into two separate tables. One holds the fluoride information, the other the
student data.

I'll start another thread for a question I have about updating the student
data.

Thanks for the help!

 

[Guest]

You really should stay in one thread to see your entire solution through to
the end...I made the same mistake. I really think you need to keep all your
students in the same database...you have a condition of annual change is
all...not unlike other things. at some point everyone "graduates" to another
level...I think it would be better to work on organizing what you have rather
than creating work for yourself every year when there are graduates and
newcomers....get the new comers in then move everyone based on "time" (read
dates) through their levels of school.

 

[Guest]

You really should stay in one thread to see your entire solution through to
the end...I made the same mistake.

My new issue though is something totally different from this one.

I really think you need to keep all your
students in the same database...you have a condition of annual change is
all...not unlike other things.

What happens is each August I receive a dump from the district database of
data about our students. I use this data to seed various databases I keep
(most are software-specific). I have taken to using it for the nurse's
fluoride database too.

It seems to me that is a better design to keep the student data in one table
and the fluoride flag in another. At the beginning of the year, I can
overwrite the student data (no need for this project to keep the old data).
I still will have the fluoride flag from this year. Then all the nurse has
to do is turn the flag on for any new student.

Over time the fluoride table will grow and even have a few values set to
"No", but the student data will change yearly. That seemed to be the most
straightforward manner in which to keep the fluoride tag for students,
graduate those who go, and enter the new ones.

The three new issues are:
1. On the data entry form, how to create a combo box which will return
values from multiple tables (use the ID# to return the data from the Students
table and the fluoride flag from the Fluoride table).

2. Figure out how to create a switchboard item for the nurse to purge the
data from the Students table at the end of the school year.

3. Figure out how to create a switchboard item for the nurse to import a
text file to populate the Students table at the beginning of the year.

It would seem to me that it might be easier to tackle each of these
separately in their own thread, but I am more than willing to handle it here.

Any thoughts?

I do appreciate your help!

at some point everyone "graduates" to another

 

[Guest]

I suppose my new way may have an issue.

I can't call up through a form the "new" imported data linked to the
Fluoride table as there is no corresponing record for any new student or any
student who wasn't already flagged "Yes".

Sigh . . .

 

[Mike Painter]

I created a simple database a few years ago for the nurse in my
school. The design has worked up until now. Other nurses have heard
about this and I have been asked to do something which I cannot
figure out how to do.

I have a table, Students, in which the following is stored:
ID: text, key
FirstName: text
LastName: text
Homeroom: text
Fluoride: yes/no

I have a query of the Students table so I can create a list by
homeroom of those who receive fluoride.

In a report, I call the query, enter the homeroom name and have a
list of that homeroom's students who receive fluoride.

Simple and no problems.

Now I am being asked for two other features:
1. the report to include a "spreadsheet-like" grid to be used as a
checklist by the teachers. The idea is that the report will now
contain a checkbox for each week of the school year that the teacher
will mark (on paper) whether or not the student received his fluoride.

Unless you are charging for this and they are willing to pay or you want a
good exercise in programming I'd say *NO* in Access.
It requires a flat file to present the grid, then code to link the position
in the grid to the relational record. Mine was an attendance record and
presented a calendar view with holidays marked off and the correct number of
days in the month showing.

Your application can be done in Excel but you will have a lot of problems
with reporting.

AND I just reread what you said. If they are doing this on paper then
someone could enter the data into a form using properly related tables.
This would probably be just a form/sumform problem.

2. each year students are promoted to another grade and assigned a
different homeroom. Rather than re-enter all this, I somehow need to
get the fluoride flag in on the new data.

You need a "Fluoride" Table with FKey, StudentID, Date, and yes/no field.

#1. I seem to be inept in creating a report that contains information
other than what is culled from the query. How can I add checkboxes
to print on the report that are not bound to anything?

If you mean empty checkboxes just draw them on.

#2: What I have done up to this point is import a new table of
students each September. This contains many of the same students as
last year, but each has a different homeroom assigned. I have then,
because it is easy enough for me to rip through, added the flag for
each student from last year's data. Obviously, this is not a good way
of doing this.

Homerooms have students so you need a homeroom table and the student table
needs the homeroom ID.
You probably will have to update by hand but if all students in Homeroom 101
move to homeroom 201 an update query will work.

 

[Guest]

You need a "Fluoride" Table with FKey, StudentID, Date, and yes/no field.

Homerooms have students so you need a homeroom table and the student table
needs the homeroom ID.
You probably will have to update by hand but if all students in Homeroom 101
move to homeroom 201 an update query will work.

I am beginning to see the issue more clearly.

Ideally, the nurse would update the homeroom year to year. The fact of the
matter is that that is not going to happen. My hope is to create the ability
to mass import the new student information yearly with the new homeroom
assignment and preserve the fluoride flag that was already entered. The
problem remains: how to do this in the best manner.

I keep thinking that if I could import the new student info to a new table
and then run a query to find any fluoride flags that might exist in the "old"
data, a query could be run to update the "new" data. That still seems clunky.

I am looking for a way to manage data when there isn't someone there to do
so and that is what is proving problematic.

 

[Guest]

Hmmm . . . if in the nurse's database she had a table called Fluoride in
which she stored:
StudentID#:key
Fluoride: yes/no

The StudentID# could be linked to a table in a master school database which
I keep on a network drive. I could have a table called Nurse, for instance
that I could keep populated with current students. Should a new student
arrive, the nurse would just enter the ID# which I would already have the
information on.

Hey, I think this should work! I don't know what the other schools would do
unless they too kept a school database somewhere, but that would solve our
internal issue I think.

 

[Mike Painter]

Bob wrote:

 

[Guest]

And this is pretty much what I did. I tweaked the form for her a bit, but
that's how I solved it in my school.

Then I gutted the database and built a student table inside the fluoride
database for other schools. I am letting them know they can either manage
the student data here or link it to a data source which they keep. This is
such a small project, the few items which would need to be modified would
take no time. I figure if they are keeping data elsewhere, they can modify
the query. If not, then they can manage it from what I built.

Thanks everyone for the help!