Grouping Table Design Question

[alwayshouston]

Hi All
I am stuck on the designing a table that would allow a customized grouping. For instance, I have an Employee Table below:

Emp_ID Emp_Name Emp_Loc Emp_Loc_Continent
100000 Jack Texas North America
100001 Mary Arizona North America
100008 Doug Canada North America
100003 Tony Brazil South America
100004 Mack Brazil South America
100005 Joe UK Europe

Now I would like a grouping table that would give me the grouping of employees that are living in North America or South America but Not in Canada. How can I save this grouping in a table and retrieve data from the table? I know I can easily write the query to get the output, but how can I save the criteria in a table format about grouping of groups or individual employees?
The grouping table should be similar to one below:

Group_ID Emp_Loc Emp_Loc_Continent

You can modify the entire table structure if needed.

I hope I make sense above. If not, let me know. I will try to explain it more clearly.
Thanks!

 

[Golom]

First of all ... your table is not properly normalized. I assume that "Emp_ID" is the primary key and the rule is that a fact (i.e. a non-key column) should be a fact about the key, the whole key and nothing but the key. The continent is dependent not on the key field but on the "Emp_Loc" field. (i.e. "Texas" is in "North America" regardless of who the employee is.) You should have two tables

tblEmployees
EmpID
EmpName
EmpLoc

tblLocations
EmpLoc
Continent

Then
[tt]
Select EmpID, EmpName, Continent
From tblEmployee INNER JOIN tblLocation
ON tblEmployee.EmpLoc = tblLocation.EmpLoc
Where tblEmployee.EmpLoc <> "Canada" AND
(tblLocation.Continent = "North America" OR
tblLocation.Continent = "South America")
[/tt]
You can save that as a query and use it in other queries as though it was a table.

 

[alwayshouston]

Thanks. I know the table is not normalized and your suggested design is correct, but I am dealing here with a table which is created in Datawarehouse as as Dimension Table. Regardless of your proposed design or my prior design, how would you saved the "WHERE" clause information in a table? For instance, Group 1 would want to include Canada, North America and South America. Group 2 wants to only include Canada. Group 3 wants to include North America or Canada. Group 4 wants to include North America or Canada and exclude Texas. I hope I am making sense here. How would I save each criteria define by a group in a table format???

Any ideas??

Again, thanks for your prompt feedback!