DataBase - Find the first record from four fields

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

  John Spencer replied...

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
'====================================================

  John Spencer replied...

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
.

and Math. I would like to set the parameter query for Eng and Math = = > Eng min, Eng max, Math min and Math max. The Eng query criteria can be "Between Eng min AND Eng max" and the Math query criteria can be "Between Math min AND Math max". But. . .if I would it can be allowed to have "Null entry Please kindly help me, thanks a lot!! All combinations will be like. . . Eng : Between Eng min AND Eng max, Between Null AND Eng max, Between Eng min AND Null, Between Null AND Null Math : Between Math min AND Math max, Between Null AND Math max, Between Math min AND Null, Between Null AND Null - - Message posted via http: / / www.accessmonster.com Access Queries

New Project ID], [001_all payment Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments], [0001_all payment min and max dates].Mindate, [0001_all payment min and max dates].maxdate FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment min and max dates].[New Project ID]; This query give me all project numbers, payment dates Access Queries Discussions TotalPayment (1) Golfinray (1) Educator (1) Mindate (1) Leona (1) Maxdate (1) Min (1) Max (1) Try SELECT TOP 1 [001_all payment Tracking].[New Project ID], [001_all payment Tracking].[Payment Dates], [001_all payment Tracking].[Cumulative Payments], [0001_all payment min and max dates].Mindate, [0001_all payment min and max [dates].maxdate FROM [001_all payment Tracking] INNER JOIN [0001_all payment min and max dates] ON [001_all payment Tracking].[New Project ID] = [0001_all payment min and max

building as being in that type. Thus [Building Type Codes] has fields [Building Type Code], [Min Floors], and [Max Floors] I'm trying to create a query that will combine [Building query that selects the [Building Type Code] from [Building Type Codes] where [Stories] is between [Min Floors] and [Max Floors]. My SQL command is below. Whenever I try to run this Stories, (SELECT [Building Type Code] FROM [Building Type Codes] WHERE [Stories] Between [Building Type Codes]![Min Floors] And [Building Type Codes]![Max Floors]) AS [Building Type] FROM [Building Stories]; Access Queries Discussions EasySELECT (1) Database (1) Floors (1) Eric (1) Min (1) Max (1) Buildings (1) Datatypes (1) Try the following query which uses a non Building Stories].Stories < = [Building type Codes].[Max Floors] AND [Building Stories].Stories > = [Building Type Codes].[Min Floors] The problem with your query was that in theory the sub query could return Building Type Code]) FROM [Building Type Codes] WHERE [Building Stories].[Stories] Between [Building Type Codes].[Min Floors] And [Building Type Codes]![Max Floors]) AS [The Building Type] FROM [Building Stories]; Note 01-01-02 7 02-01-01 5 [Building Type Codes] ID Building Type Code Min Floors Max Floors 1 L 1 5 2 M 6 10 3 T 11 99 Stories].[Building ID], [Building Stories].Stories, [Building Type Codes].[Building Type Code], [Building Type Codes].[Min Floors], [Building Type Codes].[Max Floors] FROM [Building Stories] INNER JOIN [Building Type Codes] ON

DataBase I have a table with flow rate and datetime attributes. I used a "select Min(flowrate) from table group by Datepart(day of DateTime)" query to get the min flow rate every day. But I also want to know the time for that minimum t allow me to show the Time. How can I get the time for the min flow rate? Access Queries Discussions QFINALFlag (1) MinQFINAL (1) Database (1) TimeValue (1) DateValue (1 the one record per day, you could use something like: SELECT * FROM table WHERE (SELECT Min(X.flowrate) FROM table As X WHERE DateValue(X.datetime) = DateValue(table.datetime)) - - Marsh MVP take the minumum. My codes are: SELECT Ttable.DateTime, Ttable.QFINAL FROM Ttable WHERE (SELECT Min(XTABLE.QFINAL) FROM Ttable AS XTABLE WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime) GROUP BY e. use Copy / Paste): SELECT Ttable.DateTime, Ttable.QFINAL FROM Ttable WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL) FROM Ttable AS XTABLE WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime) - - Marsh MVP This is your codes: SELECT Ttable.DateTime, Ttable.QFINAL FROM Ttable WHERE Ttable.QFINAL = (SELECT Min(XTABLE.QFINAL) FROM Ttable AS XTABLE WHERE DateValue(Ttable.DateTime) = DateValue(XTABLE.DateTime)) Will the sub SELECT return an ID for Min(XTABLE.QFINAL) record to search in the Ttable? Or only return the Value of Min

First function returning min DataBase I am analyzing wildlife research data and need to find the record that represents BY Year, IndividualID; Access Queries Discussions INNER JOIN (1) Date (1) Disk (1) MAX (1) MIN (1) Min Date (1) PM (1) Aggregate function (1) Do not use the aggregate First function. It to use, and does not really do what you think does. Instead, try the aggregate Min function. You can use the Min function on the Date field you have to return the first date for a particular with the smallest value or the order records were created. Normally, you should use the Min function instead of First, but if the ID field is an AutoNumber, there is no Visit, [Data].ID FROM [Data] WHERE [Data].IndividualID) Is Not Null And [Data].Date = (SELECT Min(X.Date) FROM [Data] As X WHERE Year(X, Date) = Year([Data].Date) And X, IndividualID = [Data].IndividualID ) ORDER BY Year, IndividualID; - - Marsh MVP [MS Access] Min([Date]) does not work because there can be more than one detection on a particular