473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Design question

Seth Schrock
2,965 Expert 2GB
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 (txtAttendanceDate, 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 txtAttendanceDate 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 txtAttendanceDate 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:

Expand|Select|Wrap|Line Numbers
  1. tblFamily
  2. FamilyID, PK, AutoNumber
  3. Family info...
  4. Attended, Yes/No
Expand|Select|Wrap|Line Numbers
  1. tblAttendance
  2. AttendanceID, PK, AutoNumber
  3. FamilyID, Number, foreign key relationship
  4. DateAttended, Date
Oct 12 '12 #1

✓ answered by zmbd

Once again.... this database isn't normalized in that I stripped the tables out that had retreat details and fields that held family information.

When the database is opened the main form will also open.
Two subforms (neither directly linked)
Upper form, basic form access to the retreats. Realtime addition and deletion of the records; however, if any family is assigned to an attendance then the record can not be deleted.

Lower form, same thought as above with the checkboxes.

The rest should be straight forward from the form.

Behind the scenes... a few update and delete queries for the magic. The realtime update between upper and lower forms handled with on current event and so forth.

Feed back always welcome.

Started to use the new navigation form control; however, I haven't quite got the hang of it and I only had about an hour here to tie this up between sample runs and other projects.

31 2210
zmbd
5,501 Expert Mod 4TB
Changing the thoughts...............
Just a second while I find that silly DB I had..........
Oct 12 '12 #2
Rabbit
12,516 Expert Mod 8TB
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.
Oct 12 '12 #3
zmbd
5,501 Expert Mod 4TB
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 :) )
Attached Files
File Type: zip faithformationtracking.zip (38.2 KB, 78 views)
Oct 12 '12 #4
Seth Schrock
2,965 Expert 2GB
@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).

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblAttendance ( FamilyID, DateAttended )
  2. SELECT tblFamily.FamilyID
  3. , (Forms!frmAttendance!txtAttendanceDate) AS DateAttended
  4. FROM tblFamily
  5. WHERE tblFamily.Attended = True
  6.  
@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.
Oct 12 '12 #5
twinnyfo
3,653 Expert Mod 2GB
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.....
Oct 12 '12 #6
Rabbit
12,516 Expert Mod 8TB
That query should work, what happens when you do it as just a select query? What does it show? Are there errors?
Oct 12 '12 #7
zmbd
5,501 Expert Mod 4TB
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!
Oct 12 '12 #8
Seth Schrock
2,965 Expert 2GB
@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_That_Have_No_Attendance and qry_families_wo_attendance_past6mo, 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.
Oct 12 '12 #9
Rabbit
12,516 Expert Mod 8TB
Is it txtAttendanceDate or txtDateAttended? Your query shows one thing but you say another.
Oct 12 '12 #10
Seth Schrock
2,965 Expert 2GB
Sorry, txtAttendanceDate 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.
Oct 12 '12 #11
Rabbit
12,516 Expert Mod 8TB
Is the form name correct? Is it open when you run the query?
Oct 12 '12 #12
Seth Schrock
2,965 Expert 2GB
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.
Oct 12 '12 #13
Rabbit
12,516 Expert Mod 8TB
I'm stumped, there's no reason why it shouldn't work. Can you attach your database?
Oct 12 '12 #14
Seth Schrock
2,965 Expert 2GB
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.
Attached Files
File Type: zip Maranatha Baptist Church Directory (1).zip (287.5 KB, 68 views)
Oct 12 '12 #15
Rabbit
12,516 Expert Mod 8TB
Weird. Ok, it's because it's a date formatted text box. Put a CDate() around the form control reference and it should work fine.
Oct 12 '12 #16
Seth Schrock
2,965 Expert 2GB
Okay, here is my code:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblFamily.FamilyID
  2. , CDate(Forms!frmAttendance!txtAttendanceDate) AS DateAttended
  3. FROM tblFamily
  4. WHERE tblFamily.Attended = True;
And here is the error message I'm getting:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric express may contain too many complicated elements. Try simplifying the expression by assigning parts of the express to variables.
Oct 12 '12 #17
Rabbit
12,516 Expert Mod 8TB
Try this, this worked for me:
Expand|Select|Wrap|Line Numbers
  1. CDate([Forms]![frmAttendance]![txtAttendanceDate])
Oct 12 '12 #18
Seth Schrock
2,965 Expert 2GB
I still got the same error message.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblFamily.FamilyID
  2. , CDate([Forms]![frmAttendance]![txtAttendanceDate]) AS DateAttended
  3. FROM tblFamily
  4. WHERE tblFamily.Attended = True;
