I have a table called TCNO which is a table containing all the information concerning Microsoft patches. I have another table containing all the servers in my company and want to find a way to show what servers are installed with what TCNO's.
I have a many to many relationship with TCNO and Server so I need another table.
All the servers reside on Air Force Bases around the world.
How do I contruct my third table to link the others?
I have a drop down list on my main page that lists all the Bases and a command button for TCNO.
both your existing tables should have a primary key... if you don't already have an existing field in each table _guaranteed_ to be unique, add a field to each table:
in table design view right-click on the record-selector for each of these new fields (or an existing _guaranteed_ unique field) and make it the primary key.
r937 is correct: you don't need tbl.link.linkID Setting up the compound-key is more elegant and guards against duplicating install records for a tcno/server pair, but it takes more howto explanation. Both ways will work. I use the lazy way and guard against duplicate records with the list-of-whats-in and list-of-whats-out approach.
After you've got a primary key in tbl.tcno and tbl.server, the lookup wizard should do the job for tbl.link.serverID and tbl.link.tcnoID
You don't need a yes/no: the existance of an entry in tbl.link ties the serverID to the tcnoID i.e. the serverID/tcnoID pair in a tbl.link record means that serverID has that tcnoID installed.
Querying tbl.link for a serverID returns all installed tcnoID for the given serverID.
Querying unmatched in tbl.tcno.tcnoID and the tcnoID returned by the above query returns all non-installed tcnoID for the given serverID