A question on Microsoft query

[Andy D 2002]

Hi
Im using the query tool (data menu => get external data => new database query) to suck information in from a database.

Its returning results, see example below:

Client Name Valid to Date Size Rate
********** ********** **** *****
Client 1 01/01/2007 Size 1 110
Client 1 01/01/2007 Size 2 220
Client 1 01/01/2006 Size 1 100
Client 1 01/01/2006 Size 2 200
Client 2 01/06/2006 Size 2 220
Client 2 01/06/2006 Size 4 440
Client 2 01/06/2005 Size 2 210
Client 2 01/06/2005 Size 4 410
Client 3
etc

How can I change it so it only shows the latest set of rates for each client? eg valid to 01/01/2007 for client 1 and valid to 01/06/2006 for client 2?

(as you can see each client has its own set of sizes and rates and the renewal dates vary by client).

Hope makes sense
Thanks
Andy

 

[rhadl1]

Hi, You would probably need a query for each client. Once you have created the query click on edit query, then add a criteria, put the criteria as [Code1] then return to excel (you will be asked for a value for code1). Then rightclick and choose edit parameters, the third option in there will be to use a parameter based on a cell value, this can then be set according to your rules.