Question on queries.

  • Thread starter Thread starter Sakuraki
  • Start date Start date
S

Sakuraki

Hi all, I have table like this

Item----Supplier----Source-----ExpiredDate
abc-------xyz---------50--------02/20/2004
abc-------xyz---------20--------03/20/2004
abc-------xyz---------30--------04/20/2004
abc-------jqk---------100-------09/15/2004
dfg-------plk---------50--------03/26/2004
dfg-------plk---------50--------04/28/2004
iuj-------daf---------100-------03/15/2004
kdj-------efg---------100-------06/16/2004
duj-------ghy---------100-------05/10/2004

I only want to take the earliest date of the same item with sam
supplier, the expecting result will be like

Item----Supplier----Source-----ExpiredDate
abc-------xyz---------50--------02/20/2004
abc-------jqk---------100-------09/15/2004
dfg-------plk---------50--------03/26/2004
iuj-------daf---------100-------03/15/2004
kdj-------efg---------100-------06/16/2004
duj-------ghy---------100-------05/10/2004

is that possible? Thanks
 
Sakuraki,

Try this in two steps. First, make a query based on your table, include
the Item, Supplier, and ExpiredDate fields, make it a Totals Query
(select Totals from the View menu in query design), leave Group By in
the Totals row of the Item and Supplier columns in the query design
grid, and put Min in the ExpiredDate column. Save and name this query.
Now, make a new query and add your table and also the first query. Join
the table and query on all three fields from the first query. This
should give what you want.
 
hi - i have a similar situation as this & i followed the same procedure
but i'm getting more records than my original table
 
Back
Top