Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: Large Query: Need your input please

    The way the main DB form/table was designed, was to have each record store data about certain PCs. 25 fields in each record are used to store software (Software IDs are is held on a seperate table and refrernced by a combobox on the main form). So while SoftwareID1 on one record might be "McAfee", on another it might be "AutoCAD".

    What I would like to do is have a query written so that somone can choose which peice of software they would like to find (from a combobox), and it will return the PC(s) which it exists on REGARDLESS of which softwareID field it's sitting in.

    Any suggestions? I'm not sure howto go about something like this.

  2. #2
    Join Date
    Mar 2004
    Brighton, UK
    Hmm that's going to be a bit of a pain I think due to the design of the data structure. If I understand it right, the main table stores info about each pc, and each piece of software installed on it is stored in SoftwareID1, SoftwareID2 etc so you can store up to 25 different programs that are installed on a pc.

    It might be better to create a new table, called eg tblPCSoftware or something, which has a primary key (eg PCSoftwreID) and also fields PCID and SoftwareID, which will allow you to have a subform in your main form where you can set up the individual pieces of software on each PC. Don't know if you've considered that but it's an idea.

    Otherwise, with your current structure, you could create a query which references a combo (say called SelectedSoftwareID) on a form where the user selects the Software. The query would then have the PC table in it, with the first column SoftwareID1, and the criteria for that column would be


    The second column would have SoftwareID2, and the criteria for that column would be the same as the first, but in the OR row, then the third would go in the next OR row etc etc etc. Ouch what a pain in the arse! You could create the query string in vba doing a FOR NEXT thingy, eg

    For x = 1 to 25

    strCriteria = strCriteria & " (SoftwareID" & x & " = Forms!MyForm!SelectedSoftwareID) OR "


    and make a SQL string by

    strSQL = "Select PCID from tblPC where " & strCriteria

    (though you'd need to remove the final OR from the strCriteria)

    Good luck

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    I heartily second restructuring your table schema. You'll save yourself immense headaches in the future.

  4. #4
    Join Date
    Mar 2004
    Thanks for the replys,

    It wasn't the best way to structure the DB, but I didn't create it and I cant really modify it at this point The good thing though is that the software IS stored in a seperate table.

    Again, thanks for the suggestions, trying them now (and yes it is a headache

Posting Permissions

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