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.
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)