Find the first record from four fields

  • Thread starter Thread starter janschutz
  • Start date Start date
J

janschutz

I have to find the first occurance for data that comes from four fields.
1. PO Number
2. PO Item
3. PO Line
4. PO Date

Since there can be many items coming in on different PO's with different
lines on different dates. I need to find the first occurance by date.

Any help would be great.

Thanks
 
Easiest solution would be two queries
SELECT [Po Number], [po item], [po line]
, Min([po date] as Earliest
FROM [Your Table]
GROUP BY [Po Number], [po item], [po line]

Now use that saved query in another query

SELECT [Po Number], [po item], [po line]
, [po date]
FROM [Your Table] INNER JOIN TheQuery
ON [Your Table].[Po Number] = TheQuery.[Po Number] AND
[Your Table].[po item] = TheQuery.[po item] and
[Your Table].[po line] = TheQuery.[Po line] and
[Your Table].[po date] = TheQuery.Earliest


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Acutally, now that I look at it. The first query does it all as defined in
your problem statement.
You would need the second query if you wanted additional fields returned
beyond the fields you are using to determine which record(s) you want
returned.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
Easiest solution would be two queries
SELECT [Po Number], [po item], [po line]
, Min([po date] as Earliest
FROM [Your Table]
GROUP BY [Po Number], [po item], [po line]

Now use that saved query in another query

SELECT [Po Number], [po item], [po line]
, [po date]
FROM [Your Table] INNER JOIN TheQuery
ON [Your Table].[Po Number] = TheQuery.[Po Number] AND
[Your Table].[po item] = TheQuery.[po item] and
[Your Table].[po line] = TheQuery.[Po line] and
[Your Table].[po date] = TheQuery.Earliest


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


I have to find the first occurance for data that comes from four fields.
1. PO Number
2. PO Item
3. PO Line
4. PO Date

Since there can be many items coming in on different PO's with different
lines on different dates. I need to find the first occurance by date.

Any help would be great.

Thanks
Click to expand...
Click to expand...
 
Back
Top