Attendance sheet (checkboxes? poor database design?) 2

[hkaing79]

I'm trying to create an attendance database. However, instead of asking a user to find a person's name and enter the date he/she attended, I would like to create a form that asks for a date, then populates people's name, and include a checkbox.

Any ideas on how to go about creating a form kind of like an attendance sheet?

Currently I have two tables:
Teacher Info
TeacherID | FirstName | LastName | School | Active

Attendance
TeacherID | Date

I read through some checkbox threads and it says I need to bound the checkbox to a field. So, I was thinking about adding a checkbox field to Attendance table but that would make the table really big. If possible, I would only like to include dates attended.

Any design help would be greatly appreciated...or links to other threads or websites. Thank you.

 

[mik18]

Why not have use a combo box? You can enter a date on a main form. Then use a tabular subform to select teacher names. Build a combo box that uses the TeacherID as the bound column and then use FirstName & " " & LastName as the second column.

mike

 

[willir]

hkaing79

tblCourse
CourseCode - primary key
CourseName

tblStudent
StudentID - primary key
StudentLN - last name
StudentFN - first name
..etc

tblRoaster
CourseCode - foreign key to Course table
StudentID - foreign key to Student table

primary key is CourseCode + StudentID

tblAttendence
AttendenceID - primary key
CourseCode
TeacherID
AttndDate (do not use reserverd word "date"

tblAttendDetail
AttendenceID - foreign key to Attendence table
StudentID - foreign key to Student table
Present - Yes / No field

Foreign key is AttendenceID + StudentID

Richard

 

[hkaing79]

mik18

Does that mean I will need to create an additional field in Attendance? I'll look more into combo boxes.

willir

Sorry, I should have clarified. This attendance sheet is for teacher training, not classroom. I created a primary key on TeacherID.

 

[willir]

Okay, answer me this...

How will you know when a teacher is on training, and what course are they taking -- are these details important?

 

[hkaing79]

The details aren't important. I just need to keep track of which teachers are attending these workshops. I was hoping to use checkboxes because it beats manually entering the same date over and over again. So I just want to enter one date and click on the teacher's name that attended.

I looked into checkboxes, but I think listboxes are more promising. I think I can write a function to append the names of the selected teachers with the input date, but I'm not quite sure how I would remove the teachers if he/she was deselected.

I'm a bit confused about the AfterUpdate function. Is the AfterUpdate function kind of like editing in real time, as opposed to making your selection and then clicking on an "Update" button?

I'm thinking I can use a function that will add a record if selected and delete a record if deselected using the AfterUpdate. But that would mean I would have to autoselect the teachers that are already listed with the input date. Is this feasible?

Pseudocode:
TList = select * from Attendance where AttDate = InputDate
for i=1 to # of teachers
if teacher is found in TList then
ListBox .selected = true
end if
next i

On AfterUpdate:
if ListBox.selected = true then
Append Teacher and input date
Else Find record and delete (is this possible?)
end if

As you can tell, I'm lacking in VB skills.

 

[willir]

hkaing79

... if teacher is found in TList then ...

The problem with your pseudocode is a) you have to "find" records, and therefore the record has to exist; b) you state you are a tad weak with VBA.

All right, you want something simple.

Instead of a check box, you just need the TeacherID and a date field - if there is a date, then the Teacher was at a workshop on this date.

tblTeacher
TeacherID - primary key
TeacherLN
TeacherFN
School
Active

tblOnCourse
TeacherID
CourseDate

To prevent duplicate entries, make TeacherID + CourseDate the primary key.

...Hmmm, but then you would have to select a date for each entry.

You want to just select teacher for a specific date / workshop. I assume you have numerous teachers who will be On Course, so you can use a "header record" that defines the date, and any specifics, i.e., the date, you want for the Workshop.

tblWorkShop
WorkShopID
CourseDate
...ah heck, it is just one more field...
WorkShop

Now we have to go back to the OnCourse table. Since the date is already part of the header table...

tblOnCourse
TeacherID - foreign key
WorkShopID - foreign key

To prevent duplicate entries, make TeacherID + WorkShopID the primary key.

Presentation
Now, you want to greatly minimize data entry.

Make sure you establish your relationships. From the menu, select "Tools" -> "Relationships". Add all three of your tables. Then establish your two relationships. Click on TeacherID on the Teacher table and drag it to the TeacherID for the ONCourse table. Repeate for the WorkShopID.

Create a contineous / tablular form based on tblOnCourse. Make the WorkShipID field very, very small, and make it invisible. With the OnCourse form open, make sure the Properties window is open. (From the menu, select "Veiw" -> "Properties"). Select your WorkShopID field, select the "Format" tab on the properties window, and set "Visible" to "no".

Next, you want to change the TeacherID field to a combo box. Select the TeacherID field, then from the menu, select "Format" -> "Change To" -> "Combo Box". Now select the "Data" tab on the "Properties" window, and select the "RowSource" field. Click on the command button to the right with the "..." icon to invoke the "Query Builder". Add your tblTeacher table to the query builder. Double click to add the fields - TeacherID, TeacherLN, TeacherFN, and if you want, School. Close the table selector. Now for the TeacherFN column, select the "Sort" field, and pick "Ascending". Repeat for TeacherFN. This will control the sort order for the "pick list" that will be used by the combo box. Close the query builder and save.

