Earliest Date SQL help

[p7eggyc]

Hi all,
My name is Peggy and I'm new to the forum but very excited to have found you. I have a SQL challenge and I will be the first to admit SQL is not my strong suit. I need a query to do the following in Access 2000:

Choose all records from the CouncilMembers table that have a matching intake record. There may be multiple councilmember records for the same intake record. I want to find the earliest council date among the councilmember records and if the earliest date is within the user defined date range, calculate the age of the person as of that council date using the birth date held in the intake table.

I have this initial sql written to get all the records:
SELECT CouncilMembers.DocketNum, CouncilMembers.[Council Date], CouncilMembers.CouncilTime, INTAKES.DocketNum, INTAKES.[Date of Birth]
FROM INTAKES RIGHT JOIN CouncilMembers ON INTAKES.DocketNum = CouncilMembers.DocketNum
ORDER BY CouncilMembers.DocketNum, CouncilMembers.[Council Date];

And I have this SQL that gets records that have multiple dates:
SELECT CouncilMembers.DocketNum, CouncilMembers.[Council Date], CouncilMembers.CouncilTime
FROM CouncilMembers
WHERE (((CouncilMembers.DocketNum) In (SELECT [DocketNum] FROM [CouncilMembers] As Tmp GROUP BY [DocketNum] HAVING Count(*)>1 )))
ORDER BY CouncilMembers.DocketNum

but I don't know how to use/combine/add to these to get the result I need. I would really appreciate any help you could shoot my way! Thanks!

Peggy

 

[CosmoKramer]

Peggy - Check out the Min function in Access help. When used with your date field the query will return the earliest date it finds.....