multiple conditions to satisfy

[geckodfw]

I have a table with a territory field, the territories need to be organized by 'region' Such as:
EAST1, EAST2, EAST3 all need to be designated "EAST"
CAN, CNG, CVT all need to be designated "CANADA"

I have tried nesting IIF's with no success, I have also tried using 'OR' with no success. Any suggestions?

Thanks

 

[Maquis]

How about creating a new territory table with a region field. You could then reference the table in your queries and reports for grouping. Maq B-)
<insert witty signature here>

 

[THoey]

If this is a one time fix and you do a:

SELECT DISTINCT region
FROm MyTable

does it return all regions that start with &quot;E&quot; should be set to EAST and those with a &quot;C&quot; to CANADA? You might be able to do the following:

UPDATE MyTable
SET region = &quot;EAST&quot;
WHERE region LIKE &quot;E*&quot;; <--- (could make this &quot;EAST*&quot;

UPDATE MyTable
SET region = &quot;CANADA&quot;
WHERE region LIKE &quot;C*&quot;

Worth a try if, like I said, a one time thing... Terry M. Hoey

 

[geckodfw]

I think that's pretty close to what I need, but I think I'm going to set up a SELECT CASE because not all the Canada's start with &quot;C&quot;, etc.

You guys are a great help! Thanks so much!

 

[geckodfw]

I have tried and failed. This is the SQL within my Access DB.

SELECT qry_top10_region.acct_name, qry_top10_region.acct_name, qry_top10_region.region, qry_top10_region.recv_acct_id, qry_top10_region.balamt, qry_top10_region.over60
FROM qry_top10_region;

What I need from Access is the 10 account names (acct_name) with the highest over60 for each region. Help!

 

[THoey]

First of all, this is confusing. Based on the name before the &quot;.&quot;, it looks like you are trying to select from a query. Is &quot;qry_top10_region&quot; your actual table name?

Also, You say:

&quot;What I need from Access is the 10 account names (acct_name) with the highest over60 for each region.&quot;

Do you mean the top 10 from each region (ten per region) or the top ten from all regions (ten total)?

Reply back with actual table structures, sample data and expected results and I sure we can get this figured out. Terry M. Hoey

 

[geckodfw]

I have a table of about 6000 records with a field called 'territory'. The 'territory' consists of values like &quot;SW&quot;, &quot;SW1&quot;, &quot;SW2&quot;, &quot;NE&quot;,&quot;NE1&quot;,&quot;NE2&quot;. I made a query called qry_top10_region from this table that creates a 'region' field combining all the &quot;SW*&quot; fields into the 'region' as &quot;Southwest&quot;, and all the &quot;NE*&quot; fields as &quot;Northeast&quot; for example.

I have made another query based on the 'qry_top10_region'. From this query I need to get the 10 highest &quot;Southwest&quot;, the 10 highest &quot;Northeast&quot;, etc. based on a field called &quot;over60&quot;

Here is the SQL again from the second query.

SELECT qry_top10_region.acct_name, qry_top10_region.acct_name, qry_top10_region.region, qry_top10_region.recv_acct_id, qry_top10_region.balamt, qry_top10_region.over60
FROM qry_top10_region;

All I have done at this point is pull all of the fields I need into this query.

Thanks again.