Hardware/Software association in Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
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.
 
Back
Top