|
|
|
Query question!!! HELP REQUIRED!!! |
|---|
| message from =?Utf-8?B?U2NhcnR5?= on 30 May 2004 |
Hi,
I've a li'l problem and I was hoping somebody could help me out here.
In my table "tblData", I've the following data;
Employee_ID
Last_Name
First_Name
Department
Office_City
Now, I've made a query that searches for all the employees in a particular city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan, Berlin, New York, LA etc.....
This query is working very well for a particular city like NY, Paris etc.. However, now I want to run a query for all the employees (First_Name, Last_Name) working in Euro-zone, Europe, Americas, and globally. And I can't just work it out.
Can anybody PLEASE help me out here??? For Euro-zone, I want Paris, Brussels, Milan and Berlin; for Europe I want all the european cities and for America, NY and LA.
By the way, I am using combo-boxes as the criteria for my queries. Ideally, I want the names "Europe, Euro-zone, Americas, Global" to be displayed in the combo-boxes. When a user selects, "Euro-zone" (for example) I want the query to display all the employees in euro-zone. The problem as you would have already understood is that the table itself contains only the cities names and NOT the region names.
I shall be very grateful if somebody could help me out here.
Regards,
Julien.
|
| Douglas J. Steele replied to =?Utf-8?B?U2NhcnR5?= on 30 May 2004 |
Do you have a table that relates the zones to the cities? If not, create
one. Then join that table to tblData, and set the critieria appropriately.
|
| =?Utf-8?B?U2NhcnR5?= replied to Douglas J. Steele on 30 May 2004 |
Thank you very much for helping me out.
But how can I make a table that relates the zones to the cities? Can you please give an exaple?
Thanks again.
|
| Douglas J. Steele replied to =?Utf-8?B?U2NhcnR5?= on 30 May 2004 |
Something along the lines of
tblGrouping
Zone_Name
City_Name
Your data would be something like:
Euro-zone, Paris,
Euro-zone, Brussels
Euro-zone, Milan
Euro-zone, Berlin
Europe , Birmingham
Eurpore, Manchester
America, NY
America, LA
etc.
|
| =?Utf-8?B?U2NhcnR5?= replied to Douglas J. Steele on 30 May 2004 |
Hi there,
I've to thank people here in this discussion group (especially Douglas) for helping me out. However, now I've run into another problem and I would really appreciate if somebody coule help me out.
In my table "tblData", I've the following data;
Employee_ID
Last_Name
First_Name
Department
Office_City
Now, I've made a query that searches for all the employees in a particular city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan, Berlin, New York, LA etc.....
This query is working very well for a particular city like NY, Paris etc.. However, now I want to run a query for all the employees (First_Name, Last_Name) working in Euro-zone, Europe, Americas, and globally. For Euro-zone, I want Paris, Brussels, Milan and Berlin; for Europe I want all the european cities and for America, NY and LA.
For this I created another table "tblCity" with the following format:
Field Names: Global, Europe, Eurozone, Americas
After which the column contains the city names; e.g.,
Eurozone (Field Name)
Paris
Milan
Berlin
Brussels
....
After doing this I made a query "qryCity" which displays the name of the American cities where the office is based (NY and LA).
Now this query is based on the table "tblCity" and returns the names of the two cities (NY and LA). I then made another query "qrySearch" based on the previous query "qryCity" and linking it with the original table "tbData".
=DLookUp("[Americas]","qryCity")
Thus this query takes the city names from the result of the query "qryCity" and then searches the employees names in the basic table "tblData".
This works really well, HOWEVER, it finds out the records of only the NY office!!!! and NOT the LA office!!
Actually, the result of "qryCity" is
NY
LA
And the query based on this (since it uses the DLOOKUP function) only finds the data for NY and not LA!!!
I want the second query to give me the employees list for BOTH NY and LA!! Can anybody PLEASE help me out here???
Douglas, another problem is that Paris is in both Euro-zone as well as Europe!!! Can you propose a solution for this?? (Thank you very much for replying to my earlier email).
I shall be very grateful if somebody could help me out here.
Regards,
Mike.
|
|
