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.

 

[CantDo]

OK, I'm not sure how that last step helped, Duane, but IT NOW WORKS PERFECTLY! YIPPEE!!

Thanks, Duane. I think I will get the book; it seems OK and I *am* trying to learn this stuff, after all.

Thanks again!

 

[BTNewman]

CantDo,

I'm having this same problem with the same error message you were getting. Can you tell me what you did to fix it?

 

[CantDo]

BTNewman:

I don't understand. What problem are you having? I basically followed the advice set out above and, the last step from dhookom did the trick. Of course, I first had to make sure my tables, relationships and forms were designed correctly (as described above). If I can help further let me know and I'd be more than willing, but first tell me exactly what problem you're haing.

Thanks.