Solved

Access 2003 Multiple Junction Tables on a form

Posted on 2007-03-22
3
1,036 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now