Query search criteria - How to use a "Not = to" search

[FriQenstein]

Greetings all,



My DB has quite a few different types of equipment, which I've put all in 1 table per the suggestions of some on this forum which has helped tremendously.

However, now I'm at a point that I need to build a Query to search for something that does NOT match the other categories.
For example, I have a Model field which identifies most of the types of equipment that I have in the Table. I also have a Misc category which doesn't fit the normal category of equipment. So, in order for someone to pull up a Report of all the Misc equipment, how would I go about writing up the search criteria string for that?

My brain is telling me something along the lines of !="blahblah" but that is my programming background and I was having difficulties finding examples of criteria that can be used in the Query.

Let's say I have the Model category, which Identifies the following: Bird, Gun Type 1, Gun type 2, etc. I was thinking that I could just put in a search string that would say: "whatever is NOT equal to these existing types, show me a list"

I hope that makes sense.
The only other idea I had about this was some sort of hidden Identifier that would flag the Misc category. Like a field or something I could tie to the Misc equipment without having to modify too much of the Table.

The reason for this type of search is that the Misc equipment will not ever fall into the other categories that I already have setup in the Model field. So I cannot add too many extra Model types due to the infrequent amount of times it would be utilized.

Thanks in advance.
Regards.

[isladogs]

You can filter for selected items in the Model field using the IN operator e.g. IN ("Bird", "Gun Type 1", "Gun type 2")
Similarly you can exclude them using NOT IN ("Bird", "Gun Type 1", "Gun type 2") as your filter criteria

[June7]

Data structure not clear to me. Are Model and Misc fields? Perhaps show sample table and data.

[CJ_London]

Another interpretation of your question is you want a list of everything from one table which is not in another table eg items v models. If this is the case you would use a left join. Working from my phone right now so difficult to provide some air code perhaps some one else can if this is a valid interpretation

[pbaldy]

Along the lines of what Ajax is saying, presuming you have a table of models try the unmatched query wizard comparing the two tables.

[FriQenstein]

Let me see if I can clarify the bit about the Tables.
I have combined all of the data into 1 Table. So all parts and equipment exist in a Table called Full_Inv.
I have another Table for Model so I can use this as a reference for each piece of equipment in the main Full_Inv table to classify what type of equipment it is.

So when a user searches for Gun parts for the G-Gun models, then the query can look for G-Gun criteria. Same for Bolt-Guns.
In another category I have our Birds which are listed as Model 5011... so any Bird body or part that is used for a Bird is associated with the 5011 Model field.
Now lets say a user wants to find out how many screws we have for a misc piece of equipment that isn't part of our normal inventory. These screws are still listed in the main Full_Inv table, but they are not associated with a Model (yet).
So a user wants to find these screws, the query would need to say:
search for description or part number of this particular type of screw but do NOT show me items matching this particular screw that are part of the following Model...

Does that make any sense? I hope I've cleared it up a bit.

I'm going to try isladogs suggestion and see what I can come up with.
I appreciate all of the help/input.

Regards.

[pbaldy]

Since you have a model table, the unmatched query wizard is probably the better option. Colin's method will work, but is not dynamic.

[isladogs]

I agree. My answer assumed you only had one table.
Both methods should work but an unmatched query is more generic.

[FriQenstein]

SOLVED

Ok. Thanks guys. I've created my Query according to the Model types NOT being present.

I appreciate the help.