Extract 1st and last dates

[jennyp29]

I need to create a subform that extracts the first and last dates from a list of events. Is this possible by creating a query with a date formula in? I can't find these formulas in my Access 2000 book.

[comteck]

Is your first date your lowest date, and your last date your most recent date. If they are you can use Min, Max. Create a textbox for the first date, and in the Control Source, type:

=Min([Date])

Create another textbox for the last date, and in the Control Source, type:

=Max([Date])

This is assuming that the field containing the dates is named "Date". If not, use whatever name is shown in the name property for the date.

Hope this helps.
comteck

[jennyp29]

Thank you Comtek for your reply, and I am sure that it is part of the answer to my problem but I think I over-simplified the question.

I have a list of people who each do different exercises on lots of different dates, on each date there is a score for each particular exercise.

I need to isolate each exercise for each person showing the first and last dates only with the appropriate scores on these dates in either a form of a report.

Yes my first date is the lowest and the last date the most recent. Sorry, but I am quite inexperienced with Access

[comteck]

I'm kinda confused. Did the Min, Max functions work, or was it not what you were looking for?

comteck

[MMcCarthy]

As the record source for your subform you need something like:

SELECT Exercise, Min([Date]) As FirstDate, Max([Date]) As LastDate FROM tableName GROUP BY Exercise

Make sure you have textboxes for Exercise, FirstDate and LastDate on the form with there Control Source set to same.

[PEB]

As the record source for your subform you need something like:

SELECT Exercise, Min([Date]) As FirstDate, Max([Date]) As LastDate FROM tableName GROUP BY Exercise

Make sure you have textboxes for Exercise, FirstDate and LastDate on the form with there Control Source set to same.

And also group by person!

SELECT Exercise, Min([Date]) As FirstDate, Max([Date]) As LastDate FROM tableName
GROUP BY Exercise, Person;

[jennyp29]

I'm kinda confused. Did the Min, Max functions work, or was it not what you were looking for?

comteck

Hi Comteck
Yes, the min, max functions did work but I can't get the correct scores to appear for these dates.

Everyone else that replied thank you but that is all very complicated and I am a novice - sorry.