Query question!!! HELP REQUIRED!!!

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

Guest

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_I
Last_Nam
First_Nam
Departmen
Office_Cit

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.
 
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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Scarty said:
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
Click to expand...
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..
Click to expand...
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,
Click to expand...
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.
Click to expand...
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.
 
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 wrote: ----

Do you have a table that relates the zones to the cities? If not, creat
one. Then join that table to tblData, and set the critieria appropriately

--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(no e-mails, please!


Scarty said:
Hi
Click to expand...
city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan
Berlin, New York, LA 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'
just work it outBrussels, Milan and Berlin; for Europe I want all the european cities an
for America, NY and LAIdeally, I want the names "Europe, Euro-zone, Americas, Global" to b
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 a
you would have already understood is that the table itself contains only th
cities names and NOT the region names
 
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.
 
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_I
Last_Nam
First_Nam
Departmen
Office_Cit

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, America
After which the column contains the city names; e.g.

Eurozone (Field Name
Pari
Mila
Berli
Brussel
....

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" i
N
L

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


----- Douglas J. Steele wrote: ----

Something along the lines o
tblGroupin
Zone_Nam
City_Nam

Your data would be something like

Euro-zone, Paris
Euro-zone, Brussel
Euro-zone, Mila
Euro-zone, Berli
Europe , Birmingha
Eurpore, Mancheste
America, N
America, L

etc


--
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(no e-mails, please!


Scarty said:
Thank you very much for helping me out
But how can I make a table that relates the zones to the cities? Can yo please give an exaple
Thanks again
----- Douglas J. Steele wrote: ----
Do you have a table that relates the zones to the cities? If not
Click to expand...
creat
one. Then join that table to tblData, and set the critieri appropriately
--
Click to expand...
Doug Steele, Microsoft Access MV
http://I.Am/DougSteel
(no e-mails, please!
Click to expand...
 
Back
Top