Stuck on Simple Query and Form from 2 Tables
in [Getting Started]
|
From: Wizbard on 6 May 2010 11:57 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. -- Green and Growing, -wiz
From: Wizbard on 6 May 2010 13:01 Sorry, the join is One to Many, Committees to Households. -- Green and Growing "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. > -- > Green and Growing, > > -wiz
From: golfinray on 6 May 2010 13:38 One-to-many relationships are perfect for using a mainform and subform. Build your mainform to operate off the ONE side. Add a subform and use the MANY table as the recordsource. The primary key in the mainform is the foreign key in the subform table and then they will link properly. Allow the subform wizard to do that for you. If you have any questions about primary and foreign keys, microsoft help has good explainations. -- Milton Purdy ACCESS State of Arkansas "Wizbard" wrote: > Sorry, the join is One to Many, Committees to Households. > -- > Green and Growing > > > "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. > > -- > > Green and Growing, > > > > -wiz
From: John W. Vinson on 6 May 2010 14:50 On Thu, 6 May 2010 08:57:00 -0700, Wizbard <Wizbard(a)discussions.microsoft.com> 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" feature: 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]
From: Wizbard on 6 May 2010 18:36 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 <Wizbard(a)discussions.microsoft.com> > 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" feature: 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] > . >
|
Next
|
Last
Pages: 1 2 Prev: Autoincrement Next: I NEED TO DESCARGAR THE PRODUCT KEY FOR MY MICROSOFT OFFICCE 2010 |