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
0
Utf
5/6/2010 3:57:00 PM
access.gettingstarted 618 articles. 0 followers. Follow

5 Replies
513 Views

Similar Articles

[PageSpeed] 39

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
0
Utf
5/6/2010 5:01:01 PM
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
0
Utf
5/6/2010 5:38:01 PM
On Thu, 6 May 2010 08:57:00 -0700, Wizbard <Wizbard@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]
0
John
5/6/2010 6:50:00 PM
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@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]
> .
> 
0
Utf
5/6/2010 10:36:01 PM
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 <Wizbard@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]
> .
> 
0
Utf
5/12/2010 12:59:01 PM
Reply:

Similar Artilces:

How do I pubish a customised form?
I have customised the Lead form, and now need to Publish these changes before re-booting the IIS. The only thing is, I don't know how to publish the changes - can you help? Thanks On the toolbar go to 'More Actions' > Publish all customisations (the publish button only publishes customisations for the selected entity) Hope that's the answer you were looking for. Robert Turnbull "Julie" wrote: > I have customised the Lead form, and now need to Publish these changes before > re-booting the IIS. The only thing is, I don't know how to publish the ...

microsoft money #2
I downloaded a preview of microsoft money about 6 months ago and the time run out on it, I deleted the shortcut and other utensils that it uploaded to the computer. This week all of a sudden the first thing the computer does when you click on something to bring up from the program menu or even shortcuts it brings up install for microsoft money 2003 and it trys to install it. Seeing that I erased or at least thought I erased all of it. It can't finish it and I can't seem to get rid of it! What do I do? Thanks Tim Did you literally erase things or did you try Control Panel >...

upgrade from 1.2. to 3.0 + new server
Hi, we are actually using CRM1.2 and we plan to upgrade to 3.0 in the next days. Since our 1.2 is hosted on a SBS2003 with Domain controller in the same box, and this is not recommended by Microsoft, we want to move to a new server. So my first question, in a few words, what’s the best strategy to upgrade and move to a new server? My second question, where can I find a documentation that handle this kind of problematic? Thanks, Marco CRM 3.0 Small Business Edition is supported on SBS. There are two whitepapers that MS has published about upgrading 1.2 to 3.0 that can be downloaded her...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

Stuck
HI there, Seeing how iam stuck in this area of MSoffice, with no appearant link to other catigories i have to ask my question here concerning MSN explorer. I downloaded explorer 7.02 now i want to uninstall it, yet i cant there is no MSN explorer in my add remove programs, even when i click on show updates. I am running windows xp and there is no folder in widows directory leading me to MSN explorer, as some advice to type comands like : " %windir%\$NtUninstallie7beta2$\spuninst\spuninst.exe." to get rid of it, none of it seems to work at all... PAthetic of Microsoft to stoop to ...

change start page number #2
This is just killing me! I'm trying to edit a file I did a year ago, and I can't remember how to change the document's first page number. In other words, I have two publisher documents that need to be linked together. The first is 30 pages long, so I want the second to begin page numbering with page 31. I currently have it starting at 35, but I can't figure out how to change it. Pleeeeease help. Thank you. tools/options/general/ start pub with page.... -- "Display tolerance & kindness to those with less knowledge than you because there is ALWAYS someone ...

Crystal Reports Runtime 8.5 vs. 9.2
Our application installs and uses CR 9.2 files. Afterwards a 3rd party app relying 8.5 CR no longer works. Reinstall that app and then ours doesn't work. Reinstall ours and then that app doesn't work. And so on... Are one of these applications installing\using CR improperly or is it impossible to run 8.5 and 9.2 side-by-side? Shouldn't app coded against 8.5 work if the runtime is upped to 9.2? ...

Query design help requested
hello to all I'm fairly new at this! Here's my situation. qryA and qry B have identical structures. The PKey used in both is EmplID. I need to know how to construct qryC (again with the same fields) so that it contains ALL the data rows in qryA EXCEPT those also in qryB. Help greatly appreciated Victoria Use the unmatched query wizard (first dialog when you create a new query.) Use qryA as the first 'table', and qryB as the one to exclude. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips...

