Many thanks to both of you for responding. Golfinray's solution worked for
the short term. I had already tried it, but didn't see anything because I
had not entered any CommitteeID data and the query was telling me so by
remaining blank.

JWV: Actually, for now at least, one committee is all anybody will be
assigned to, but you make a good point and it probably behooves me to broaden
the scope now, rather than later. I'll keep working on it.

Many thanks, again.

-- wiz

Green and Growing


"John W. Vinson" wrote:

On Thu, 6 May 2010 08:57:00 -0700, Wizbard
wrote:

I know I should know how to do this and have probably done it before, but
can't seem to get it right:

Tables: Households (Many) Committees (One)
Fields: (Standard Contact Info Fields) (Chair & 12 # fields for Members)
Many to One Join on Committees.ID to Households.Committees_ID

I want to be able to build a Committes query and form that 1) in the form
selects a name from a Households.Head dropdown list in each Member (1, 2, 3,
etc) field; 2) after selection, displays Phone and Email info beside each
Committe.Member's name; 3) stores relational data in the proper table.

I haven't done much Visual Basic, but I have a little experience with macros.

Any tips, pointers and outright solutions are greatly appreciated.

If you have twelve fields for members... you have a spreadsheet, not a
normalized database! in other words your table structure isn't appropriate.

And if you have a committees_ID field in Households, you are saying that each
person may be on one, and only one, committee. In most organizations that's
not realistic.

I'd suggest that you instead use a normalized many to many relationship,
allowing each Committee to have any number of members, and each person to
serve on any number of committees:

Committees
CommitteeID primary key
CommiteeName
other info about the committee as an entity in its own right

Households
HouseholdID
contact info

CommitteeMembership
CommitteeID link to Committees
HouseholdID link to Households
Role e.g. "Chair", "Secretary"

I'd strongly urge that you avoid the "lookup" featu see
http://www.mvps.org/access/lookupfields.htm
for a critique. Instead use a Form with a Subform, using combo boxes on the
forms as appropriate. It's not necessary to have a Lookup Field in the table
to do so, and in fact you should not be entering data in tables at all.
--

John W. Vinson [MVP]
.