Several Questions

[DUNNER7]

I have started my student database and things have progressed quite well so
far ( thanks for all the help from everyone). I have several questions and
maybe someone can direct me to the right forum to get the specific answers
some of the questions have already been asked, but I need further
clarification.

1. If I have a table of 10,000 students, but at any one time I will be
working with about 300 students would the best way of handling this be:
having a table with the 10,000 but include a field "active" with y/n option
and then have the ones selected yes populate a new table that are used in all
of my reports and data keeping?
ex. Tdemographicsall (10,000 students)
Tdemographicsactive (300 students)

2. I have struggled with the attendance part and I am close to a solution,
but need some more suggestions and direction. I want to keep daily
attendance in a table, I structured it as follows:
Tattendance (table name)
taattendanceid (autonumber pk)
tastudentid(linked to demographics table)
tapresent(y/n checkbox)
tadatetime (value (now)
With this structure I am able to have a combo box, but it lists 300 names
and is hard(slow) to scroll down as 250+ kids pass by during a 15 minute
window. If I take the table "demographics" and select Create and then choose
"multiple items" it lists each student and I can delete all of the extra data
until I am left with a list of all student by ID, last, and first name. I
want to be able to click (event) one of those fields, say ID and this event
would cause the value of the fields in the attendance table: tapresent to
change to "yes" and tadatetime to enter as (now) on the click of the ID? The
click would also have to ensure that the ID field on the form corresponded
with the ID field in the attendance table.

Thanks,
Del Dobbs

 

[KARL DEWEY]

1. Include a field "active" but do not make another table. Just include the
"active" field as criteria for all queries used with forms and reports.
2. Tattendance does not need a checkbox when you have the date. The date
should default to Date() and not default to Now(). You might add a field
'Remarks' for stuff like 'late', 'early', etc. for when they were not there
the full day.
Have a unique index of tastudentid and tadatetime.
You will always have more addtending than not except just before a holiday
so why not use the exception for less work. Open form and pick those that
are not present, left early, arrived late, etc.
Once you have pick the absentees, click command button that appends records
to Tattendance for all 'active' except those picked unless they have
'Remarks'.

 

[DUNNER7]

I understand your answer to question #1...thanks. Question #2 has to do with
the actual entering of data into the attendance table. I explained that I
created a form that lists every student's name. I would like to create a
click or double click event that when the name on the form is clicked the
date/time in the attendance table is recorded at the correct time for that
specific student. I agree with your comment about the Y/N, I don't need
it...the time will be sufficient enough to show who is or is not present. I
need a hint on the code to use to click on the name and cause the date/time
in the attendance table to record the time date for the student whose name
was ckicked.

Thanks,
Del

 

[KARL DEWEY]

Open a continous form that has Tdemographicsall as source having added
TmpAbsent (Yes/No) and TmpRemarks (text). Have 'On Click' event that
SetValue Yes in TmpAbsent. Scrool through clicking on those absent. Add
Remarks and necessary.
Command button calls macro to append records to Tattendance for all 'active'
except those picked unless they have 'Remarks'. The macro runs update query
to update TmpAbsent to 'No' and TmpRemarks to Null.

 

[DUNNER7]

Karl,

Thanks for your continued replies, but I don't think you understand what I
am trying to do.

I need to have a record of each student as they enter my doors and the time
that they enter. So as they walk by me - - the event should change the
default of "no" (absent) to "yes" present and the time and date that they
walked in. That is why I want to use the "multiple form" format that lists
all 300 or so names and I can click on the students name when I see them walk
in the door and thus mark them present and the time entered in my attendance
table.

Thanks,
Del Dobbs

 

[KARL DEWEY]

I do not physically see how you will be able to do what you propose.

Do a little math, take the time spread of first student to last entering
divided by 300 to see how much time per student to lookup the names and tick
them off.

I was expecting the Home Room or First Period teacher to present attendance
to you.

If you have some kind of Card Reader or RFID system you could do what you
propose.

 

[John W. Vinson]

Karl,

Thanks for your continued replies, but I don't think you understand what I
am trying to do.

I need to have a record of each student as they enter my doors and the time
that they enter. So as they walk by me - - the event should change the
default of "no" (absent) to "yes" present and the time and date that they
walked in. That is why I want to use the "multiple form" format that lists
all 300 or so names and I can click on the students name when I see them walk
in the door and thus mark them present and the time entered in my attendance

I agree with Karl that this may be really impractical. Just fitting 300 names
on screen at the same time would be a challenge!

What you could do is have (say) six tall thin subforms side by side, each
based on a query extracting some 50 names. You could put code in the
DoubleClick event of the name textbox to set the time field to Now:

Private Sub txtStudentName_DblClick()
Me!txtTimeIn = Now
End Sub

If the names are arranged alphabetically (A-D in the first subform, E-I in the
second, etc. - whatever works best) it should be possible.

 

[DUNNER7]

I thank both of you for your help. I have done it with the combo box last
year. We did it with Google Docs, but were unsure about the level of
security involved that is why I am trying to design this database to put on
our school's secure server. I was able to hit the combo box scroll down to
the name and hit the enter button and attendance/time were recorded on the
table. It was a lot of tedious work and sometimes I missed a person or two.
This year we hope to have this database on two computers so that two people
can simultaneously enter the kids as they come in. I wish I had a swipe
system...I believe in an earlier post I asked if it was possible to generate
a barcode for each name and use a scanner to enter them as they came in.

Using the "multiple form" format in Access I am able to list all the names
and format the names so that they will all appear on one long page and I can
scroll down using the side scroll bars. Depending upon the size of the
monitor I could easily get 50+ names on the screen at one time. It is much
easier to scroll down a page then to scroll down the list of names in a combo
box. We are just about to a solution...thanks again for all of your input.

Del

 

[John Spencer]

Try adapting this to your requirements. Although with 300 students

Sample query to populate a continuous form

SELECT tblStudents.StudentID, tblStudents.LName, tblStudents.FName,
Temp.SID, Temp.AttendDateTime
FROM tblStudents LEFT JOIN
(SELECT tblAttendance.StudentID as SID, tblAttendance.AttendDateTime
FROM tblStudents LEFT JOIN tblAttendance
ON tblStudents.StudentID = tblAttendance.StudentID
WHERE tblStudents.Active = True AND
(tblAttendance.AttendDateTime is Null
OR tblAttendance.AttendDateTime between Date() and Date()+1)) AS Temp
ON tblStudents.StudentID = Temp.SID
WHERE tblStudents.Active = True
ORDER BY tblStudents.LName, tblStudents.FName;


In the detail line of the continuous form add a button with the
following code for the click event

Private Sub btnAssign_Click()
Dim iLastPosition As Long

iLastPosition = Me.SelTop

'Set the datetime field
If IsNull(txtTheDateTime) Then
Me.txtTheDateTime = Now()
DoCmd.RunCommand acCmdSaveRecord

Else 'Reset the record to null, it was clicked by accident
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.Requery
If iLastPosition > 1 Then
Me.SelTop = iLastPosition - 1
End If
Me.SelTop = iLastPosition
Me.btnAssign.SetFocus
End If
End Sub

 

[DUNNER7]

I think that you might have something...I will give it a whirl. I have one
question? What is the Temp.SID and Temp.AttendDateTime are those fields on
an attendance table?

 

[John W. Vinson]

It is much
easier to scroll down a page then to scroll down the list of names in a combo
box.

Do note that it is NOT necessary to scroll down the combo box! If (as you
should) you have Auto Complete set, you can tab into the combo box, type the
first letter or two of the name (the first visible field in the combo), and it
will jump to that name.

 

[John Spencer]

Note that the query contains a subquery which has been assigned the
alias TEMP. In order to use the fields of the subquery, you reference
them with the alias. And yes the query assumes a table tblAttendance
with fields StudentID (aliased as SID in the subquery) and AttendDateTime

You could actually construct the sub-query as a separate query and then
include it in this query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Peter Hibbs]

