Access 2003 Multiple Junction Tables on a form
Posted on 2007-03-22
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.