Query Help 1

[figler]

I have a table:

ColProduct ColDate ColSyntheticID
300 2/13/01 1
300 2/14/01 2
300 2/15/01 3
400 11/3/99 4
400 11/4/99 5
400 11/7/99 6
700 2/11/00 7
700 2/19/00 8
700 2/21/00 9

I want to write a select statement on this table that returns the lowest date for each ColProduct:

ColProduct ColDate
300 2/13/01
400 11/3/99
700 2/11/00

Is this possible? I was thinking something like:

'Select Distinct Max(ColDate), ColProduct' but that is not working for me. Is this possible in an Access Query? Quick reply would be greatly appreciated -- thanks! -Brad

 

[smedvid]

SELECT Table1.TestField1, Min(Table1.TestDate1) AS MinOfTestDate1
FROM Table1
GROUP BY Table1.TestField1;

should do the trick.. Steve Medvid
"IT Consultant & Web Master"
web page under development...

 

[figler]

Thanks, that's it. I had been using a similar statement but without the 'Group By' clause and it was returning only one record. Maybe I don't understand best use of 'Group By' -- can someone explain?

 

[smedvid]

Real quick overview is that Group By is used to summarize many lines of data into a single occurrence. Like the example above where the Min() function is used for a group of Product codes to get the lowest date. Also, you can use Group by on multiple fields depending upon the database table and reporting needs. Translation: you can have a Group By Field1, Field2, Field3,... etc. The best way to learn is to experiment and see what results are produced. The Access help is also good for examples as is MSDN and there are many books available. Steve Medvid
"IT Consultant & Web Master"
web page under development...

 

[WildHare]

Group by "sorts" by unique values - so if you have 10 "Harry" and 14 "Sam" and 32 "Rose", each with their own dates, a

Select Name, Min(date) group by NAME

will get the MINIMUM date for EACH specific name

Harry - 1/1/02
Rose - 10/15/01
Sam - 2/3/02

Without a GROUP BY, you would get the single, individual minimum date... 10/15/01


Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[figler]

Awesome, thanks Steve. Much appreciated.