The bound column will be column 1 - TeacherID.

Now select the "Format" tab of the "Properties" window, and select the field ColumnsWidths. If you are going to use only three columns, TeacherID, TeacherLN, TeacherFN, set the following fields...
Column Count: 3
Column Widths:[tt]0";1";1"[/tt]
List width: 2"

If you included School in the query builder, use...
Column Count: 4
Column Widths:[tt]0";1";1";1.5"[/tt]
List width: 3.5"

Close the form and save.

Create main form based on tblWorkShop. (I am going to assume you will have create a form, or import the Teacher information.)

With the WorkShop form open in design mode, click on the OnCourse in the database window and drag and drop it on to the WorkShop form. You now have a main form + subform. Because you established your relationships, Access will link the forms for you.

Tweak the form designs as required.

Open the form for WorkShop. Fill in the prelimanary info for the WorkShop, and go to the subform to the OnCourse. You will note as you type a Teacher's last name, the combo box will select the first match. You will hit most teacher after 3 or 5 characters. Hint: ALT+DownArrow open the list box.

As an FYI, although you have a fairly easy way of tracking teachers OnCourse, you can copy and tweak the OnCourse subform to display the course and hide the teacher info. Then add it to a main form based on the Teacher table -- now you can see what dates a specific teacher was OnCourse.

Yea, I know you wanted to create a list of all teachers and then select only the ones OnCourse. You would still hvae a fair bit of work, and a lot of coding. This way, you have created a database with minmal amount of code which would be easier for you to maintain.

Richard

 

[hkaing79]

Wow. Thanks Richard.

I guess the biggest hurdle was I had too few tables? I was hoping to get away with just Attendance and Teacher table, but now I realize that I needed a third table to keep track of those dates. I didn't think it was important because I was hoping to create those dates on the fly, but it does make sense that I should control for these dates.

My primary task was only to track attendance regardless of workshop, but already you got me started with extra features.

I'm beginning to get a clearer picture now. Thanks for guiding me in the right direction.

There's over 400 teachers, so if 399 of them attend, that's gonna take awhile. So I'm gonna try to use an extended listbox?

 

[willir]

Hmmmm

First, thanks for the star.

What you can do, later, is develope a few command buttons that add all teachers, or all teachers for a school, or all for a department, or all for a department at a school. Then you would delete those not going.

Another approach I used, and it is much more complicated is to have two forms. The one form is an extended list with sort capabilities. End user double clicks on an entry and it appears in the OnCourse subform for the selected item.

...Save this stuff for later. I suggest you get the basics down first.

Richard

 

[hkaing79]

Actually, that approach is what I'm trying to do right now. I didn't know you had to create a subform. The example I was looking at created two listboxes.

There were four codes: Select All, Select Item, Deselect All, and Deselect item.

I've been trying to find that site again, but can't seem to do so.

Sigh, the headaches of user friendly interfaces.

 

[hkaing79]

So I got the Form to display listbox of teachers called lstSelect and a listbox of teachers that attended a workshop called lstTAttended. I'm using an unbound combobox called SelDate to select the date of the workshop.

The problem is I can't add the selected item. I tried using DoCmd.RunSQL to insert the lstSelect.Selected(0) and lstSelect.Selected(1) which contains the two fields I want to add (or does 0 and 1 refer to the record rather than the field? it's a 5 column listbox of which I just want the first two).

I also tried using DAO but I read that you can't make changes to a table using DAO?

What method should I be using to add the selected item in lstSelect to lstTAttended?

-hk

 

[willir]

hkaing79

You seem to have made good progress. But let's run a quick check list...

1) Have you created your relationships?
(Menu, "Tools" -> "Relationships", add your tables, drag primary keys to foreign keys, and check "enforce referential integrity" when prompted)

2) Did you create a tabular / contineous form based on tblOnCourse? This will be used as a subform.

3) Did you create a regular form based on tblWorkShop?

4) Did you drag the tabular form based on tblOnCourse on to the main based on tblWorkShop?

If you have done the aforementioned steps, after you create a record on the Main form based on tblWorkShop, you should then be able to created your records for OnCourse. (Which I suspect you may be calling Attend ??)

This seems to be a little different than...

So I got the Form to display listbox of teachers called lstSelect and a listbox of teachers that attended a workshop called lstTAttended.

Before you get your lstSelect to work, I suggest that you need to get the Main form / Subform working.

Richard

 

[hkaing79]

1. I created the relationships.

2. Is creating form/subform necessary? I assumed subform was necessary only for the purposes of using combo box in tabular/continuous form. But since I went with listbox, I thought I could get away with working just in the main form.

3. At first I started using Bound objects but when I did, the workshop dates kept on changing on me. So I started using all unbound objects. I used rowsource WSDate for SelDate, and I used a function on AfterUpdate of SelDate with the following (simplified version):

