Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    21

    Unanswered: VB and table structure help

    First let me outline my problem.

    I am creating a database that will catalogue all of the computers at my office. It will also catalogue what software is installed IN them and what Software is installed ON them.

    I am having no problems with the hardware side of things. The software is a different matter altogether!

    I have a table called "PC Base Unit", "Software" and "LicenceTable".

    As there are many software titles and many computers I have the link box "LicenceTable". The records in this table are dynamially generated using a script which adds as many rows per software title as there is in the "MaxLicenceNumber" field. This means that if we have 5 versions of MS Excel (with the ID MSE00) then the "LicenceID" field of the Licence table will have:

    Licence ID | PC Asset Tag

    MSE00-001 | GEN0101
    MSE00-002 | ITS0100
    MSE00-003 | other PC Id's
    MSE00-004 |
    MSE00-005 |

    in it.

    There is another field in this table which is used to link the LicenceTable to Software. That is the software ID.

    Now, as i understand it in the Software field it should automatically put the PackageID value that the LicenceID value realates to, like this:

    Licence ID | PC Asset Tag | PackageID

    MSE00-001 | GEN0101 | MSE00
    MSE00-002 | ITS0100 | MSE00
    MSE00-003 | other PC Id's | MSE00
    MSE00-004 |
    MSE00-005 |

    This then should allow me to run a query that will be able to determine the name and version (stored in the software table) of the software on each computer eg.

    On GEN0100 - Microsoft Excel 2000.

    This is not happening! When I open the LicenceTable I have the generated (using INSERT INTO) LicenceID and whichever PC ID numbers I have entered into the PCAssetTag field. But the PackageID field is empty.

    I cannot work out what the problem is... can any of you fine people help??

    Regards

    Joe

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Access will not automatically populate the table with the package based on the license. You need to do this either through a form or through code.

    I would imagine that in your Softwaretable you have license number and package name.

    If this is the case you do not need to include the package name in your License table at all.

    You could instead work this out at query time by creating a relationship from your License table to your Software table on license number.

    Then drag the field package name into the query and it will automatically populate with the licensed software name.

    HTH

  3. #3
    Join Date
    Nov 2002
    Posts
    21
    Originally posted by dynamictiger
    I would imagine that in your Softwaretable you have license number and package name.

    If this is the case you do not need to include the package name in your License table at all.

    You could instead work this out at query time by creating a relationship from your License table to your Software table on license number.

    HTH
    Thank you for getting back to me, but in my Software Table i have the following fields:

    PackageID
    PackageVendor
    PackageName
    PackageVersion
    PackageOther
    PackageSerial
    MaxLicenceNumber

    In my Licence Table I have:

    Licence
    PCAssetTag
    PackageID

    I only put the Package ID field into the Licence table so that I could link them together. The link appears and I enforce referential integrity, however when I try a query as you suggested it gives me no results when I know that there should be.

    I just cannot see what is wrong!

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    Attached as Access 2002 file
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2002
    Posts
    21
    That connects properly which is great, but it means that the PacakgeID values that I have (MSE001 etc) cannot be copied into the Licence table which is required.

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    You may need to modify the field type to make that happen. That was just a quick how to, so you can get the idea.

  7. #7
    Join Date
    Nov 2002
    Posts
    21
    Thanks very much. I will have a big play around, see what we can cobble together

    Neo

Posting Permissions

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