Del,

I suggest that you use a Flex Grid control to display your student
names on screen (i.e. similar to a spreadsheet display). How many you
can display at one time will, of course, depend on the resolution of
your screen but you should be able to show over 300 names easily at
one time. If you had a grid configuration of say 10 columns and 50
rows you would have 500 cells, the number of columns you could show
would, I suppose, depend on the length of the student names but even 7
columns would still give you 350 names.

Using a Flex Grid control would also give you other advantages which
may be useful. For example, the grid cells for different groups of
students, say students in a particular class or year (or whatever)
could be coloured differently to make it easier to find each student
on the grid. Also, when you click on a cell to mark the student
present, the cell colour could be changed to black (or whatever) to
show that the student has been registered and it could also disable
the cell to prevent accidental multiple entries.

As you click (or double-click) on a grid cell to register the student
is present, the Flex Grid click event would add a new record or update
an existing record (it's not clear to me how you are doing this) in
the table and, if you are using a multi-user system, it could update
the other user's screen at the same time.

I assume you have some method to identify students with the same name
so that every name on the grid is unique which would make it easy to
identify which cell the user clicks on and so update the records for
the correct student.

You would need to write a bit of VBA code to use a Flex Grid control
but it sounds to me as if this would be a better system than the one
you are considering.

If you would like more information on the Flex Grid control just post
back.

HTH

Peter Hibbs.