SQL MINUS in ACCES with Similar Tables 1

[spinalwiz]

I've got a very simple table that is just a list of equipment. Theres an ID field and an Equipment field. This table is called EquipmentList

Now through a rather long SQL statement I get a table of Equipment being used for a particular event (called EquipmentInUse). Again there is just an ID field and an Equipment field.

All I want to do is get a list of equipment NOT in use.

Using 'normal' SQL I would just have to do something along the lines of:

EquipmentList MINUS EquipmentInUse

Access doesnt seem to want to let me use this very useful MINUS command (any idea why?). Through searching around I found suggestions to use the LEFT JOIN workaround but this doesnt seem to offer any help either.

EquipmentInUse LEFT JOIN EquipmentList

just gives you EquipmentInUse and

EquipmentList LEFT JOIN EquipmentInUse

gives EquipmentList


Any ideas?

Thanks

 

[SkipVought]

Hi,

Use NOT IN list
[tt]
Select ...
From ...
Where EquipmentID NOT IN (your select of the list of equipement ids)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[spinalwiz]

Thanks, I cant believe I spent so long over this. Its easy if you know how