Originally Posted by
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.