Not sure on set up

[chellebell1689]

So I know this question goes somewhere, but not sure where the best place is, so I posted here.

I have three tables (for this question) Members, Family, Deacon. Member is linked to Family (many to one); Family is linked to Deacon (many to one); and (currently) Deacon and Member are not linked. If I link these two, it would be one to one. I want the Deacon table to be autofilled with the Name & Phone if the "Deacon" field in Members table is "Yes".

Could I do this with an Append Query? Do I have to link them? Is this even possible? Also, would I use a Delete Query to remove the Deacon once the filed is changed to "No"? (Our Deacons don't change that often.)

 

[MarkK]

What I might do in this case is have one table called tblPerson with three fields, IsMember, IsDeacon, and IsFamily. Then, if someone becomes a Deacon, I just set the IsDeacon field to True. Done.

Then, if I only want to see deacons I write this query . . .

SELECT * FROM tblPerson WHERE IsDeacon

 

[plog]

That's not how relational databases work. You don't "autofill" tables with data from other tables. Instead when you need that data, you create a query, link your tables apporpriately and then bring all data into that query and use that query.

Additionally, you have logical inconsenstnecies in the relationship among those 3 tables:

Many members to one family
Many families to one deacon.

Transitively that means you have a many to many relationship between members and deacons. But you say its one to one.

First, you generally shouldn't have one to one relationships at all. You just put the data in that relationship all in the same table. Second, you wouldn't establish a formal relationship between all entities--relationships should be like branches on a tree, each sprouted out from one trunk. Not a spider web where they are all inter connected.

Maybe post some sample data from all 3 tables to demonstrate what you want to accomplish.

 

[chellebell1689]

Oh ok, thanks for explaining that plog! I thought everything had to be connected. As for the connection between Deacon and Members, it's really how you look at it. So there maybe a deacon with DeaconID "1" and his name is Tom Smith, Tom may have many families with many members to look after, but on the reverse only one member is named Tom Smith with Deacon ID "1". That's why I was saying it would be one to one. As I go along I am seeing that not all the tables will be connected to the others.

So if I wanted to make a report to show all the families (and members) assigned to a specific deacon, do I need the Deacon table? Or can I just do it with a query?

 

[plog]

So if I wanted to make a report to show all the families (and members) assigned to a specific deacon, do I need the Deacon table? Or can I just do it with a query?

Yes. You would need a query in which you would use the Deacons table.

Can you post the structure of all 3 tables we are talking about?

 

[chellebell1689]

K, last question! I've got a subreport kinda set up...but I don't know how to get it to show all members assigned to that specific deacon. It's in my member detail form and I have a tab for the deacon families, there is a field that will show the deacon's number. I want the subreport to pull that number and show all members for them. I think I have it close to being set up. It currently pulls the member's name, number, birthday from the member table and the family ID & Deacon ID from the family table.

Am I on the right track? How do I get it to filter? (currently it only shows the member that is currently open, even if they're not a deacon)

 

[plog]

I really don't know if you are on the right track, kind lost me with all the subforms, tabs and filters.

Generally, to have a show a subreport filter to just specific items that match the main report you use the child/parent on the attributes of the subreport.

 

[chellebell1689]

K, here's two pictures of my set up. The "detail form" png shows the field asking if they are a deacon or not. If this bubble is filled in, then (looking at the "deacon tab" png) this subreport will be filled out with all the members assigned to this specific deacon and the "Deacon's ID" field will have their deacon id in it.

The third picture "deacon tab example" shows what currently happens if I click on the deacon family tab. (Note: Sandy is NOT a deacon)

Hope this is a little more clear on my end goal.

 

[chellebell1689]

Good news! I (somehow) got the subreport to filter (by playing with the actual report). If I open the actual Deacon Family report, it won't filter...but in the form it does. So that's good! Now my problem is how to make the deacon tab not available if the member is not a deacon, and how to print the family report from the member tab (with it filtered)...I think I can get the last one done.
I've got another post open for the deacon tab, and I think I can figure out the printing; so I'm closing this post.

Thanks so much for all the help!!