Dennis,
M2M's are ugly but a fact of life. I've done what you have suggested. I suggest that you make the combination of server_id and software_id a unique constraint to keep out dupes.
In Access drop down lists are called combo boxes. You can base them on queries. Just make sure the server_id and software_id fields are first in their respective select statements that populate each of the two combo boxes. The actual verbiage should be in the second fields of these queries. If you want to show the verbiage instead of the id number, make the Column Count 2, Bound Column 1, and make the Column Widths something like 0"; 2". Making the first column zero hides the id numbers.
HTH,
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
I am designing a database our software/hardware inventory and there is a
many-to-many relationship between 'server' and 'software' table. So I
created an association table between them to resolve this. While I believe
this is the right thing to do, I am wondering how we are going to populate
this association table.
It seems to me that we have to create a separate screen for this table to
have the users manually enter the data. Right now this table has only three
columns - ( server_software_id, server_id, software_id ).
Bonus question - if we are going to use Access to manually enter data, is
there any way we can avoid entering the raw numbers ( the association table
columns are all numeric ). I remember with Oracle Forms you can set up some
kind of drop down list where you pick the text value and the ids get
populated behind the scene. I am not a fan of Access, can we do something
similar?
TIA
Dennis Meng
Database Administrator
Focal Communications Corp.
