EXIST QUERY IN ACCESS.....

[navyaa]

I am trying to write a query with the EXIST statement in access but it doesnt seem to be working. When i generate the query using access it uses an innerjoin but doesnt use EXIST and i need to use that for a class project..

I am making a library database with two tables called CHECK-OUT and LOCATION-TYPE and i am trying to write a query which gets the book title and description from the CHECKOUT table(we have other types of resources such as DVD'S AND TAPES..ETC) and
then the CHECKOUT also has a Loc-ID which tells us where each resource is located(the floor)

so our query has to first get all the books available and then have an exist statement which says where they are located in the library..

this is what i have right now:
SELECT [Check-Out].[Resource-Type], [Check-Out].Title
FROM [Check-Out]
WHERE ((([Check-Out].[Resource-Type])="B")
AND ((Exists (SELECT * FROM Location-Type WHERE ( Loc-ID = 'A-1') and ([Location-Type].[Loc-ID] = [Check-Out].[Loc-ID]) ))<>False));



Any suggestions will be very helpful!!
THANK YOU,
nAVYAA

 

[Russell Hauf]

Hard to say w/o seeing your tables, but try something like this:

SELECT [Check-Out].[Resource-Type], [Check-Out].Title FROM [Check-Out] WHERE (Exists (SELECT * FROM Location-Type WHERE (([Location-Type].[Loc-ID] = [Check-Out].[Loc-ID]) AND ([Check-Out].[Loc-ID] = 'A-1') AND ([Check-Out].[Resource-Type]="B")))<>False);

HTH,

Russell