Design question

[Seth Schrock]

I'm trying to create a system to track attendance and I keep running in circles trying to figure out how to design it properly. Basically, what I'm wanting is a form with a textbox on top (txtAttendanceD ate, will explain later) with a complete listing of families in my database (in tblFamily) with a checkbox next to it in datasheet view. So far I'm fine. Next, I want to be able to select the date of the event for which I'm tracking attendance in txtAttendanceDa te and then select the families which attended by clicking on the checkbox. I will then want to pull a report that lists the families that have not attended an event in the past six weeks

My problem is getting all of this information into the attendance table. My original thought was to use an append query that would select all of the families whose checkbox was true and place the family ID into the attendance table and then use value in the txtAttendanceDa te to populate the AttendanceDate field in the attendance table, but the date didn't go over.
Another problem with this design is that I will have a whole bunch of records in the attendance table for some families, each with a different date. How would I query for each family and only pull those that don't have a newer date? For example, if I have two records for one family one dated 1/1/10 and one dated 10/11/12, a query with a criteria of <8/30/12 would list the family because they have a record dated 1/1/10. So I'm trying to come up with a design that will solve both the entry problem and the report problem.

Here is my current design:

tblFamily
FamilyID, PK, AutoNumber
Family info...
Attended, Yes/No

tblAttendance
AttendanceID, PK, AutoNumber
FamilyID, Number, foreign key relationship
DateAttended, Date

[zmbd]

Changing the thoughts....... ........
Just a second while I find that silly DB I had..........

[Rabbit]

1) We would need to see the code or query that you're using to insert into the attendance table.

2) For the query, you can select max date grouping by family.

[zmbd]

Simple Tracker

Seth,
Attached is a database that I've stripped way down to remove the confidential information and I've added a few dummy records.

I have three tables, one for families, one for retreats, and a final for attendance. Families and Retreats are one to many against the attendance and attendance has an additional index to prevent entering the same family against the same event.

I've stripped out the other tables such as location etc; thus, the database isn't truly normalized

There is a "data entry" query.... this query takes the attendance table and I've made a few look-ups. I have no issues with look-ups in queries - just tables. This makes it easy to see where and who.

There are then three other queries that should be obvious from their names as to what they do.

This is a database I made for myself, no supper fancy reports, no fancy forms, I did have a few simple reports for "form-letters" however, those broke when I took out the family details ;)

Take a look, see what you think. With only a little bit of effort you could add in the table for locations the family details (I actually have a table of families and then a table with individuals so that I can track by family and by individual family members.... but I stripped that table too :) )

[Seth Schrock]

@Rabbit
Well, I had deleted the query because it didn't work, so I had to recreate it (it still doesn't populate the DateAttended Field).

INSERT INTO tblAttendance ( FamilyID, DateAttended )
SELECT tblFamily.FamilyID
, (Forms!frmAttendance!txtAttendanceDate) AS DateAttended
FROM tblFamily
WHERE tblFamily.Attended = True

@Z
The events that I'm dealing with are Sunday church services. I thought that I could bypass the event table (meaning less to type in) by just using the date field in what would be the join table if I had the event table.

Also, your database doesn't have the method of data entry that I'm looking for. The reason I'm trying to do it with the check boxes is that the way that attendance is going to be tracked is that the pastor will sit down on Monday and go through the list of people and try to remember if they were there the day before. If they were, then they get a check in the checkbox. It is easier to do this when you can see everyone without having to click on the dropdown box and scroll through everyone.

[twinnyfo]

Seth,

I agree with Z that you need three tables. One for events, one for families (I would exclude the Yes/No field) and one for attendance. The attendance table only needs the event date and the pk of the family table.

For input, this is a change of direction, but one I have used successfully for many years. On the main form, have two subforms, one for current attendees for the date, which is based off the attendance table, and then another subform, listing non-attendees, which is based on the families table, but built on a query which only lists families not currently attending the selected date's event. In the subform, add a command button that simply appends the familyID to the attendance table with the selected date. That should be relatively simple code. Requery the subforms, and there you go.

For the report, I'll have to think about tht for a while, but I know it can be done. My initial thought is to query a list of those who HAVE attended within the last x number of weeks, and then join that query to the families table, looking for null values, which will produce a list of families that have not attended....

I'm thinking out loud on my iPad instead of my pc, so I don't have access to trying this out.

Hope this points in the right direction.....

[Rabbit]

That query should work, what happens when you do it as just a select query? What does it show? Are there errors?

[zmbd]

I suspected that the data entry might not be what you were after; however, what about the remaining queries?

Funny... did you catch the title of the database? I had meant to change it...

Give me a minute and I'll mock you a data entry along the lines I believe you're after.

Friday, no school, and it's my day off... so I'm playing supper Daddy right now!

[Seth Schrock]

@Rabbit
It shows the fields that I want, but only the FamilyID has anything in it. The DateAttended field is blank even though the txtDateAttended has 10/11/12 in it. There are no errors.

@Z
The queries look good. I would probably combine qry_families_Th at_Have_No_Atte ndance and qry_families_wo _attendance_pas t6mo, but I believe that I can do that. I really appreciate you creating a sample database.

@Twinnyfo
Normally, that is exactly how I do many-to-many relationships and I think that it works best for most situations and certainly is easier to design. However, in this case, I think that it makes the user work much harder, so I'm willing to spend the extra effort in designing it so that the user doesn't have to work as hard. However, because I have never done it this way, I'm having a hard time figuring it out.

[Rabbit]

Is it txtAttendanceDa te or txtDateAttended ? Your query shows one thing but you say another.

[Seth Schrock]

Sorry, txtAttendanceDa te is the correct one. I just got confused with the field DateAttended. I have verified that the query matches the control name and still no luck.

[Rabbit]

Is the form name correct? Is it open when you run the query?

[Seth Schrock]

The form name is correct and it is open at the time the query is ran. If it wasn't, the query would prompt for the value in the control.

[Rabbit]

I'm stumped, there's no reason why it shouldn't work. Can you attach your database?

[Seth Schrock]

I hope you have Access 2007 or 2010. I can't save it as 2003. Also, don't look at the design of the rest of the database. I'm in the middle of redoing it as it is not the best at following normalization rules.