Rabu, 25 April 2012

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

 

Glenn,

After my swift failure last night I took John's advice and looked at the
Northwinds database and learned one thing, which is that I do not know how
to reverse engineer as well as I thought I did. It is going to take me a
while to figure out what is going on and how they set the forms up to input
a many-to-many database. I am sure I will be back when I have an
intelligent question. Thanks for your help.

Bill Singer

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Glenn Lloyd
Sent: Tuesday, April 24, 2012 5:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Many-to-Many input form

Oops,

Of course you are right, John. I will blame my error on a temporary lapse.

Apologies to Bill for confusion.

Glenn

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 John
Viescas
Sent: April-24-12 6:18 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Many-to-Many input form

Glenn-

Actually, the subform needs to be bound to the junction table, with the
Parent
table providing a Row Source for a combo box that sets the second FK.

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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Glenn
Lloyd
Sent: Tuesday, April 24, 2012 10:50 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Many-to-Many input form

Bill,

If you need to track, even sometimes, more than one parent, then a junction
table between players and parents would be correct. You could maintain the
junction table using a form bound to the players table with a subform bound
to the parents table. The player id would be the field to use to relate the
subform to the form. You could have another subform on the same form to
display, for example, the teams or sports that a parent coaches. The
junction table probably should have additional fields to indicate
information about the parent child relationship. For example you might want
to have a field to indicate whether the parent is father, mother, or
surrogate (in the case of a Big Brother, for example)

You mentioned earlier that you might want to see your coaching history.
Having two different ids for yourself (8 and 27) would make that very
difficult. Once again a junction table between people or parents and sports
or teams would be needed. I would include extra fields for season and team.

Glenn

<mailto:glenn@rgservices.ca <mailto:glenn%40rgservices.ca>
<mailto:glenn%40rgservices.ca> > Description:
signature block_OTM

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
Singer
Sent: April-24-12 4:23 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Many-to-Many input form

Glenn,
That sounds like a many-to-many relationship with a "junction" table or a
3rd table that builds the association. So are you saying I should have a
junction table to associate players with parents? If so, how do you make
the input easy for the end user? Can they input the player and parents on
the same form or do I have to put them in on separate forms and then build
the association or relationship on a 3rd form? I could see how that would
get frustrating for the end user.

Bill Singer

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of argeedblu
Sent: Tuesday, April 24, 2012 3:12 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: 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
<mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> argeedblu
> Sent: Tuesday, April 24, 2012 2:30 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> > Viescas
> > Sent: Tuesday, April 24, 2012 2:05 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.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
>
>
>

[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

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

_____

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2411/4958 - Release Date: 04/25/12

[Non-text portions of this message have been removed]

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar