SQL Problem - Selecting Data from Multiple Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Problem - Selecting Data from Multiple Columns

Author

Topic

Ace
Starting Member

2 Posts

Posted - 2002-07-03 : 09:37:54

Hi People, I'm looking for help!

I'm running a Query to look up some data in a table within my database. I've looked all over the net to find answers but cannot come up with anything.

Anyway, Let's say for example that I have a table called 'Store', e.g

-------------------------------------------------------------------------------------------
[STORE]
{BARCODE ID} {PRICE} {QUANTITY} {MIN} {MAX} {USED 1} {USED 2} {USED 3} {USED 4}
000001.......£4.50...1..........1.....10....1........2........3
000002.......£2.99...3..........1.....20....2
000003.......£1.49...7..........1.....8.....3
000004.......£1.66...1..........1.....5.....1
000005.......£1.89...2..........1.....10....3
-------------------------------------------------------------------------------------------

I then run this Query on the table.

-------------------------------------------------------------------------------------------
SELECT *
FROM Store
WHERE [Used 1] & [Used 2] & [Used 3] & [Used 4] = Forms![Machine Spares].txtSearchText;
-------------------------------------------------------------------------------------------

Lets say I search for the number '3'.
It will only return

-------------------------------------------------------------------------------------------
{BARCODE ID} {PRICE} {QUANTITY} {MIN} {MAX} {USED 1} {USED 2} {USED 3} {USED 4}
000003.......£1.49...7..........1.....8.....3
000005.......£1.89...2..........1.....10....3
-------------------------------------------------------------------------------------------

We can see from the example table above that Barcode ID 000001 has the number '3' in the {USED 3} Field, so why isn't it picking it up?!

It seems that it will work on any number in USED 1, USED 2, USED 3 and USED 4 as long as none of the other fiels have numbers in to, for example if USED 2 had a 5 in on Barcode ID 000003 then it would pick it up, but as soon as I put a 7 in USED 4 for Barcode ID 000003 then it would just simply ignore that record.

Am I using the wrong operator in the query? (&).. does anybody know how to do this?

Hope you can understand what i mean, I'm not very experienced in SQL Query's so would appreciate any help.

Sorry about the Dots, the formatting went screwy!

Thanks
Martin

Edited by - Ace on 07/03/2002 09:41:22

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 09:49:09

Martin,

The query should be :

SELECT *
FROM Store
WHERE [Used 1] = Forms![Machine Spares].txtSearchText
OR [Used 2] = Forms![Machine Spares].txtSearchText
OR [Used 3] = Forms![Machine Spares].txtSearchText
OR [Used 4] = Forms![Machine Spares].txtSearchText
OR [Used 5] = Forms![Machine Spares].txtSearchText;

Additionally you might consider normalising your database design.
[url]http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212669,00.html[/url]

HTH,

macka.

Ace
Starting Member

2 Posts

Posted - 2002-07-03 : 11:14:49

Yup I should normalise it really

That works a treat though, thanks a lot Macka

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources