Update set where like not working

[danb]

Hi,

Probably a very simple problem to fix but I can't seem to get it working.

I have a table with a text column called 'OperatingSystem' - containing data like this: 95/98/2000/XP

I also have some Yes/No columns called IsOS95, IsOS98, IsOS2000 etc.

I'm looking to tick the appropriate yes/no column depending on the presence of the operating system in the OperatingSystem column.

The query I'm using doesn't work however:

UPDATE SET IsOS95 = -1 WHERE OperatingSystem LIKE '95';

It works fine if I hard-code the WHERE statement, but returns no data when using the LIKE command.

Any ideas??

Thanks very much

 

[Mile-O]

Wrong Approach.

Your checkboxes are a repeating group and are unnecessary.

All you need is a table like this:

tblOperatingSystems
OperatingSystemID
Operating System

and a table for your computers (sample fields)

tblComputers
ComputerID
OperatingSystemID

Now you can join the two Operating SystemID fields.

 

[SQL_Hell]

It looks like you are missing the table name in your update statement, should be like this:

UPDATE mytable SET value = 1

 

[danb]

Oops, that's just a typo - the table name is in query, it should read:

UPDATE product SET IsOS95 = -1 WHERE OperatingSystem LIKE '95';


Thanks for the advice Mile-O-Phile, I'm aware of table joins, but the database has to be maintained by a *total* novice - so check-boxes are best in this instance.

Isn't there a performance issue with joining large tables as well??

Thanks anyhow, still got a problem...

 

[Mile-O]

Thanks for the advice Mile-O-Phile, I'm aware of table joins, but the database has to be maintained by a *total* novice - so check-boxes are best in this instance.

Ah, I'm suggesting that instead of checkboxes on your form you have one combo with a query of the Operating Systems table in it. The user would simply select the operating system.

This would make it correctly normalised and on your issue of it been maintained by a complete novice my suggestion makes more sense as, when a new operating system comes on the market you just add a new record to the Operating Systems table and bingo! it's available for selection on the form. With your design, a new operating system can come on the market and, to accommodate it you are going to have to redesign your table(s), which in turn would mean having to reqork your queries, which would mean that you'd need to add extra checkboxes to your form, and your report(s) will also need maintained. Code, also, can be at the mercy of this design flaw.

So, what's better? Two seconds to add a record or the hour(s) involved in redesigning the database to accommodate an object because it hasn't been modelled correctly?

On the plus side, too - with the way I suggest you only need to run the update query on one field as opposed to each field that represents an operating system.

 

[Mile-O]

Isn't there a performance issue with joining large tables as well?

Do you have large tables?

It depends on what you are joining with - an indexed long integer field is more desirable than linking two text fields (that's just folly).

 

[Mile-O]

the database has to be maintained by a *total* novice

Give them some credit.

 

[danb]

And to have multiple operating systems selected?

 

[Mile-O]

In that case, you'd model it properly with a junction table as that's a many-to-many relationship.

Have a look at Pat Hartman's many-to-many sample database.

 

[danb]

There are over 5000 records in the database, so I'm concerned that joins will cause a larger system overhead than simple check-boxes.

I can add 'Longhorn' to the table when it comes out in 2007 without much headache. It's not like a new OS is released daily...

I see what you're saying and thank you for the advice.

 

[danb]

Thinking about it, by changing the structure of the database, I'm still left with the same problem. How to update the current OperatingSystem field so that it is more accessibly structured (for web searching etc) automatically. Going through each of the 5000+ records by hand isn't an option.

 

[danb]

Back to the original question, it seems to be the percentage sign wihich is causing the problems.

This works (returns lots of updated results):

UPDATE product SET IsOS95 = -1 WHERE OperatingSystem LIKE '95/98/2000';

But this doesn't (can't find an records to update):

UPDATE product SET IsOS95 = -1 WHERE OperatingSystem LIKE '95%';


Totally strange.

 

[Mile-O]

Totally strange.

Not really. In access the wildcard is an asterisk

UPDATE product SET IsOS95 = -1 WHERE OperatingSystem LIKE '95*';


As an aside, the criteria LIKE '95/98/2000' isn't that great as Like is used for finding/matching strings. You have given a literal value to find and, therefore, would be best suited with the equals sign instead.

 

[danb]

Ah ha! Thanks Mile-O-Phile.

I've been looking at Pat Hartman's Many-to-Many example to try to do it 'properly' like you suggest, but it really makes no sense. As you can tell, my Access skills are really poor.

I've never dealt with Forms (don't even know what they're used for) and queries are pretty new to me too. I guess it must be quite simple to have a select-box from which a number of options can be added to a data-cell, but I just don't have the time to learn right now...

Thanks again.

 

[Mile-O]

Have a look at the three tables in my sample here.

Forms, by the way, are what you use to enter data into your tables.