Problems with table or relationship structure? 1

[CantDo]

I have four tables:

Table 1 : Families
FamilyID as Autonumber
LastName as Text
{other family specific fields not relevant to this question}

Table 2: Members
MemberID as Autonumber
FirstName as Text
FamilyID as Number FK to Families.FamilyID
UniqueLastName as Text
{other member specific fields not relevant to this question}

Table 3: Groups
GroupID as Autonumber
GroupName as Text
MemberID as Number FK to Members.MemberID
PositionID as Number FK to Positions.PositionID

Table 4: Positions
PositionID as Autonumber
Position as Text

The relationships between the tables are one-to-many all the way down the line as follows: Families > Members > Groups > Positions

Tables 3 and 4 are pretty static; i.e., their data is pre-set and will only change very infrequently.

The data in Tables 1 and 2 will grow, shrink and change over time.

I want to be able to create a form where I can add, edit or view, via comboboxes, the info. I am envisioning a form would look something like this:

Group: [combobox]

Continuous Subform: Members:

LastName [combobox] / FirstName [combox -- filtered after LastName updated] / Position [combobox]

I know I have a problem with the way I've designed these tables or their relationships, but for the life of me I can't figure out where I've gone wrong. Can anyone help? Any assistance is appreciated.

 

[lespaul]

what makes you think you have a problem? Everything looks fine to me.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[dhookom]

I think of groups as committees, organizations, etc. where a group might have many members. I don't see how this could be implemented without tables

tblGroups
==============
GroupID
GroupName
Active

tblGroupMembers
==============
GroupID
MemberID
PositionID
StartDate
EndDate
Comments

I might not understand your application and if so, please disregard...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

Thanks for your advice so far, guys.

lespaul: I can't create the form I've described; it won't work. I'm sure its either a problem with the table design or with the relationships.

dhookom: Thanks. I've come to the same conclusion, that another table is needed. I also think that the relatationship between groups and members needs to be many-to-many. Do you agree? Anyway, I'm trying that out this morning and I'll let you know. Thanks again.

 

[dhookom]

CantDo,
I think I agree with what I stated earlier

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

I'm not sure how to rephrase my question in better terms so as to make you understand it. So instead, I'll describe the database I'm trying to create generally in the hopes you can continue to help me.

This is a database being written by me on a volunteer basis for our local Church. It keeps contact and other information for church families and their members (hence the separate tables Families and FamilyMembers to avoid duplication of LastName and Address -- & other -- fields). It also tracks positions held by individuals in Church groups such as Church Offices, Church Council, Pastoral Committee, etc. Within these groups, an individual could be a Convenor or a Member or a Volunteer, for example. The actual groups themselves and the positions church-wide are pretty static and won't change much over time. Individuals can be part of any number of Groups and each Group can have any combination of available positions.

I've got the family and individual tables working fine so that data entry and retireval can be achieved correctly. What I can't get my head around is how to 'design' the group and group position information in their respective tables nor how to relate these to the individuals (and families) table once they've been designed. This is where I'm breaking down.

My ultimate objective is to create a data entry form for groups and position -- where I enter, edit and show Group, family name of member, first name of member and position held -- in the simplest and most user-friendly way possible.

I hope that makes my question more understandable and I appreciate any help I can get. Thanks.

 

[dhookom]

Have you created tblGroups and tblGroupMembers? You would use tblGroupMembers (or a query based on it) in a subform on the Members form to enter their groups. You would use a subform based on the same table on a Groups form to select all the members of the group.

I would question whether families (rather than members) would be members of a group.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

Yes, thanks Duane, you're pretty close to what I'm looking for. I haven't tried your tables yet, but tables very similar have been tried. Let me ask you a couple of questions first before I try your suggestion in order to make sure we're on the same wavelength.

What's the necessity of the active field in tblGroups?

Are the linking fields in tblGroupMembers lookup fields? If yes, which ones?

What are the relationships between the tables?

Will this allow me to create a Groups form (with or without subforms) to add or edit members to a Group as in my original description in my first post above? If so, how would that be structured using the same type of nomenclature as in your last post?

