Linking Tables

[JCDugas]

Hi,

I'm not sure what the best approach to this is but this is what I want to do.

I have two tables, one table contains all PC's my company operates and the other table contains software licenses we own. I want to associate a license with a PC.

Example: If I have 100 MS Office licenses I want to associate 1 license per PC that has it installed.

What's the best way to set this up?

Jeremy

 

[nberryman]

What is the structure of your tables?

Post these and we can help



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[JCDugas]

Is there an easy way to do this?

 

[JCDugas]

\documents\MIS\Administration\MISInventory\Inventory.mdb Monday, December 22, 2003
Table: tblLicenses Page: 1
Columns
Name Type Size
nID Long Integer 4
Manufacturer Text 50
Title Text 50
Version Text 50
Qty Integer 2
PurchaseDt Date/Time 8
Comment Text 250
GenericName Text 50
Retired Yes/No 1
Table Indexes
Name Number of Fields
nID 1
Fields: Ascending
\documents\MIS\Administration\MISInventory\Inventory.mdb Monday, December 22, 2003
Table: tblWorkstationHardware Page: 2
Columns
Name Type Size
idxWorkstationID Long Integer 4
txtStatus Text 10
txtReplacementYear Text 10
Branch Long Integer 4
Department Long Integer 4
txtBiosName Text 10
txtDescription Text 50
txtSNCPU Text 25
nModelID Long Integer 4
dtPurchaseDate Date/Time 8
txtServiceDates Text 50
txtMemUpgrade Text 15
txtOS Text 15
txtCDROM Text 3
txtModem Text 40
txtNIC Text 40
linWallPort Long Integer 4
txtOffice Text 10
txtMisc Text 255
txtProblems Text 50
txtComments Text 60
ynPNAgentInstalled Yes/No 1
ynAntiVirusProtect Yes/No 1
Relationships
tblWorkstationHardwaretblUsers
tblWorkstationHardw tblUsers
idxWorkstationID idxWorkstationID
Attributes: Not Enforced
RelationshipType: One-To-Many
tblWorkstation-ModeltblWorkstationHardware
tblWorkstation-Model tblWorkstationHardw
nModelID nModelID
Attributes: Not Enforced
RelationshipType: One-To-Many

 

[BGrego]

Simply create a third table i.e. tblWorkStationLicenses that with the following fields and types:

idxWorkstationID (long int)
nID (long int)

Now, this table can used to store a workstationID and a license id, thereby joining the two tables. You can also associate workstations to multiple licenses by entering the same workstationID into the table with a different licenseID, or what ever, but this is how you do it.