Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2003 Multiple Junction Tables on a form

Posted on 2007-03-22
3
1,039 Views
Last Modified: 2013-11-29
I am new to Access 2003 and learning on the fly however I have taken a basic database class.  Here's my situation.  I have been tasked with building a Price Book database for our sales team (from a spreasheet) that contains Software Items,  Service Items and Maintenance Items  that we sell.  I have the database built.  I have tables for Software Items, Service Items and Maintenance Items and several more such as categories, product name & type, all of which are now populated.  

Some Software Items have Associated Service Items as well as Maintenace Items.  Some Software Items use the same Support and Maintenace items so I determined that I needed to build 2 Junction tables to represent the Many to Many situations of Support and Maintenace Items.  I need to know how to populate these Junction tables with appropriate records.  All tables currently have their assocation listed in a column as long as it is one to one.

As well, I need a form that I can search for a specifc Item and have it display the Software Item, Service & Maintenance items associated with it, allowing the user  to change or add associations.  I built a form that shows all of those and that is how I found out that I needed Junction tables as it would not let me enter the same Service item on 2 item numbers.  

So how do I add the data to the Junction table?  How do populate the junction table for new codes without having to know the record numbers?  My initial hope was to build forms that the users could Add and Edit the data then build a Switchboard for them to access those forms.

I have been all over the internet trying to find solutions with no luck. I am exhausted and freaked out.  I really thought this would be a fairly simple project...  I hope you can help.
0
Comment
Question by:CudaGal
3 Comments
 
LVL 2

Assisted Solution

by:shy_talk
shy_talk earned 200 total points
ID: 18776136
Create a two-column junction table, and in each field's properties, on the look-up tab, specify a two-column look-up to the id and the name field as a combo box or list box. If you like, and you know the names are unique, set the id column width to zero, so only the name displays in the drop-down.

Also, create a unique, two field index on the pair of columns. This will prevent you from inadvertently creating duplicate associations.

Go through the two column junction table in display mode, and for each association, select an item from the first table using the first column's look-up combo box, then select its associated item from the second table using the look-up combo in the second column. You will find that you are able to associate more than one item from one table with more than one item from the other.
.
Hope this helps.
0
 

Author Comment

by:CudaGal
ID: 18776475
Sorry to be so dense but my current junction table contains only three fields the PK and the two FKs.  I managed to attach a lookup and it works fine for the first FK but it returns the same values on the second FK.  I'm sure I lost something in translation.

BTW - how do you create a unique, two field index on a pair of columns?

Thanks for your help!
0
 
LVL 39

Accepted Solution

by:
stevbe earned 300 total points
ID: 18782118
< contains only three fields the PK and the two FKs>
this is the way I do it all the time.

I do not create lookups directly in the table ... ever, slow pig iof a resource hog :-)

So ... here is the set up ... you have the Products as you main form and you have one subform that is bound to the junction table for Services with the Link Child and Link Master fields being the ProductID. You do not need to display the ProductID field on the subform. For the Services you use a combobox (this is the equivalent of the tbale lookup but is the ciorrect way to do this) that prsents the list of services.

now repeat for the Maintenace ... it needs it's own subform etc. Lets see if we can get this up and running and then we can add the *Search* functionality which is fairly easy (in fact the combobbox wizrd can do this for you but I don;t like the code it writes :-)

Steve
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question