Re: [MS_AccessPros] Replacing multivalue fields

 

John,

Thank you very much for your assistance and for teaching me about the dangers of multivalue fields. It seemed like a convenient shortcut originally, and now I know better.
Anyway, I have gotten my many-many relationship working properly and have utilized it in my forms and reports.

There are a few other places where I used multi-value fields. I'm looking to see if I can clean these up.

Within my Projects table, I have fields to define that project, and then a multi-value field for "Related Projects". This field is populated using a combo box with a row source of Projects.ID and Projects.ProjNo. A table linking to itself. (For example, a project might be replacing a motor. A related project would be the work on the item that the motor drives.)
How would I re-create this using a junction table? Potentially:
    ID (PK, auto#)
   ProjID (PKfrom tProjects)
   RelateID (New field within tProjects?)
So on my relationships table, I would have two joins between a pair of tables?

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Kacy-

Using a form / subform is the correct way to do this.  Your outer form can edit either Projects or Needs.  If you edit Needs on the outer form, then the Record Source of the subform should be a query that links the new tProjNeed table with the Projects table on ProjectID.  Include all the fields from tProjNeed and the description field from Projects.  Do not include the ProjectID field from Projects.  Build a Continuous or Datasheet form on that Record Source and use a combo box that does a lookup to the Projects table to set tProjNeed.ProjID.  The Link Master Fields should point to the Need ID in the outer form, and the Link Child Fields should point to the NeedID on the subform.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

 

__._,_.___

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___