Prev: jeramie bellmay
Next: How to subtract data in one chart from another chart in access?
From: ModernGIS on 19 Jan 2010 14:34
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
From: John Spencer on 19 Jan 2010 14:51
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

ModernGIS wrote:
> 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
From: KARL DEWEY on 19 Jan 2010 15:14
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];

--
Build a little, test a little.


"ModernGIS" wrote:

> 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
From: golfinray on 19 Jan 2010 15:15
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.
--
Milton Purdy
ACCESS
State of Arkansas


"ModernGIS" wrote:

> 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
 | 
Pages: 1
Prev: jeramie bellmay
Next: How to subtract data in one chart from another chart in access?