Confusing SQL query

[int3grate]

I'm trying to construct this query using sql in microsoft access, and I'm having a little trouble finding a way to do it.I have an items-sold table that looks similar to this:

item_name							 department-------------------------------------------------Geo positioning system   			 NavigationMap measure						   NavigationGeo positioning system				BooksSextant							   BooksPocket knife-Nile					 BooksPocket knife-Nile					 Clothes

I need build a query that list departments that have not sold a Geo position system. Any ideas?

[redsun]

Just try this:

SELECT department FROM yourtablename WHERE item_name NOT LIKE 'Geo positioning system'

Redsun

[int3grate]

Just try this:

SELECT department FROM yourtablename WHERE item_name NOT LIKE 'Geo positioning system'

Redsun

That doesn't work. That query will return departments that have sold Geo Positioning systems....

[redsun]

That doesn't work. That query will return departments that have sold Geo Positioning systems....

Did you tryed the query? There is "NOT LIKE" which will return the department names NOT selling "Geo positioning system".

[jesh]

Can Access handle multiple SELECTs like SQL Server? If so, this might work for you:

SELECT department FROM yourtablename WHERE department NOT IN (SELECT department FROM yourtablename WHERE item_name LIKE 'Geo positioning system')

[lordfa9]

SELECT department FROM yourtablename WHERE item_name NOT LIKE 'Geo positioning system'

I think you missed out on soemthing the phrase you want to use for the LIKE statement should have a * at the start and endTry this

SELECT department FROM yourtablename WHERE item_name NOT LIKE "*Geo positioning system*"

[justsomeguy]

SELECT department FROM yourtablename WHERE item_name NOT LIKE 'Geo positioning system'That query does not work because in this case:

Geo positioning system				NavigationMap measure						   Navigation

It will select the Navigation department because it has an entry where it does not include the GPS, even though the GPS is on another row. The query just tells it to select all departments where there is a row without the GPS in it. If Access supports subqueries, then jesh's answer will work.

[aalbetski]

This works in SQL Server. Not sure about Access

select department from items_sold where department not in (select department from items_sold where item_name = 'Geo positioning system')

It uses a sub query to first determine all the departments that have sold a Geo positioning system and then the outer query excludes them

[jesh]

This works in SQL Server. Not sure about Access

select department from items_sold where department not in (select department from items_sold where item_name = 'Geo positioning system')

It uses a sub query to first determine all the departments that have sold a Geo positioning system and then the outer query excludes them

Isn't that what I said?

[aalbetski]

Yes, and I had edited it to that effect, somehow it came back, sorry

[jesh]

Yes, and I had edited it to that effect, somehow it came back, sorry

Heh, no worries.