Tricky SQL Question

[ttocsmi]

I have an Access database for which I'm trying to write a query based on a request from the users. I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value. What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records? ArtworkID / DescriptionText 123 / Motor 234 / Fuse 234 / Start Switch 456 / Stop Switch 789 / Start Switch 789 / Stop Switch 789 / Light Result = ArtworkID 789 Any assistance will be greatly appreciated. Thanks!

[June7]

This is tricky because requires considering data of related records.

Will an ArtworkID ever have multiple Start or multiple Stop?

SELECT * FROM tablename WHERE DCount("ArtworkID","tablename","DescriptionText Like '*Switch*' AND ArtworkID=" & [ArtworkID])>1;

[jopap]

select ArtworkID from tbldata where ArtworkId in (Select ArtworkId from tbldata where descriptiontext like *switch group by artworkId having count(*) > 1)

This query assumes you can have only one start and one stop switch for a single ArtworkId

[ttocsmi]

select ArtworkID from tbldata where ArtworkId in (Select ArtworkId from tbldata where descriptiontext like *switch group by artworkId having count(*) > 1)

This query assumes you can have only one start and one stop switch for a single ArtworkId

No go, even after modifying to this (I changed the field names):

select artboardnumber from art_callout_table where artboardnumber in (Select artboardnumber from art_callout_table where lrutext like '%switch%' group by artboardnumber having count(*) > 1)

I'd like to search for substrings within the DescriptionText field (not always the entire string) such as 'switch', 'motor', or 'ECU'.

[ttocsmi]

Tricky, indeed. There could be multiple occurrences of the same word, and each search term may be a substring of the entire DescriptionText field.

I have SQL working for 2 x search terms; I'm going to try it with 3+ terms and some NOTs for good measure...

[orange]

Watch this free video tutorial and see if it helps.
http://www.datapigtechnologies.com/f...earchform.html

[ttocsmi]

I have an Access database for which I'm trying to write a query based on a request from the users. I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value. What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records? ArtworkID / DescriptionText 123 / Motor 234 / Fuse 234 / Start Switch 456 / Stop Switch 789 / Start Switch 789 / Stop Switch 789 / Light Result = ArtworkID 789 Any assistance will be greatly appreciated. Thanks!

Surely there's an easier way, but this is how I got it to work (I've updated the table and field names):


SELECT * FROM art_callout_table
WHERE artboardnumber IN (

SELECT artboardnumber FROM art_callout_table

WHERE artboardnumber IN (

SELECT artboardnumber FROM art_callout_table

WHERE lrutext LIKE "%start%")

AND lrutext LIKE "%light%")

AND lrutext LIKE "%switch%";


What confused me repeatedly during development, and what confused the users (they were expecting the search to work one way & I was assuming it to work another), is that there are two distinct search methodologies:

1) Using the built-in Access table filtering commands (or a basic SELECT FROM WHERE statement) to narrow down a table of description text values. Repeated filters can be applied to narrow the list further, but if you're looking for "switch" and "start", all you'll get is individual description text records having both values, eg "big red starting switch" or "switch start light" but not "green switch". Not terribly helpful.

2) Use a different search technique to return all ArtworkID values which have "switch" or "start" or "light" strings within their DescriptionText fields, eg don't look just for DescriptionText records similar to "switch start light blinker".

I thought this was going to be much easier than it turned out to be. Thanks all for your help.

[orange]

If you have 3 terms try

SELECT artboardnumber
FROM art_callout_table
WHERE 
lrutext LIKE "*" & [EnterWord1] &"*")  AND
lrutext LIKE "*" & [EnterWord2] &"*")  AND
lrutext LIKE "*" & [EnterWord3] &"*") ;

[June7]

In my post#2, I was thinking you wanted only the records where ArtworkID had more than occurence of 'Switch'. If you want ALL records with 'Switch' in the description, use wildcard.

SELECT * FROM art_callout_table WHERE lrutext Like '*Switch*';

If you want to further filter for only the 'start' or 'stop' records, then use orange's suggestion for multiple criteria. Are 3 enough for your needs? Review http://allenbrowne.com/ser-50.html