Maybe we're getting somewhere, but I've tried so many things up until now, I'm just trying to make sure I'm not going down a path I've tried before. Thanks.

 

[dhookom]

I add an Active/Status field to most of my "lookup" type tables so that I can leave records in the table but set them to inactive so they don't appear in combo boxes. For instance, if you have a product table with 100s of products, you may not sell some of these any more. You need the inactive products in the table for history purposes but you don't want them to display in the combo box for new order details.

I don't use lookup fields. They are evil

http://www.mvps.org/access/lookupfields.htm.

The tables are related based on the field names.

You can edit groups and participation in groups using a form with a subform. This is like an Orders form with an Orders Detail subform.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

One last question before I give this a whirl, Is there a key field in tblGroupMembers? It doesn't look like it from your structure.

If there is, I've tried this before with no luck. My problem is that with the structures I've tried before: i.e., Families > Members > Groups > Positions, I can't seem to create a form based on a junior level (i.e. Groups) with a subform based on a senior lever (i.e., Families and Members).

Anyway, thanks for all your help with this but I feel like I've been spinning my wheels for a number of days on this. I may just do something completely different. Thanks again.

 

[CantDo]

Duane:

OK. I've tried your structure (without a key field). When I create my form based on Groups table, choosing a Group on the form does not result in a corresponding match in the subform (I have a GroupID field visible in the subform to check this and it's always blank no matter what). The subform is based on the GroupMembers table linked to main by GroupID. I have an AfterUpdate procedure on the GroupID control to requery the subform.

I've tried "pushing" the form's GroupID value to the subform's GroupID prior to the requery, but, as its currently setup, when I click on the form's GroupID control, I get an error saying "Run-time error '3348': Cannot add record(s); join key of table GroupMembers not in recordset". This is odd since the subform is based on the GroupMembers table and GroupID (the linking field) is in the recordset.

What now?

 

[dhookom]

I would add a primary key field on tblGroupMembers but others wouldn't.
I don't think you need to create one form with subforms to manage all of this. Stop thinking about Families > Members > Groups > positions. Take the relationships two tables at a time.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

If your link master/child properties are GroupID, then you don't need any code. The subform will automatically display the group member records. Make sure the subform is not set to data entry.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

OK, this will work to look at these records, but want I need is a data entry form centered on Groups. That's where I've been having my trouble all along. I am so frustrated right now I could spit! I can't get this to even come close to what I'm expecting. I think I'm going to give up.

Thanks anyway, Duane.

 

[dhookom]

If you set the Data Entry property of a form or subform to Yes then you only see and add new records. You don't see previous records that you have added. Just set your forms to allow additions, edits, deletions,...

If you can't figure this out, take a look at the order enter form and subform in the Northwind.mdb. Your Groups are like Purchase orders and your group members are similar to order details.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

CantDo

Duane is bang on here I think.

You have a many-to-many relationship where a person can belong to more than one group, and a group can have many members.

This is why you need...
tblGroupMembers
==============
GroupID - foreign key
MemberID - foreign key
PositionID - foreign key (position w/n the group)
StartDate
EndDate
Comments

Primary key could be GroupID + MemberID + StartDate
(Eg, if a person is chair for multiple periods)

The only tough issue is if a person holds more than one position within the group. For example as chair and secretary.

...Moving on
You asked about how to present the data in a form...

I use two approaches...

Main form + subform
I personally perfer this approach.

Create a form on say tblGroups
For the subform use tblGroupMembers. The subform can be / should be a continous form.
Child and Master link field is GroupID
Hide GroupID control text box. Not necessary to see this in the subform.

Turn MemberID into a combo box
RowSource: Member with an inner join on family
ControlSource: MemberID

Turn PositionID into a combo box pointing to tblPositions / Positions table.

When you look at the form for a specific group, you will see the attending members and their positions.

...Now flip the approach. Base the main form on the tblMembers. The subform will still be tblGroupMembers. Since the link field will be MemberID, hide this control in the subform and change GroupID to a combo box. When you look at this form, you will see what groups the person is involved with (and past groups).

...The other approach is to use a form based on tblGroupMembers where both GroupID and MemberID are used as combo boxes. This approach may be okay for quick entry, but in my opinion, would not be useful in the presentation of data.

Richard

 

[CantDo]

Richard:

Thanks. That was something I could actually understand. Thank you for taking the time to write it out in detail for me. When I looked at what you wrote, I was sure I had tried this structure before but, just to make sure I wasn't losing my mind, I tried it again.

So, to recap, here are my tables:

Table 1 : Families
FamilyID as Autonumber
LastName as Text
{other family specific fields not relevant to this question}

Table 2: Members
MemberID as Autonumber
FirstName as Text
FamilyID as Number FK to Families.FamilyID
UniqueLastName as Text
{other member specific fields not relevant to this question}

Table 3: Groups
GroupID as Autonumber
GroupName as Text

Table 4: Positions
PositionID as Autonumber
Position as Text

Table 5: GroupMembers
GroupID as Number FK to Groups
MemberID as Number FK to Members
PositionID as Number FK to Positions

I set GroupID and MemberID as key fields in Table 5 (as you kinda suggested). Relationships were created based on the fields in GroupMembers.

I then created a form based on Groups and placed a listbox (lets call it GroupList) showing Groups with a hidden bound column tied to GroupID. I then created a subform based on GroupMembers and placed combo boxes as you suggested. I have left GroupID visible (for now so I can see what's going on) on the subform.

Initially, I set the control source of GroupList to GroupID and the combo boxes actually seemed to work and I was able to create a first entry from the form but when I looked at the underlying table, it had created an entry tied to Group 0 (there is none in the Groups table; they start at 1). When I went back to the form, two things were evident: first, I was unable to select a Group from GroupList (I got a Windows error tone when I tried) & second, the GroupID on the subform had moved to 1. Ever since, it has stayed on 1.

I tried removing the Control Source on GroupList, and that let me choose a Group in GroupList but the subform GroupID did not move with it and it stayed at 1.

To make things even stranger, although the Groups, Positions and GroupMembers tables (when I cascaded the view) were updated to show the inputs I made the entries did no appear in the Members table (when cascaded).

Can you see why I've been so frustrated with this thing? It always comes sooooo close but it never quite seems to work right! Any more thoughts on what I'm doing wrong?

Thanks again.

 

[dhookom]

I'm not sure why you added any list box or combo box on your Group form. You should really only need to display the GroupName field. You can show and disable the GroupID if you want.

The subform on the Group form is based on the GroupMembers table. This subform displays two combo boxes for selecting a Member from the member table and possibly a position from the position table. You can display the GroupID. The subform should be continuous and have the Link Master/Child properties set to the GroupID field.

To see how a professional would do this (and get his entire application) check out John Viescas' site:

http://www.viescas.com/Info/books.htm

John has created and gives you a complete membership MDB (and several others). These sample applications would take a very good developer months to build. For less than $50, you get $1,000s of consulting and contract development.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CantDo]

I'll look into your suggestion; but until then, I'd like to get somewhere with what I'm doing.

The reason I added a list box (or combo box) to my form is to allow me to choose a group to view/edit/add to. That was what I've been trying to accomplish all along. I hate using Access' built in navigation buttons, so I avoid them at all costs.

I don't understand how, if I only "show" the GroupName field, I can use this form to dynamically view/edit/enter group information -- not without another form calling it, I mean. My goal was to create one form, mainly for data entry purposes, centered on Groups where a user could choose a group and then pick from comboxes the values to populate it.

BTW, I still can't tell why I got a GroupID of 0 when none actually exists. Do you?

 

[dhookom]

Your group table has only one field other than the GroupID. What other "group information" do you expect to "view/edit/enter" on the main form?

There is a combo box wizard that allows you to find a record in a form. The combo box is unbound (it has no control source). The combo box should not be used to link master child.

What is the record source of your main group form?
What other "bound" controls are contained on this form?
What are the subforms on the group form?
What are teh record sources of the subforms?
What are the Link Master/Child properties of the subforms?

I just picked up the Viescas book to read through it while watching tv. I doubt there is any better value. This is not because my name is in the book and one of the sample database applications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]