First and Last field of a period.

[Guest]

Hi! I need your help...!
I want to take the First and the Last data of a field in a period.
Example:
I have a table details of the despatches for all the countries and I want
the First Desp. No and the Last Desp. No of a period (e.g for the period
1/1/2005 - 31/1/2005)

 

[Al Newbie]

Try using the First and Last functions in the query

something like

SELECT Last(tmp.DespatchNo) AS DespatchNoResult
FROM (SELECT Table1.DespatchNo, DateFe
FROM Table1
ORDER BY DateFe, DespatchNo) tmp
WHERE (((tmp.DateFe) Between #4/1/2005# And #4/30/2005#))

This takes all the records and sorts them by date and despatch no (it
assumes despatch no is numeric and consecutive numbering) and then uses the
last and first function

OR something like

SELECT Max(Table1.DespatchNo) AS DespatchNoResult
FROM Table1
WHERE (((Table1.DateFe) Between #4/1/2005# And #4/30/2005#))

This uses the Max and Min functions and therefore doesn't require the
records to be sorted

HTH

 

[Van T. Dinh]

Check Access VB Help on the DMin() and DMax() functions.

 

[Jeff Boyce]

As Van points out, consider using Min() and Max()-related aggregation. If
you are working in a query, take a look at the Totals query information.

By the way, the First() and Last() aggregations rely on Access internally
determining the first (and last) record stored. How Access does that, and
how you would may not be the same.