Subform Problems (Date & Filter)

[chellebell1689]

I have an attendance form with a subform for each Sunday School class at my church. I am having two problems with the subforms, the date & a filter.

I have the date box set up at the top of each column and assign to the appropriate field in the appropriate table. My problem is that the date is saving only for one person at a time and I have to retype the date every time i check the attended box. How do I set it up so that when the date is entered into the box, it automatically adds it to all members? (I feel it needs to be done through a macro, but I'm not sure how to set it up.)

The other problem is that I have a filter for each form (who is on the roll sheet), and it works in the actual form, but when I open the attendance form (with the subforms) it gives me an error about trying to apply a filter and it's not assigned to a table...?? I'm not applying the form to the attendance form, just each individual subform. How do I fix this problem?

Thanks in advance!

 

[chellebell1689]

Can anyone help with either problem??

 

[Frothingslosh]

The reason you've not gotten replies is because your question is really kind of vague, and it also sounds like you're still thinking in terms of Excel. We need to know precisely what you're doing, written as if explaining it to someone who doesn't know a bloody thing about your database.

For example, you want the date changed for all members. Got it. But in what table? Under what circumstances? What date? Does this include prior days they attended? Do you want to have it default to a specific date entered or just default to today's date? Do you want it to be applied retroactively or just to new records as they are created?

There are all sorts of ways to do what you're asking, but without knowing precisely what you're going for, there's no guarantee that just tossing out one random approach (like setting the 'default value' property) will actually be the one you need. And honestly, a screenshot of your form (with any identifying data blacked out) would help immensely.

Same really applies to your second question - we need specifics. So you have a filter that works in one form but not another. We get that, but it gives us literally no starting point to use in order to help you. What is the 'actual' form, and why are you trying to apply a filter from that form to a completely different form? (Filters don't work like that, by the way.) What precisely are you trying to do with the attendance form, and why isn't the built-in filter that comes with subforms doing the job? What data are you seeing as compared to what you're expecting?

Give us more to work with and we'd be happy to help you.

 

[pbaldy]

Updated post here, so I'll close this thread:

Edit; since there's a reply here, I'll delete the other thread.

 

[chellebell1689]

Oh, I'm so sorry! Sometimes what I say makes sense in my mind, but I tend to (more often than not) forget other people can't see inside my head. lol

Ok, let's start with the first question.
I have a table with all the church members that are currently on a roll sheet. The way the attendance is set up is so that I can type in a date and hit the check box if they were present. But it is currently set up so that I have to enter the date for each member (and there's a lot of members). Ultimately I want is for the user to be able to open this form, enter the date for each Sunday of the current/upcoming month (depending on when they accomplish this), print the roll sheets (which I believe I can set up, we'll see), and then after each Sunday come in and just hit the check box of who was present and who wasn't. I've got an append query set up so it will update each record, and I only want it to update the records if the box is checked.
Also, (not sure if this is needed info) I have the attendance table as a subdatasheet in my master members table so that we can see each time they have attended Sunday School.

Let me know if you need any more information. I tried to give as much as possible, but I might have left something out. Sorry again for the vague questions.

 

[Frothingslosh]

Apologies for the delay getting back to you - we've had a couple urgent issues come up at work, so I have been and still am quite busy. Trust me, your questions aren't forgotten.

 

[chellebell1689]

no problem at all! I completely understand. I only work for my church Mon-Wed, so no rush. ��

 

[Frothingslosh]

Okay, there are a couple ways to approach this.

The long way, from the looks of it, may involve some major re-writing, but it will make your life much, much easier down the road.

The short way is a quick fix, but from the looks of it, you're going to have a lot of problems going forward, as I'm guessing based on both the image and your comments that your database is absolutely non-normalized.

The quick fix would be to put an unbound date field in the form header for the user to fill out, and then have a button that uses that date to populate the records for the month. For that, I would need to know how you're generating the records and how this is all laid out.

The drawback to this is that it's a band-aid. If your database is as non-normalized as I suspect, you're going to run into never-ending problems along this line because, while data sheets may LOOK like spreadsheets, Access doesn't work remotely like Excel does.

If you want the longer fix, I have something half-typed up and saved from my initial start at replying.

 

[chellebell1689]

Thank you for that! I don't know what "non-normalized" means, but I would like the permanent fix and not the band-aid. I wont always be the one to work with this and I may not always be around ti fix problems, so I want to make it as bug free as I can. Thank you for your help!

 

[Frothingslosh]

Okay, while I finish up the original post I was going to make (it's quite long), what you should do is look into "database normalization". It can be a lot to take in all at once, and I don't have any really good links for it (others keep posting them and I keep forgetting to save them), but in a nutshell, it means that each table should be about one 'thing', and every field must relate directly to that thing and only to that thing.

My normal example is invoicing. On an invoice, you have the buyer, the date, the invoice number, the shipping address, the items bought, their costs, the amount shipped, etc.

In Excel, you can represent this all on one spreadsheet. In a database, however, you would want the buyer and the shipping address in one table (two, if you allow multiple shipping addresses), the invoice number and date in another, the items and their pricing in a third, and the actual invoice lines (x items bought, y shipped) in a fourth.

Then these tables are all linked together using primary and foreign keys. This is because each buyer may have multiple invoices, each invoice can have multiple line items, and while each line item only has one item purchased, the items may appear in many, many different line items on different invoices. These are all examples of one-to-many relationships.

So start by trying to make heads or tails of database normalization, and I'll post again once I get the post complete.

 

[Frothingslosh]

Okay, looking at this while I run some reports.

What do you mean by 'subdatasheet'? Do you mean that you have them in separate tables and set up a one-to-many relationship?

One thing you're definitely going to need to do is forget about Excel. Access has tables, not sheets, even if they look similar by default.

If I were to set up something like this, I would have tables for Members, Classes, Staff, and Attendance. Something like this:

tblMembers
MemberID (Autonumber unless you need to actually use this IRL)
LastName
FirstName
(Contact info such as address or phone number)
Enrolled (Yes/No, tracks if enrolled in Sunday School)
Active (Y/N, tracks whether they're active members or no longer church members)

tblStaff
StaffID (Autonumber)
LastName
FirstName
PositionID (Long Integer, linked to tblPositions.PositionID)
Active (Y/N, just tracks whether they're still working for the church)

tblPositions
PositionID (Autonumber)
PositionName

tblClasses
ClassID (Autonumber)
ClassName
ClassDescription (Memo)
*This allows you to track classes differently. When I attended Sunday School as a kid, there were several classes, broken down by student age.

tblEnrollment
EnrollID (Autonumber)
ClassID (Long Integer, linked to tblClasses.ClassID)
MemberID (Long Integer, linked to tblMembers.MemberID)

tblAttendance
EnrollID (Long Integer, linked to tblEnrollment.EnrollID)
ClassDate
Attended (Y/N)

You could even do things like include class topics in the Attendance table, include start and finish dates for the classes so you can track each class separately (say, Summer 2014 vs Summer 2015 vs Summer 2016). If you're really feeling froggy, you could even include start and finish dates in both the staff and the class tables and get a thorough history.

The purpose of the tblEnrollment is to generate a list of which members are in which class. If everyone in your system is to attend each Sunday School class, let me know and we can do a somewhat more straightforward setup, but be aware that you would be losing massive flexibility.

Anyway, with this setup, you would create a form where you select a class, enter a date for the class, then press a button. This button would pull everyone enrolled in the class, add records to tblAttendance for everyone enrolled for the given date, and then open a form showing only those records for attendance.

This is a fairly complicated setup, but the payoff is it will make reporting much, MUCH easier. It also lets you get away from having to create a different tab for each class; through the use of forms and sub-forms, you will be able to pull up the class of your choice via simple combo-boxes.

Aaaand I just got handed a huge project that’s time-sensitive. I’ll get back to this as soon as I can.

 

[chellebell1689]

Ok, here's a pic of most of my tables. I was contemplating having a master table for everyone (so I can have an update/append query to sort ppl) and then having a table for each member type (visitor/deceased/moved/current/child). Also, I have a table for each Sunday School class.

So am I on the right track with this, or do I need to split them more?

Also, I included the fields that we have for each member (in case I do need to split it up more). There's one more pic with 15 more fields, but I had met my cap.

Thank you so much for helping me with this! I really appreciate it! I've been working on it trying to find out what works best and then I was planning on starting from scratch but only adding what I found works best (best way for me to learn).

**To your second reply:
The way I have it set up is that I have a table for all the members (those who have joined the church by baptism/letter/statement) and I wanted it set up so that the subdata sheet would show each time that person has attended Sunday School. But the way I have the SS attendance is split into tables for each class and you can't do a subdata sheet for multiple tables. I'm willing to do whatever works best and lasts the longest!

**Really wish you were here in North TX!**

 

[Frothingslosh]

That'll definitely have to wait a bit - this new project is 30 seconds typing, 30 seconds searching. I can do quick answers atm but nothing major, as there are around 600 lines I need to find and copy data from.

 

[chellebell1689]

No worries! My project has no deadline, you do what you need to do! I don't want to bite the hand that feeds me, lol. I really appreciate the help!

 

[chellebell1689]

Heyo,
Just wondering if you've had a chance to look at this and help me.

I greatly appreciate the help!

 

[Frothingslosh]

Hopefully that project will be finished today - it's a freaking nightmare.

 

[Frothingslosh]

Okay, that really annoying project is finally complete (nothing like being handed several thousand more lines to check and being told in no uncertain terms that automating it isn't allowed), and I'm finishing catching up on my other work. I won't be able to do anything today (vet appointment after work), but I'll look at this again tomorrow. My apologies for the delay!

 

[chellebell1689]

lol sounds fun. :-D
No problem at all, you do what you need to. I really appreciate the help!

 

[Frothingslosh]

Okay, going through your links, there are some issues.

First off, you should never have numbered tables. Enumerated tables mean you're incorporating data into the database structure rather than the database, and that will bite you in the ass when you start writing queries and reports. (One example - pulling the same data from each of 9 different enumerated tables can easily require writing 9 virtually identical queries.

So for your classes, you would want one single 'Classes' table that includes all the relevent data, and you would include a class ID to differentiate between them. That would also let you differentiate between the class taught by Bob from 3/1 to 6/30, and the class taught by Bob from 7/1 to 10/30.

There's no need for a kids table as well as a members table. Beyond the question of whether or not kids are members of the congregation, it's just going to cause other problems - what happens when they turn 18 and are members but no longer attending Sunday School? The same with Members and Moved Members. Instead, just have one table - Members, or Attendees, or Parishoners, or something, and include all three categories. You can ignore Age as long as you have Date of Birth. As to member vs moved member, you can signify that with a yes/no field or, better yet, a number field linked to a separate MemberStatuses table, which would include things like Active, Inactive, Moved, Deceased, etc. (This, FYI, is called a 'lookup table', if you want to search the term.)

For your 'parents' data, you can use your Members table. That's one reason you should use an ID number for everyone, even if just internally. I don't really want to go political, but just to cover all possibilities, you could go Parent 1 as a long-integer (assuming your ID is auto-number), linked back to the ID, Parent 1 Role (Father/Mother/Guardian/Foster/Step/etc), and the same for Parent 2 (if there is one - parent 1 could be single, divorced with sole custody, widowed, etc).

As a rule, I tend to prefer the wording 'gender' over 'sex', as it's more neutral (and you don't get jokers answering 'lots' or 'yes').

You should NOT have your calss data in the members table - doing so is a violation of both second and third normal forms and makes life more difficult. Among other things, it eliminates any possibility of you keeping a history.

Same with Deacon. You should have a 'Staff' table of some kind, with yourself and any other personnel listed. If you absolutely have to track who each person's deacon is (and I always thought there was one deacon per church - learn something new every day!), you can do so through a field linked to the Staff table.

Basically, looking at your design, the very first thing you need to do is learn about Normalization. All relational databases (such as Access) are based on the concept, and only work well when the tables are normalized. I would suggest starting by looking up 'normalization' on this forum and reading some of the replies that pop up, especially if the person replying is a moderator or has 'Access MVP' or thousands of posts in his or her stats.

 

[Frothingslosh]

I strongly advise you to post another thread on this topic, as I will no longer be around to help you further.