Solved

Access 2003 Multiple Junction Tables on a form

Posted on 2007-03-22
3
1,043 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
[X]
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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

717 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