UK stuck with old version
This is most probably a pointless question, but here goes. I wondered why that in the UK the latest version is money 2005, where as if you live in the USA you can get money 2007. Is there many new things in money 2007 that are not in money 2005? NIK -- Remove nospam for personal reply if responding via newsgroup. I guess that the cost of converting M2007 to work for the UK was a lot greater than any potential sales! :-( See http://money.mvps.org/product/US/2007/Default.aspx for a list of M2007 features. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http:/...

Compare and Merge #2
I know that I cannot compare and merge a protected workbook but if I make a copy of the data in the spreadsheet and save it with a differnt name, it stills doesn't allow me to merge the two. ...

outlook 2002 attachments #2
i have an xp system with outlook 2002 which i use to collect my emails from my office. since i upgraded to 2002 i find that outlook hangs whenever i try to get my mail if there is an attachment with it. i also have a problem with certain plain emails with outlook running very slowly or reporting that it is not responding ...

Unable to hide columns #2
Hello All, I have a spreadsheet where on one of the sheets, if I try to hide more than 7 columns at one time, I receive the following message: 'cannot shift objects off sheet' Can anyone help on this. Graham I have experienced the same thing.. Always when I try to hide rows. As to my knowledge there is no solution. It is a bug in excel tha happens when you have hidden rows, and delete an visible row just abov the hidden part. If the hidden part has formatted areas, it wont let you hide that par again. I have searched everywhere, but there is no solution except for copyin the dat...

deleting items #2
Does anyone know what causes emails to disapear once they have been deleted from the sent items folder? There's no rules set! No warning comes up saying do you want to permanently delete this message! Any help would be much appreciated Cheers Steve steve <anonymous@discussions.microsoft.com> wrote: > Does anyone know what causes emails to disapear once they > have been deleted from the sent items folder? The very act of deleting them will make them disappear from the Sent Items folder. What are you really asking? -- Brian Tillman Smiths Aerospace 3290 Patterson Ave....

Sending the variable to a query as a criteria
Hello, I can send a value as a criteria to a specific query if it is a text of a textbox, combobax, label etc. But, i wonder if i can send a variable to a guery... I mean, such as... dim a as integer a= me.texbox3.value requery xxx... And in xxx, there should be column, for ex. xyz that has the criteria a.. But it does not work :( How can i do it? thanks... SupperDuck wrote: > Hello, > > I can send a value as a criteria to a specific query if it is a text > of a textbox, combobax, label etc. > > But, i wonder if i can send a variable to a guery... Create a us...

Rota spreadsheet
Hope someone can help as I'm at a total loss. I've got a rota that shows all personnel details, with shift patterns: 9am - 3pm 8am - 4pm 10pm - 4am etc (shift patterns subject to change and are individual) I have a column that shows them 'clocked on' and another for 'clocked off'. Now for legal purposes I need to know who is 'on' at regular intervals: 7 am 7.10 am 7.20 am 7.30 am so thinking logically I'd say if for the 7 am count if they are 'clocked in' but not 'clocked off' then count that person but I can't seem to find the r...

CRM integration #2
Hi, CRM is quite new (well very new!!) to me - and I am playing with Data Integration. I have the following to ask: I have a SQL Server Table with Customer Account Details. A master application feeds this table with information form a mainframe system. This SQL Table contains the master source of the data. The problem is that I need to do integration between this SQL Account table and CRM. Thus, I have to consider 3 operations that might be exercised on the Records namely: CRMAccount.Create() CRMAccount.Update() CRMAccount.MarkAsDeleted() If Data is inserted into the SQL Table, a ...

clip art missing #2
I have Publsiher 2007. Recently, I was using a friends computer who has Publisher 2003 and I created a .pub file. I opened the .pub file I created using Publsiher 2003 on my computer. Now my clip art is missing. How can I retrieve the clip art files? Were the images embedded? View, Pictures, do you have Detailed display enabled? -- Mary Sauer http://msauer.mvps.org/ "willy" <willy@discussions.microsoft.com> wrote in message news:95AC7F24-98F1-458C-B872-C6E78FAA683D@microsoft.com... >I have Publsiher 2007. Recently, I was using a friends computer who has > Pu...

Query subforms
I have 4 pages set up on one form with subforms (each based off of the same table). When I run the query I have to enter the [month] and [year] for each sub form. Is there a way to run a query over a page instead of subforms. I am having the same trouble with the report ...

time formats using Excel 2010 Query
Having problems with the time format using query with an excel worksheet... I just started using excel 2010 (upgrade from 2003). I have a worksheet with a column for time. The times are formatted as time (ex. 10:30:00 PM - not text). When I run a query all the times show up "1/0/1900 0:00". Then I try to format those times they all show up as 12:00 AM or ZERO. I can go back and change all the times to text, but this is a large worksheet....then they don't sort correctly as text versus a number... I did not have this problem with Excel 2003. I just want the times to show u...

Blinking Form
I have a Form with several sub forms. Sometimes when you move the mouse across the screen or such it blinks. We have tried it out on several computers and it does the same thing. Any Idea? -- Rose Rose Have a look at http://allenbrowne.com/ser-46.html This may be your problem. If not please post a bit more information. Peter Hibbs. On Mon, 23 Apr 2007 10:24:04 -0700, Rose <Rose@discussions.microsoft.com> wrote: >I have a Form with several sub forms. Sometimes when you move the mouse >across the screen or such it blinks. We have tried it out on several >computers a...

deleting sheets #2
i have a Excel document that i am trying to delete a sheet from... when i right click on the sheet, the "delete" option is grayed out same thing when i go to EDIT, DELETE SHEET i thought at first that there was some kind of protection, but when i go to TOOLS, PROTECTION it's also grayed out! any suggestions? It sounds like something is interfering with your toolbars. If it happens with all your workbooks, then I'd try resetting all my toolbars at once. (This will toss all your customizations!) Close excel windows start button|search|*.xlb Rename them to *.xlbOLD Start...

Equation help. simple for regular user probably
I have a basic graph 1,2,3,4,5.....going down in and a,b,c,d,e,f,c going across. EX. A B C D E F G ---------------------------------------------- 1 500 25 75 800 (200) 2 3 4 5 6 7 My question is how can I make an equation that Goes like this : Column A+B+D-E=G I showed an example above for on row but i wouuld wnat this to work for any row no matter what data I answer. I want this ewuation to work for any data entered for the whole sheet. I hope you understand and can help anyone. any ?'s post --...

Advanced Filter Causes Crash #2
I tried both of the ideas that were offered and got great results.. thanks so much -- flipnell ----------------------------------------------------------------------- flipnello's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1491 View this thread: http://www.excelforum.com/showthread.php?threadid=26552 Great! Thanks for letting us know. flipnello wrote: > I tried both of the ideas that were offered and got great results... > thanks so much! > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Testing #2
Sorry...That should have never happened -----Original Message----- From: R. Brown [mailto:rbrown_nospam@rbnetworking.net]=20 Posted At: Sunday, January 04, 2004 12:54 AM Posted To: admin Conversation: Testing Subject: Testing This is the first test message ...

append query with visual basic
I'm trying to execute an append query (qapdCopy&PasteProperties) with visual basic, but I get an error. Run-time error '' Syntex error in INSERT INTO statement. ================================================ Private Function Duplicate() Dim strInsert As String, strFrom As String Dim sstrWhere As String, Dim strSQL As String Dim db As DAO.Database Set db = CurrentDb strInsert = "INSERT qapdCopy&PasteProperties.* " strFrom = "FROM qapdCopy&PasteProperties " strWhere = "WHERE qapdCopy&PasteProperties.ChemicalID = " & [Forms]! [frmM...