Many to Many issue

[sdonovan]

I have a table called TCNO which is a table containing all the information concerning Microsoft patches. I have another table containing all the Air Force bases where we have a server installed. I want to find a way to show what servers have what TCNO's installed.

I have a many to many relationship with TCNO and Base so I need another table.

How do I construct my third table to link the others?

I thought of the following:
tcnoID, lookup on tbl.tcno primary key
Base_Name, lookup on tbl.Base primary key
and Installed YES/NO

How can you have lookup field tcnoID and Base_Name primary keys? They are null values in my new table which I named tbl.installedTCNO.

I have a drop down list on my main page (switchboard) that lists all the Bases and a command button for TCNO.

On the TCNO form I wanted two lists: what's installed, what's not for the current server... capture a double-click on an entry in either list to switch it to the other, or double click and bring up another form that has a check box "Installed" YES/NO.

Regards,

Steve

 

[llkhoutx]

What you need is a "junction table." It has two indexed columns, one containing the tcnoid and one containing the baseid. For each tcnoid, you'll have multiple baseids.

 

[sdonovan]

Can you elaborate on indexed columns? I have a lookup for both tcnoID and BaseID, should I have used insert/lookup field?

 

[llkhoutx]

In this context, an indexed column is merely a column in a table which has it's index property set to "Yes, duplicates allowed."

One solution, using this idea is to create a combobox on this table, filtering by tcnoid, which then gives all the baseids for that tcnoid.

 

[sdonovan]

One solution, using this idea is to create a combobox on this table, filtering by tcnoid, which then gives all the baseids for that tcnoid.

I like your idea.

However, I have a Combo Box on the form that displays the Base Name. I don't see where I can "filter by tcnoid" It is bound to Base_Name. My main form has a combo box with base_name and a command button for TCNO.

I want to select a base from the combo then click on TCNO and open TCNOInstall form where I view two list boxes. One in green where all the TCNO's are installed for that server. The other list box in red that displys TCNO's that are not installed.

Have a double click event on the one box to move a TCNO to the installed List box.

I know I am asking too much!

My form comes up with one list box so far but it doesn't display the correct data. It show all TCNO's My lookup fields in TCNOInstall table don't pull the TCNO's that are selected in the lookup fields.

 

[llkhoutx]

your list box query should have a field whose criteria is the combo box. Requery the list box on AfterUpdate of the list box.