Selecting oldest or newest data from multiple records

[Guest]

Hello! I am working with a table of orders that is related to a table of
order activities. An activity record includes a beginning and ending date as
well as an amount.

I am looking for a way to group/total or consolidate by order and to find
the oldest date in the activity table for the START DATE field and to also
define the newest date for that order in the END DATE field. The result would
be the current period of performance. Some orders only have one record (which
is easy to solve) while others may have 2-5 activity records as modifications
are made over time.

It is usually true that the first activity record contains the oldest START
DATE and that the most recent entry will have the newest END DATE but there
are some exceptions depending on the accuracy of the specialist who enters
the activity.

Thank you in advance.

 

[John Spencer]

Without more detail on the structure of your table(s), you would probably
want the Minimum Start date and the Maximum end date. Can you post the SQL
of a query that gives you all the data that you require in multiple rows.

A generic query to give you the begin and end dates would look something
like:

SELECT OrderNumber
, Min([StartDate]) as Begin
, Max([End Date]) as End
FROM [Your Table]
GROUP BY OrderNumber

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Very cool, John! Your SQL example was enough to get me to my answer; I simply
modified mine a bit. I use MIN and MAX often in Excel but I didn't think it
would be that easy in Access.

Thank you for the prompt and extremely helpful assistance! Thanks Mr. MVP!
--
Dawn Bjork Buzbee

Without more detail on the structure of your table(s), you would probably
want the Minimum Start date and the Maximum end date. Can you post the SQL
of a query that gives you all the data that you require in multiple rows.

A generic query to give you the begin and end dates would look something
like:

SELECT OrderNumber
, Min([StartDate]) as Begin
, Max([End Date]) as End
FROM [Your Table]
GROUP BY OrderNumber

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello! I am working with a table of orders that is related to a table of
order activities. An activity record includes a beginning and ending date
as
well as an amount.

I am looking for a way to group/total or consolidate by order and to find
the oldest date in the activity table for the START DATE field and to also
define the newest date for that order in the END DATE field. The result
would
be the current period of performance. Some orders only have one record
(which
is easy to solve) while others may have 2-5 activity records as
modifications
are made over time.

It is usually true that the first activity record contains the oldest
START
DATE and that the most recent entry will have the newest END DATE but
there
are some exceptions depending on the accuracy of the specialist who enters
the activity.

Thank you in advance.