Hardware/Software association in Form

[iTanas]

Hi,
I have a database which consists of:
1. A Hardware table that lists all the workstations.
2. A Software table that consists of all different software titles.
3. A Relationship table (something I created after reading these discussion
groups) which has 3 fields: RelationshipID, SoftwareID and HardwareID and it
basically has a list of all the software installed on each computer such as
here:

RelationshipID SoftwareID HardwareID
1 OfficeXP Computer1
2 WindowsXP Computer1
3 Winzip9 Computer1
4 OfficeXP Computer2
5 Windows2000 Computer2
6 Winzip9 Computer2
7 NortonAV10 Computer2
.....
etc

I have a form which displays records for each Hardware, however I'd like to
also include something like a listbox that would list the softwares installed
for that particular machine, pulling the information from the Relationship
table? Could someone please advise me on how to accomlish this. Thanks a
lot for your help.

 

[Douglas J. Steele]

Assuming that the HardwareId is found in a text box named, say,
txtHardwareId, you'd want the RowSource for your list box to be something
like:

SELECT Software.Field1, Software.Field2
FROM Software INNER JOIN Relationship
ON Software.SoftwareID = Relationship.SoftwareID
WHERE Relationship.HardwareID = Me.txtHardwareId

How you use this, though, depends on how you've designed your form. If you
scroll from computer to computer, you'd refresh the list box in the form's
Current event.

 

[Jim Ory]

iTanas,

If I understand your question correctly, you have one too many tables. The
Relationships table seems to be adding complexity where it is not needed.

Your Hardware table should have a unique index. Your table should look like
this:

ComputerID
ComputerName
ComputerLocation
(and whatever you want to describe this Computer)

Your Software table should have a unique index. Your table should look like
this:

SoftwareID
ComputerID
SoftwareTitle
InstalledDate
(etc.)

Your main form will be based on your Hardware table; a sub-form will be
based on a query of the Software table. The sub form can be in Data sheet
view, so it will look like a list. Link the sub form with the main form via
ComputerID. The Wizard does a good job at this.

As each computer is displayed in the main form, the sub form will display
the software installed on that machine.

 

[Douglas J. Steele]

No, Jim, 3 tables are required. This is a classic example of a many-to-many
relationship (one computer can have many pieces of software installed on it,
each piece of software could be installed on many computers). To resolve
many-to-many relationships, you use a third "intersection" table.

Your way, you'd have to have details about each piece of software (Title,
etc.) stored redundantly.

 

[Jim Ory]

Doug,
Thanks for the many-to-may method advice. I'll hit the books a littler harder.