How to get Min and Max from Resultset

[newbie2006]

Hi there

I am using MS Access and am trying to extract the region information from my vendorquery and find the earliest period and latest period for each. My raw data looks like (RID=regionID):

RID Year Quarter
1 1997 2
1 1998 2
1 1999 1
1 2006 1
2 1990 3

I have used the following sql:

SELECT Region_id, VendorQuery.Year AS BeginningYear
FROM VendorQuery
WHERE (((VendorQuery.Year)=(Select min(year) from vendorquery as subquery where subquery.year = vendorquery.year)));

I manage to get the region and beginning year using the above SQL but now i need to extend the query into "quarter" e.g. i should retrieve 1997 qtr 2 as my last record for region 1:

RID Year Quarter
1 1997 2

How do i do that?

[Daemonspyre]

Try this article from the MySQL Moderator Guelphdad.

It explains how to get what you are looking for:

http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml

[newbie2006]

Hi there

i did what was suggested. However i need to do this at 2 levels:

1) between RID and Year
2) between Year and Quarter

This is to select the minimum year for that Region_id
SELECT vendorquery.Region_ID, Min(vendorquery.Year) AS MinOfYear
FROM vendorquery
GROUP BY vendorquery.Region_ID;

and this is to select the min quarter for that Year
SELECT vendorquery.Year, Min(vendorquery.Quarter) AS MinOfQuarter
FROM vendorquery
GROUP BY vendorquery.Year;

I tried to combine them

SELECT vendorquery.Region_ID, Min(vendorquery.Year) AS MinOfYear, Quarter
FROM vendorquery
where Quarter=(SELECT vendorquery.Year, Min(vendorquery.Quarter) AS MinOfQuarter
FROM vendorquery
)
GROUP BY vendorquery.Region_ID;

the error given by MS Access is:
"You have written a subquery that can return more than one field without using "exists" in the main query "from" clause

I am really new in SQL and will appreciate any help.