Results 1 to 7 of 7

Thread: Design issue.

  1. #1
    Join Date
    Nov 2002
    Posts
    32

    Unanswered: Design issue.

    I have a table called TCNO which is a table containing all the information concerning Microsoft patches. I have another table containing all the servers in my company and want to find a way to show what servers are installed with what TCNO's.

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

    All the servers reside on Air Force Bases around the world.

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

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

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tbl.link:

    linkID, autonumber
    tcnoID, lookup on tbl.tcno primary key
    serverID, lookup on tbl.server primary key

    combo for the servers seems a good idea.

    for tcnos, why not 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.

    izy

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for the link table, make the primary key a compound key consisting of tcnoID and serverID -- in other words, do not declare linkid autonumber, it is superfluous

    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Nov 2002
    Posts
    32
    How do I designate whether a server has a TCNO installed or not. I guess I need a Yes/No field somewhere but am unsure of how or where to implement.

  5. #5
    Join Date
    Nov 2002
    Posts
    32
    How do you make a lookup field a primary key, it is null in my Install table.

  6. #6
    Join Date
    Nov 2002
    Posts
    32
    bump

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    both your existing tables should have a primary key... if you don't already have an existing field in each table _guaranteed_ to be unique, add a field to each table:

    tbl.tcno.tcnoID, type:autonumber

    tbl.server.serverID, type:autonumber

    in table design view right-click on the record-selector for each of these new fields (or an existing _guaranteed_ unique field) and make it the primary key.

    tbl.link
    r937 is correct: you don't need tbl.link.linkID Setting up the compound-key is more elegant and guards against duplicating install records for a tcno/server pair, but it takes more howto explanation. Both ways will work. I use the lazy way and guard against duplicate records with the list-of-whats-in and list-of-whats-out approach.

    After you've got a primary key in tbl.tcno and tbl.server, the lookup wizard should do the job for tbl.link.serverID and tbl.link.tcnoID

    You don't need a yes/no: the existance of an entry in tbl.link ties the serverID to the tcnoID i.e. the serverID/tcnoID pair in a tbl.link record means that serverID has that tcnoID installed.

    Querying tbl.link for a serverID returns all installed tcnoID for the given serverID.

    Querying unmatched in tbl.tcno.tcnoID and the tcnoID returned by the above query returns all non-installed tcnoID for the given serverID

    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •