Search Contents in a variety of fields (1 Viewer)

[rgreene]

Hello Everyone,

I have a database that our department uses (IT department). In this database we track PC specs (HD size, RAM...). We also track software installed on that PC (Win '95, 2000, NT Bridge, INsyte...) Now on the software I have the software name and a check box next to it. If the software is loaded we check the box, if it's loaded it's left unchecked. Here lies my question. If I want to see who has INsyte for instance I have a query setup that runs and grabs all the records. I have several of these software fields and instead of having a seperate query for each I want to have a query asks me which FIELD (ie INsyte, Bridge, WinCoins) I want and then run and find the records that have the software installed that I chose.

Does this make sense? If not let me know and I'll try to explain better.

Thanks in advance.
Rick

 

[Hayley Baxter]

Hi Rick

I think what you are asking is how can you create searches on multiple fields using only 1 query. I use QBF (Query by Form) to search on multiple fields where you set up a form with all the fields you want to pull a search on and the search results are displayed in a query although you can change this to open another form or a report to view the results. If this sounds like what you are after and you would like some help with this let me know.

Hay

 

[rgreene]

Thanks Hayley,

I've never heard of a Query by Form (QBF). Once I have the QBF in place will I be able to have it ask me which field I want it to check? If so then I think we are on the right track here. Any assistance you can give me on the QBF would be greatly appreciated.

Thanks,

 

[Graham T]

Rick

What may be causing you problems is the design of your tables, although you have not exactly indicated how these are structured. It is just when you mention that you have several of these software fields...

My thoughts would be that you have a Many-to-Many relationship present.

One PC can have Many types of Software installed on it. Many of the Software could be installed on Many of the PC's.

If this is the case then you would need three tables -

tblPC's:
PC_ID (Primary Key)
PCName
Spec's etc

tblSoftware:
Software_ID (Primary Key)
SoftwareName

tblLINKPC_Software:
PC_ID (PK)
SoftwareID (PK)

You would then create two One-To-Many relationships between the tables and the LINK table.

You will then be able to query each type of software to return which PC has this installed by only querying one field.

I hope this helps

Graham