Searching for specific word in query

[ModernGIS]

Greeting,
I am working with a large database and need to sort things out. My name
table "All Accounts" has a field with different enteries (Active, Inactive,
ETC). What I need to do is to build a query to have it look in one specific
column "Account Status". Than, I want the query to produce (2) different
results. Lets say I want it to search through the "Account Status" column and
all the records that show "ACTIVE" to be produced in one result/table and the
other that show "INACTIVE" to be procuded in a seperate table.

Any ideas on how I might be able to do this?

Thanks

 

[John Spencer]

Does the field only contain the one word?

Field: Account Status
Criteria: ="Active"

IF the field contains a string of words and you want to match just one
specific word, add a calculated field to your query and use the like operator

Field: " " & [Account Status] & " "
Criteria: Like "* Active *"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Why produce a table that will be outdated after the next transaction? Just
use a query whenever you need the information or a report from the query to
deliver to someone.

SELECT [All Accounts].*
FROM [All Accounts]
WHERE [All Accounts].[Account Status] = "ACTIVE" OR [All Accounts].[Account
Status] = "INACTIVE"
ORDER BY [All Accounts].[Account Status];

 

[golfinray]

You can put Like "active" or Like "Inactive" in the query criteria for your
account status field. Then you could separate them by putting in two new
fields with:
Active:IIF([account status],="active","active","")
Inactive:IIF(account status]="Inactive","Inactive,"")
If you only have active or inactive you could use:
Status:IIF([account status]="active","active","inactive")
That should give you the list.