Solved

Access 2003 Multiple Junction Tables on a form

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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

19 Experts available now in Live!

Get 1:1 Help Now