Adding a Region Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field with department numbers in Asia, Europe, Latin America and
USA. I want to sort by Region, pulling all the Asia office designations to
Asia, all the European office designations to Europe, etc. I have 90,000+
records and this query will be done 2-3 times per week. What is the SQL
statement that will perform this query? Thanks for any and all help.
 
If you have a field in your table that will give you
Country or Region information. Then this query will be
simple. If you do not have that field, then the query
will almost be next to impossible. Because you will have
to require to add a field to the table and go throught all
the records and determined which record belong to which
country/region.
 
I have a field that contains department numbers, 3000XNWT (for Hong Kong),
3000XSEO (for Korea), 3000XPRS (for France), 3000XLND (UK), etc. (36
countries in all) and if I make a field for "Region" how do I get the Korea
records to say Asia, the France records to say Europe, etc.? I would be
checking for department designators, such as NWT, SEO or whatever country we
have locations?
Thanks.
 
I have a field that contains department numbers, 3000XNWT (for Hong Kong),
3000XSEO (for Korea), 3000XPRS (for France), 3000XLND (UK), etc. (36
countries in all) and if I make a field for "Region" how do I get the Korea
records to say Asia, the France records to say Europe, etc.? I would be
checking for department designators, such as NWT, SEO or whatever country we
have locations?
Click to expand...

I'd suggest creating a 36-row table with your country codes in one
field and the corresponding region - in plain text, "Europe" or "Asia"
or whatever - in a second field. You can then simply include this
table in a query linking on the country code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
One of two things that comes to mind that can help you
with this problem.

1) You can create a field called "region"
("Asia", "Europe", etc), then update region field based on
the department field.

Or

2) You can create another table that will contain a Region
and Department field. The Region will
be "Asia", "Europe", etc. The Department will
be "3000XNWT", "3000XSEO", etc. Then you have to join the
table to get the region information.

Hope this will help.
 
I would even recommend you create a table of regions and make the
Region column in the table of country codes dependent on that.
Otherwise, someone will put in a region of "South America" for certain
countries, while someone else will put in "S. America". When this
happens, and you try to report something, you will get two sets of
results, one for "South America" and another for "S. America" with
separate subtotals.

Always set up a table for every entity, every list of items, in your
database. Formally create and maintain all the relationships. It
saves time in the long run.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top