I even tried leaving off the alias in case that was what you meant. Same thing. I tried creating a new query in case something had gotten stuck. Same thing.
Oct 12 '12 #19
Rabbit
12,516 Expert Mod 8TB
I've attached my copy. The only change I made was the one in my post. I'm using Access 2007.
Attached Files
File Type: zip fixed.zip (256.9 KB, 71 views)
Oct 12 '12 #20
Seth Schrock
2,965 Expert 2GB
There must be some glitch in Access 2010. I just tried yours and I got the same error message.
Oct 12 '12 #21
Rabbit
12,516 Expert Mod 8TB
Must be, it was running fine on mine.
Oct 12 '12 #22
Seth Schrock
2,965 Expert 2GB
I guess I'll have to wait to see what Z comes up with. I haven't been able to figure out how to make it work.
Oct 13 '12 #23
zmbd
5,501 Expert Mod 4TB
Oh....
I had been trying to follow the thread and noted that there had been some work and the kids were very "me-me-me" so stopped.... sorry.

Give me a few moments to get back in the groove
What I have so far is two sub-forms, one showing the retreat information that you can enter or select
The second shows the families and the check boxes
Just working on some code to sync the two forms etc...
Once again it's not normalized so there's some extra work to get things to be happy.
Oct 13 '12 #24
zmbd
5,501 Expert Mod 4TB
I haven't forgotten... been a busy weekend.

Just finishing putting togeither the logic behind the append to the attendance table... only another couple of minutes of codeing but I need to get some sleep... starting to type the same SQL twice in a row (zleepy Z) All of the queries work and I want to clean up the test entries before posting.

I'll have to get back to this Monday night after I get home. The boss is away on conference and we've auditors so I'll have very minimal time to even get to the email... :)
Oct 15 '12 #25
zmbd
5,501 Expert Mod 4TB
Once again.... this database isn't normalized in that I stripped the tables out that had retreat details and fields that held family information.

When the database is opened the main form will also open.
Two subforms (neither directly linked)
Upper form, basic form access to the retreats. Realtime addition and deletion of the records; however, if any family is assigned to an attendance then the record can not be deleted.

Lower form, same thought as above with the checkboxes.

The rest should be straight forward from the form.

Behind the scenes... a few update and delete queries for the magic. The realtime update between upper and lower forms handled with on current event and so forth.

Feed back always welcome.

Started to use the new navigation form control; however, I haven't quite got the hang of it and I only had about an hour here to tie this up between sample runs and other projects.
Attached Files
File Type: zip faithformationtracking.zip (146.9 KB, 183 views)
Oct 15 '12 #26
Seth Schrock
2,965 Expert 2GB
That is incredible! It is taking me awhile to figure it all out. So far, my only question is: In the frm_retreat On_Current event, do I need the
Expand|Select|Wrap|Line Numbers
  1. Select Case zstring
portion, or is that just for parts that you stripped out of the database? I know I need what is inside the
Expand|Select|Wrap|Line Numbers
  1. Case Else
part, but what about the rest?
Oct 16 '12 #27
zmbd
5,501 Expert Mod 4TB
ahhh my dirty little secret...

The entire select case statement is needed to link the two subforms.
zstring = Nz(Form_frm_maindataentry.ztxt_currentretreatrecor d.Value, "")
I'm using the text box as a semaphore and user feedback.
So I pull the current contents of the textbox and I'm looking for "update pending" or "move to last"
When the user enters a new record there are few things that can happen... if the user tabs thru, then you get to the new record, if the user does a <shift><enter> to save etc.... so when the new record is saved, the after insert event fires that tosses "update pending" in the mainform text box and kills any user sort; however, the user has moved the cursor and there's no way to stop it, so now the on current event fires, so I check for the update message, trapping the movement per say, now I flag "move to last" that I want to move to the last record in the form.... requery, which causes the on current to fire again and I get the move, which cause the on current to fire again, and now I can pull the information needed to sync the yes/no field in tbl_family with the entries in tbl_attendance. If the user has moved to the new record then the text box states blanks if the user cursored up then the cursor moves to the new(ly) inserted record and the information for that is shown.

There has to be a better way... and I've not found it.

The form's open event checks to make sure that the main form is open and if so then it moves to the first record then moves to the last.... that way the scrollbars show up; if one simply moves to the last record, the scroll bar doesn't show up until one hits the first record... annoying little glitch.