Private Sub SelDate_AfterUpdate()
'Populates lstTSelect and lstTAttended

lstTAttended.RowSource = "SELECT tblAttendance.WorkshopID, tblAttendance.TeacherID, tblWorkshop.WSDate, School, TName FROM tblWorkshop [JOIN Syntax] WHERE tblWorkshop.WSDate = SelDate [ORDER syntax];"

lstTSelect.RowSource = "SELECT School, TName, tblWorkshop.WSDate FROM tblWorkshop [JOIN syntax] WHERE tblWorkshop.WSDate <> SelDate or tblWorkshop.WSDate is null [ORDER syntax];"

End Sub

Is this the same thing as creating a bound object? I'm not familiar with bound/unbound objects, only queries and reports.

4. I followed your steps with the combo box, but I'm like retarded or something. I got the combo box to save the TeacherID but not the WorkshopID. I finally gave up and proceeded to your advanced suggestion. Ha ha.

So, I went ahead and used your two listbox idea. I got the displaying to work. Now for the saving.

I've tried:

Private Sub lstTSelect_DblClick(Cancel As Integer)
'Insert Item to tblWorkshop

DoCmd.RunSQL = "INSERT INTO tblAttendance (WorkshopID, TeacherID) VALUES (lstWorkshopID.Selected(0), lstWorkshopID.Selected(1))"

End Sub

But I got an "Argument not optional" error with the RunSQL.

So I tried using DAO:
Private Sub lstTSelect_DblClick(Cancel As Integer)
'Add Item to tblWorkshop

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblAttendance"
Set db = CurrentDb()
Set rs = db.openrecordset(strSQL)

rs.AddNew
rs("WorkshopID") = lstTAttended.Selected(0)
rs("TeacherID") = lstTAttended.Selected(1)
rs.Update

rs.Close
db.Close

End Sub

But this seems to have done nothing. I'm not sure if I'm using the ListBox.Selected(0) correctly since it's multicolumned. Should I be using ListBox.Column(0) instead?

Thanks for putting in so much time to help me out.

 

[hkaing79]

Correction: Using DAO does do something. It adds a new record but they're all blanks.

 

[hkaing79]

Correction: I see my error. Using updated code:

rs.AddNew
rs("WorkshopID") = SelDate.Column(1, 0)
rs("TeacherID") = lstTSelect.Column(0, 0)
rs.Update

I made the SelDate combo box two column (WSDate, WorkshopID).

Now, it's adding in teachers, but regardless of which teacher I select, it's giving me the first row, rather than the first row selected.

So close!

 

[hkaing79]

WOOHOO! Got it to work.

rs.AddNew
rs("WorkshopID") = SelDate.Column(1)
rs("TeacherID") = lstTSelect.Column(0)
rs.Update

Thanks Richard and other Tek-Tips threads.

 

[willir]

Actually, you did a great job trouble shooting and problem solving.

 

[hkaing79]

I read through a lot of threads, most of which you've posted some sort of reply. Do you this on a full-time basis?

I just found an error in lstTSelect:

"SELECT DISTINCT tblTeacher.TeacherID, SchoolName([SchoolNum]) AS School, [TLast] & ', ' & [TFirst] AS TName, tblAttendance.WorkshopID FROM tblTeacher LEFT JOIN tblAttendance ON tblTeacher.TeacherID = tblAttendance.TeacherID WHERE tblTeacher.TeacherID NOT IN (Select TeacherID FROM tblAttendance WHERE workshopID = " & SelDate.Column(1) & ") ORDER BY SchoolName([SchoolNum]), [TLast] & ', ' & [TFirst];"

I'm trying to display only the teachers that did not attend in lstTSelect. So a Teacher should only appear in one of the listboxes.

Currently, if a Teacher attends Workshop 1 and 2 and I select Workshop 1 or 2, it works fine. But when I select Workshop 3 or 4, there are duplicates. Shouldn't DISTINCT take care of that?

 

[willir]

hkaing79

No, just very part-time.
Just some itellectual stimulation while I do laundry, etc -- more engaging than reality TV, and most of the junk TV shows. I am no where near as prolific as PHV, Roy or the Cajuin, and others. But I do like design...

SELECT DISTINCT tblTeacher.TeacherID, SchoolName([SchoolNum]) AS School, [TLast] & ', ' & [TFirst] AS TName, [COLOR=blue]tblAttendance.WorkshopID [/color]

Since you included WorkShopID, plus the name and school, it will display each workshop attended.

Of course, you temper this with the NOT IN clause, but you are using a LEFT OUTER join which means it will find records on the ONE-Side of the 1:M whether it matches or is null (no match) on the MANY-Side.

This means that you will see John Smith even though he did attend WorkShop 3 & 4.

Richard

 

[hkaing79]

Yea, I see PHV in a lot of threads.

Sigh. You make it sound so easy. I took off WorkshopID and it works beautifully.

Thanks for all your help. I can't believe you're able to troubleshoot without seeing the actual database. That's crazy.