Quick Query - Query from a Newbie.

[Taki186]

I'm currently building a database to log computer hardware and software info.

I need to be able to design a query that will look across 10 different software fileds, for, let say the word "Photoshop",
but I'm struggerling to understand the correct expression to use and where to use it.

I've been trying to get this working for 3 days now, I'm new to Access 2002 and I'm sure this is easily acheived. Any help
would be greatfully recieved. In the meantime, I'm gonna keep on battling on.

Thanks in advance.

Confused from London.

 

[sambo]

what is the query being used for? Does the table contain 10 fields, or ten records in one field?
If 10 fields, could the word "Photoshop" possibly end up in any of 10 different fields? If so, you need to redesign the way your table is set up.

Let me know

 

[Taki186]

Thanks for the reply Sambo.

I have a software tbl set-up with 10 Software fields,
and from a custom form I propogate these fields with combo
box's containing the relevant software on the computer. (Photoshop, Illustrator, Quark etc..)

My problem is that I might have 'Photoshop' in software field 1 for the first entry, but, then have 'photoshop' in software field 4 for the next entry. So the word 'Photoshop' could end up in 10 different fields. What I need is a query expression that scans all 10 fields a reports only on word 'Photoshop' I've got a feeling that I might need to re-design my database.

What do you think........

 

[simongallop]

First: Your database really ought to be redesigned. You should have a table with an ID field and the computer details eg asset number, serial number etc.

Then you should have a table with Comp_ID and Software. This table would then have 10 records per computer (if there are 10 pieces of software) rather than your current setup of one record with up to 10 items. The advantage is that it is easier to search as you are only looking at one field and you won't have redundant fields / limitations. This means that if a machine has 1 piece of software, on your current setup you would have 9 fields being blank. By limitation this means that if a machine has more than 10 pieces of software what then? With the new format it does not matter.

If you are adamant that you want to stick to the current setup then you need to add a WHERE clause to your query. Presume that your 10 software fields are called S1, S2, S3 etc and the table is called software the SQL would look like this

SELECT *
FROM software
WHERE S1='Photoshop' OR S2='Photoshop' OR etc..

HTH