Clear as a Tar Pit?
Oct 16 '12 #28
Seth Schrock
2,965 Expert 2GB
So the Select Case isn't looking at the value, but the status (or whatever the correct term would be) of the record? If so, a tar pit might be too dark, but a mud hole might be appropriate. Way above my head anyway. Well, I will try to get everything switched over tomorrow and see if it works on mine (or more accurately, if I can get it to work).

A million thanks for all the effort you have put into this Z!
Oct 16 '12 #29
zmbd
5,501 Expert Mod 4TB
The best way to watch the action:
In the after insert event of the frm_retreat insert a Stop right at the beginning - save
Close all the forms. Re-open the main.
The form opens with the last record in the frm_retreat selected and the textbox under the form showing the retreat information.
Enter a new record using the {tab} key throughout... once you move to the new record, the after insert event fires.
The stop command sends you to debug... now arrange the windows so that you can step the code.
The "update..." text is entered into the main form - user might see this might not.
Any sort order is cleared
<1>Now the on current fires
All selected families are cleared
The "Update..." is read from the textbox
Select case deals with this cycle by:
The flag "Move..." is entered into the textbox
The frm_retreat is re-queried
<2>Now the on current fires due to the re-query
All selected families are cleared (should move this)
The "Move..." is read from the textbox
Select case deals with this cycle by:
setting the textbox to an empty string
moving to the last record
<3>Now the on current fires due to the record move
Case else trips
We check to see if we're on a new record (no)
so we build the query string that pulls from the record we're on (which should be the last inserted record) and we update the family subform.
recursion <3> ends
recursion <2> ends
recursion <1> ends
On current ends
After insert ends
...
Shift-Enter - no record movement, the cursor stays with the inserted record and the "Update Pending" flag is shown. This is fine as the current record is correct and the user can simply select the families.
...
Cursor up to a prior record:
Basically the same as the tab thru to new... in that it runs the loops and then puts the user on the newly inserted record.
...
Oct 16 '12 #30
Seth Schrock
2,965 Expert 2GB
Okay, I think I'll worry about HOW it work later. I have pasted in your code, Z, (making all the names fit my database) and it is working perfectly. Now all I have to do is follow your example for the reports that I need.

Thanks so very very much Z. I really appreciate all the work you put into creating this sample for me.
Oct 18 '12 #31
zmbd
5,501 Expert Mod 4TB
My pleasure.
The recursion is really a pain(...) in this case. One would think that in the afterinsert or afterupdate event that one could cause the record cursor to simply move to the last inserted record... it just doesn't work that way... took me months to figure that out back in the older versions of MSA!

The reports were just simple report wizard based using the two queries... I even used the control wizard to place the two buttons for the reports on the form... not the most elegant code; however for a very basic action it works.
Oct 18 '12 #32

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: andy2O | last post by:
Hello comp.lang.py, Can you help me with ideas for the following (somewhat newbie) OO design question in Python? Note, I'm using psuedo-code, not actual Python for the examples! Background:...
0
by: James Walters | last post by:
Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for...
0
by: Krist | last post by:
Hi All, I have a database design question, pls give me some help.. I want to define tables for salesman's sales target commission . The commission could be given per EITHER sales amount of :...
1
by: Krist | last post by:
Hi All, There is some additional info I forget on this same topic I just posted. I have a database design question, pls give me some help.. I want to define tables for salesman's sales target...
1
by: dixp | last post by:
I'm new to writing multithreaded apps and I have a design question. I have a winforms app and a class which has a method that does processing which is time intensive. I want the user to be able...
3
by: reageer | last post by:
Hi all, I have a design question: I have a bunch of users (name, address, zip, etc.). They are assigned a card with a specific id. The only thing unique is this card id, or probably the...
7
by: Steve Long | last post by:
Hello, I have a design question that I'm hoping someone can chime in on. (I still using VS 2003 .NET 1.1 as our company has not upgraded XP to sp2 yet. duh I know, I know) I have a class I wrote...
29
by: Brad Pears | last post by:
Here is a simple OO design question... I have a Contract class. The user can either save an existing contract or they start off fresh with a blank contract, fill in the data and then save a...
9
by: fjm | last post by:
Hey everyone, I lost my internet connection for about 5 months and finally got it back. This is one of the first places I came back to. I have to say that I had a heck of a time finding it...
2
by: RoaringChicken | last post by:
Hi. Vista Ultimate Access 2007 I'm developing an inventory database and have question on design. The database stores collection details. One item in the collection, one record. The design...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.