ACCESS FEED: RE: [MS_AccessPros] Many-to-Many input form

Bill,

Both boys have the same real parent. You don't become a second person just
because you have two children playing sports. Imagine if you had ten
children.

Your player roster should be populated with a foreign key for the parent.
My general approach when I have people who play several roles is to have a
people table for all people and seperate role tables for each potential
role, parents, players, coaches. Then I would use a junction (Relationship)
table to relate each role to sports and teams.

Glenn

Sent from my Galaxy tablet.
On Apr 24, 2012 3:56 PM, "Bill Singer" <Bill.Singer@at-group.net> wrote:

> **
>
>
> Yes, but if a parent has two kids in the league then the parent will be
> listed two times.
>
> My son David has a parent, Bill (me) with and ID of 8.
> My son Spencer has a parent, Bill (me) with an ID of 27.
>
> Parents are the coaches of the teams and if I want to track the coaching
> history the system does not know that coach Bill, ID 8 is the same as coach
> Bill, ID 27. Especially if I coach two separate teams there is no way to
> see the merged history of my coaching.
>
> Maybe there is another way to handle it that I am not aware of.
>
> Coach Bill
> MN
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of argeedblu
> Sent: Tuesday, April 24, 2012 2:30 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Many-to-Many input form
>
> Bill,
>
> The relationship between parents and children is one to many. What you may
> be missing is a many to many relationship between children and sports --
> one
> child can play many sports/each sport has many players. That doesn't change
> the relationship between children and parents.
>
> Glenn
>
> Sent from my ASUS eee Transformer Prime Tablet On Apr 24, 2012 3:21 PM,
> "Bill Singer" <Bill.Singer@at-group.net> wrote:
>
> > **
> >
> >
> > In designing my sports team database I thought a one-to-many between
> > player and parents would be fine. It made it very easy to input the
> > information on a form and a sub form. Now I have two boys in
> > basketball and they both have a dad named Bill Singer (me) and now I
> > have double records. This becomes a problem when I try to track some
> > history information with for "Bill Singer".
> > I know how to make an association table to make the relationship a
> > many-to-many but how do I make a nice form that will allow me to input
> > the player and the multiple parents on the same form. The one-to-many
> > relationship made that easy, the many-to-many has my brain locked up
> > because I need to populate the t_parents and then the new association
> > table..and I would like to do it on the same form.
> >
> > Can you point me in the right direction.
> >
> > Bill Singer
> >
> > MN
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John
> > Viescas
> > Sent: Tuesday, April 24, 2012 2:05 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: RE: [MS_AccessPros] Re: Forms Population Question
> >
> > Terry-
> >
> > Then I don't understand what you're trying to do!
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007
> > Inside Out Building Microsoft Access Applications Microsoft Office
> > Access 2003 Inside Out SQL Queries for Mere Mortals
> > http://www.viescas.com/ (Villefranche-sur-mer, France)
> >
> > ------------------------------
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > Terry Olsen
> > Sent: Tuesday, April 24, 2012 7:20 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Re: Forms Population Question
> >
> > During initial data entry, the Badge is not in tblCurrentPersonnel,
> > but will be once entered.
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> > <JohnV@...> wrote:
> > >
> > > Terry-
> > >
> > > If Badge is in tblCurrentPersonnel, you can use the AfterUpdate
> > > event of
> > the
> > > Badge control to open a recordset on tblCurrentPersonnel for records
> > > with
> > the
> > > same Badge number. Grab the first record (if any), and populate the
> > fields
> > you
> > > want. You can leave the text boxes unlocked so the user can change
> > > stuff
> > after
> > > your code loads the "default" values.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out Microsoft Office Access 2007
> > > Inside Out Building Microsoft Access Applications Microsoft Office
> > > Access 2003 Inside Out SQL Queries for Mere Mortals
> > > http://www.viescas.com/ (Villefranche-sur-mer, France)
> > >
> > > ------------------------------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > Terry Olsen
> > > Sent: Monday, April 23, 2012 3:09 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Re: Forms Population Question
> > >
> > >
> > > The record source of the form is tblCurrentPersonnel. All controls
> > > are
> > bound
> > > controls. Badge, ID, PIN, Name are text boxes. Currently, I enter
> > > the
> > same
> > data
> > > into each of them, varying only Name to a slight degree.
> > > What I'd like to have the form do is:
> > > 1) I enter a value into badge
> > > 2) I move to another control on the form and
> > > 3) PIN, ID, and Name values are populated from Badge
> > > 4) I'd also need to have Name be editable as I still need to add the
> > > concatenation.
> > > Thanks
> > > Terryomsn
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> > <JohnV@>
> > wrote:
> > > >
> > > > Terry-
> > > >
> > > > OK, what's the Record Source of the form, and please explain how
> > > > you're
> > using
> > > > Badge, ID, Name, and Pin and how you expect some values to
> > autopopulate.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out Microsoft Office Access
> > > > 2007 Inside Out Building Microsoft Access Applications Microsoft
> > > > Office Access 2003 Inside Out SQL Queries for Mere Mortals
> > > > http://www.viescas.com/ (Villefranche-sur-mer, France)
> > > >
> > > > ------------------------------------
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > Terry Olsen
> > > > Sent: Sunday, April 22, 2012 7:21 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: [MS_AccessPros] Re: Forms Population Question
> > > >
> > > >
> > > > Thanks, Duane. I am not storing duplicate values in the table.
> > > > Terryomsn
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Duane"
> > <duanehookom@
> > >
> > wrote:
> > > > >
> > > > > Terryomsn,
> > > > >
> > > > > I think John was asking for more information regarding your tables.
> > Are
> > you
> > > > really storing multiple duplicate values in the same table? Is the
> > > > form
> > bound
> > > to
> > > > some type of a transaction table? Is there a separate employee table?
> > > > >
> > > > > How about provide more context to what you are trying to do so
> > someone
> > can
> > > > give the best help?
> > > > >
> > > > > Duane Hookom
> > > > > MS Access MVP
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Terry Olsen"
> > <terryomsn@>
> > > > wrote:
> > > > > >
> > > > > > Thanks for your response.
> > > > > > I currently have a bound control for each of four employee
> > > > > > fields
> > Badge,
> > > ID,
> > > > Name, and Pin. While using the form, I've found that Badge needs
> > > > to
> > entered,
> > > and
> > > > 2 of the 3 other values are always the same and the third (Name)
> > > > varies
> > only
> > > by
> > > > the concatenation of a 2 character code. That works fine if I
> > > > continue
> > to
> > > enter
> > > > the same value 3 times.
> > > > > > While trying alternatives, I added 3 text boxes intending to
> > display
> > the
> > > > value entered in Badge in ID, Name, and PIN after the Badge
> > > > control had
> > been
> > > > populated and the user moved to another control on the form.
> > > > > >
> > > > > > Do I need to have these 3 additional text boxes or is it
> > > > > > possible
> > to
> > > > populate the original bound controls in the same manner?
> > > > > >
> > > > > > I've been studying the Access 2010 Inside Out book. While I
> > > > > > have
> > found
> > > > information on what the control and form events are, I haven't
> > > > found a discussion yet that pertains to populating forms in this
> > > > manner. I am
> > also
> > > > looking for how and when, or if I can, reference the values in the
> > control
> > and
> > > > record buffers.
> > > > > > Thanks
> > > > > > Terryomsn
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> > <JohnV@>
> > > wrote:
> > > > > > >
> > > > > > > Terry-
> > > > > > >
> > > > > > > How is the table containing those three fields related to
> > > > > > > the
> > table
> > > you're
> > > > > > > editing? Access might fetch that info for you automatically
> > > > > > > if
> > you
> > > include
> > > > that
> > > > > > > table in your form's Record Source, and it's linked on Badge.
> > > > > > >
> > > > > > > John Viescas, author
> > > > > > > Microsoft Office Access 2010 Inside Out Microsoft Office
> > > > > > > Access 2007 Inside Out Building Microsoft Access
> > > > > > > Applications Microsoft Office Access 2003 Inside Out SQL
> > > > > > > Queries for Mere Mortals http://www.viescas.com/
> > > > > > > (Villefranche-sur-mer, France)
> > > > > > >
> > > > > > > -----------------------------------
> > > > > > >
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > Terry
> > > Olsen
> > > > > > > Sent: Saturday, April 21, 2012 6:45 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > Subject: [MS_AccessPros] Forms Population Question
> > > > > > >
> > > > > > >
> > > > > > > For a new entry, I have a data entry form that I want to
> > > > > > > reduce
> > > keystrokes
> > > > on.
> > > > > > > In one bound control, badge, after initial data entry, I
> > > > > > > want to
> > > populate
> > > > three
> > > > > > > other text boxes, txtEmployeeId, txtEmployeePin, and
> > txtEmployeeID.
> > > After
> > > > all
> > > > > > > entries on the form are complete, I want to populate fields
> > > > > > > in
> > the
> > DB
> > > > table
> > > > > > > columns for each of them.
> > > > > > > Which event do I use?
> > > > > > > What persists the data to the relevant DB record?
> > > > > > > Thanks
> > > > > > > Terryomsn
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> > _____
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.1913 / Virus Database: 2411/4956 - Release Date:
> > 04/24/12
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2411/4956 - Release Date: 04/24/12
>
>
>