A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Stuck on Simple Query and Form from 2 Tables



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2010, 04:57 PM posted to microsoft.public.access.gettingstarted
Wizbard
external usenet poster
 
Posts: 6
Default Stuck on Simple Query and Form from 2 Tables

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
  #2  
Old May 6th, 2010, 06:01 PM posted to microsoft.public.access.gettingstarted
Wizbard
external usenet poster
 
Posts: 6
Default Stuck on Simple Query and Form from 2 Tables

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

  #3  
Old May 6th, 2010, 06:38 PM posted to microsoft.public.access.gettingstarted
Golfinray
external usenet poster
 
Posts: 1,597
Default Stuck on Simple Query and Form from 2 Tables

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

  #4  
Old May 6th, 2010, 07:50 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Stuck on Simple Query and Form from 2 Tables

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]
  #5  
Old May 6th, 2010, 11:36 PM posted to microsoft.public.access.gettingstarted
Wizbard
external usenet poster
 
Posts: 6
Default Stuck on Simple Query and Form from 2 Tables

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]
.

  #6  
Old May 12th, 2010, 01:59 PM posted to microsoft.public.access.gettingstarted
Wizbard
external usenet poster
 
Posts: 6
Default Stuck on Simple Query and Form from 2 Tables

I'm back... embarrassed to admit that I got it working for about a day and
then accidentally deleted my subform and haven't been able to recreate it
properly again.

JWV, I took your advice and went with 3 tables: Households, Committees and
CommMembers. I joined them through linking the ID fields of Households and
Committees into Households_ID and Committees_ID number fields that I set up
in the CommMember table.

I created the main form, frm-Committees, using the ID, Committee, Action
Items and Objectives fields from the Committees table.

I created the subform, subfrm-Committees, using the Role field from
CommMembers and the ID, Household Head, Phone (and other) fields from the
Households table.

Here's where I get lost: I want to use a combo box in place of the
Households Head field in the subform to select from a dropdown list of
Household Heads, which, when a name is selected, pulls the rest of the
contact info from Households table (which has already been populated) into
the subform record and eventually adds the Role, Objectives and Action Items
data to their respective tables.

Currently, when I select a name from the Households list, it creates a new
record in Households and populates the Household Head field in the new record
with the Household Head ID number, rather than the name. I don't want to be
changing Households at all, just get the name from that table.

I suspect my problem is in which fields I am joining and, perhaps the types
of joins between the tables, but I've gone round and round without getting
it right for two or three days. I'm just using wizards because I don't yet
grasp the code itself.

Can you help by suggesting which field from the main form I should be
joining to which field on the subform and what types of joins they should be?

Many thanks for your patience on what should be obvious to me.

-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]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.