Access 2003 Multiple Junction Tables on a form

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.
CudaGalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shy_talkCommented:
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
CudaGalAuthor Commented:
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
stevbeCommented:
< 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.