Results 1 to 4 of 4

Thread: IT Database

  1. #1
    Join Date
    Jul 2006
    Posts
    108

    Unanswered: IT Database

    so i'm building a "quick and dirty" asset tracking database...

    i'm trying to keep it simple so that it's easy to maintain (as little VBA as possible), but i've ran into a snag...


    here is what i'm trying to accomplish:

    Tbl_Departments
    ID
    DepartmentName


    Tbl_Software
    ID
    SoftwareName
    SoftwareVendor
    etc...


    Tbl_Computers
    ID
    DepartmentID
    ServiceTag
    Manufacturer
    etc...




    so obviously, a computer in Department A needs a set of software X, and Department B needs a set of software Y and so forth... so to solve this problem i added another table:


    Tbl_DepartmentSoftware
    ID
    DepartmentID
    SoftwareID
    Notes



    that works fine, now i know what software goes with what departments and etc... and i've got the form designed for that quite well... (detail form of the department with a list view of the department software)


    Now, the tricky part:

    what i want to have next is on the computer detail form, i'd like to list out all of the software required for that department, AND have a checkbox next to each software where i can simply check-off whether on not the software has been installed...

    now i'm stuck... i know i need another table that should link the ComputerID, and either

    A) The Tbl_Software.SoftwareID, or
    B) The Tbl_DepartmentSoftware.ID

    in a many to many format... but i'm unsure of which one i should use... they both seem plausible.


    I'd like to not have to code a bunch of VBA behind the scenes, and i've noticed that some access queries will let you perform edits/updates/inserts from within the query even though it is a "select" query...

    can anyone help me out?
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have software related to departments, and computers related to department...

    shouldn't software be related to computer?

    or don't you care that there are 9 computers in department M but only 6 of them have McAfee installed?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    108
    My Though process is, that when you create a department, you list out the software that is required for a computer contained in that department...

    then, when a computer is added, it should show the software list that is required for it's department, and each software should have a checkbox next to it stating whether or not the software has been installed...


    does that make sense?
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Personally, I think you should change your table layout a bit... Something like:

    Code:
    Tbl_Departments
    ---------------
    DepartmentName    [Primary Key]
    
    
    Tbl_Software
    ------------
    SoftwareName      [Primary Key]
    SoftwareVendor
    etc...
    
    
    Tbl_Computers
    ------------
    ID                [Primary Key]
    DepartmentName    [Foreign Key]
    SoftwareName      [Foreign Key]
    ServiceTag
    Manufacturer
    etc...
    Then I'd do as r937 suggested and have each computer, linked through their DepartmentName foreign key to their department... And their software, again linked through their foreign key.

    You could even go so far as to eliminate Department altogether (provided you didn't miss an 'etc...' off the end of that table too?), and simply use a SELECT statement to retrieve those computers with a 'DepartmentName' that matches 'Administration' (for example).


    On the other hand, if you're happy enough with your present database layout, and it does the job you're after, I would suggest:

    - Adding a field to your 'Tbl_DepartmentSoftware' table, that is of yes/no (boolean) type, called 'Installed' (or whatever you want), which defaults to 'no' (or 'FALSE').

    - Adding a 3 column listbox to your form (which it sounds like you already have... or are you refering to the actual records appearing in the footer [for example]?), and populating it with:
    Tbl_DepartmentSoftware.[DepartmentID]
    Tbl_DepartmentSoftware.[SoftwareID]
    Tbl_DepartmentSoftware.[Installed]

    - Setup a button, with a teeny tiny bit of VBA (about 15 words or so), accompanied with a small SQL statement to update the table (about 15 words or so)... The button, when clicked, will detect which item is selected in the listbox, and change the 'Installed' value from 'no' to 'yes', and vice versa. The listbox will also be updated instantly to indicate the change.

    To achieve what you're after without using any code whatsoever, isn't really possible.


    Add the new field to the table, and create a listbox on your form that contains only the three fields I mentioned above (in that order). Then create your button, go into its properties, and at the bottom you should see, 'On Click', use the dropdown box to input '[Event Procedure]', and then click the little '[...]' button next to it... This will open the VBA editor for you, and will input for you:

    Code:
    Private Sub myButtonName_Click()
    Then on the line immediately after that, copy and paste:

    Code:
    Dim strSQL As String
    
    strSQL = "UPDATE Tbl_DepartmentSoftware " & _
             "SET [Installed] = TRUE " & _
             "WHERE [DepartmentID] = '" & Me.yourListboxName.Column(0) & "' " & _
             "AND [SoftwareID] = '" & Me.yourListboxName.Column(1) & "'"
    
    'Column(2) is based off the order I gave for the 3 columns in the listbox.
    '(2) refering to the third, 'Installed' column.
    If (Me.yourListboxName.Column(2) = 0) Then
        CurrentDb.Execute (strSQL)
    Else
        CurrentDb.Execute (Replace(strSQL, "TRUE", "FALSE"))
    End If
    
    Me.yourListboxName.Requery
    Change the bits in blue to whatever name you gave your listbox... Save it, and give it a whirl.


    Should do exactly what you're after.


    Any issues, let me know, be here all day.
    Last edited by kez1304; 09-01-11 at 07:44.
    Looking for the perfect beer...

Posting Permissions

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