MS Office Query Problem

[GeSuN]

Hi,

I have a little problem figuring how to do a certain query in Excel... I have a list of clients with their adresses (country included) and the product they bought. My query consist of selecting all the clients from only 3 selected countries (I know how to do this part of the query) and in these 3 countries, 1 country has all the clients without exception and the 2 others have only the clients who bought a selected item... How do I do this???

If any question ask me!

 

[GeSuN]

Bump...

 

[NeoV]

clarify a bit please..

 

[GeSuN]

Ok, here's a short list of what I have.

Client Country Product

Roger Canada Modem
Paul Japan Mouse
Samy France Keyboard
Patrick USA CPU
Paula Canada CPU
Rick Canada Mouse
Lili Japan Modem
Nick USA Mouse
Henry USA Monitor

So with my query, I want to show the client of USA, Canada and Japan only... BUT for USA and Canada, I only want the client who bought CPU not the others and for Japan I want every client I have.

Results:
Patrick USA CPU
Paula Canada CPU
Paul Japan Mouse
Lili Japan Modem

 

[bot2600]

WHERE (((Country = "USA") OR (Country = "Canada") AND Product = "CPU") OR ((Country = "Japan") AND (Product = *)))

I don't know anything about Excel, but it would be somthing like that in Access.

Bot

 

[NeoV]

do you know how to use filters? That would do the trick quite easily, but that would only work for one country at a time....personally, I would do a pivot table so you can select your criteria like country or part....

 

[Bglad]

Excel cannot return a list like you described. It can only give a mathematical answer to a formula in one cell. A pivot table is your closest bet.

The only other thing you can do is create a column describing your query, use IF and AND functions to make decisions, and have Excel put some flag word in rows that meet your criteria.

What you are asking is a function better suited to a database.

 

[GeSuN]

I've found it...

Here's what to do : (there maybe be errors in the translation, my excel is in french and I don't know the option names in english by heart 😛 )

If you name your list then you go in the menu :

Data/External Data/Query/

Then you select Excel Files, and then the file you want to query.

You select the fields you want, chose your filters and then you select Display data in Microsoft Query (not sure about the name of this option in english) then you can edit your query.

So yes its possible to make request...

Thanks anyway!