[AccessD] Need most recent record from many

Susan Harkins harkins at iglou.com
Fri Mar 28 10:43:25 CST 2003


Totals view on your grouping field, but it won't work with extraneous
fields. If you need to show other data, you may need a subquery.

Susan H.


> Didn't know about Max(datefield) and am excited about that, but I don't
> think I worded my question correctly, because both your's and Bob's
> suggestions make sense, but for the whole query.  I need each record on
the
> one side to match up to one record on the many side (where I need the most
> recent record of a group).
>
> Example - we have a group of members that have made dues payments for as
> few as one year (new member) up to an unlimited number of years and want
to
> link only to the most recent year's dues information.  This group uses an
> expiration date to determine a current member, so it can cover more than
> one calendar year.  What I have been doing is sorting the information
first
> by the primary key then descending by the received date and appending the
> information to a table with properties to only accept a unique primary
> key.  So, theoretically, the first record will be the most current and be
> the only one written to the table.  If this is the only way to work this
> situation, I'll set it up to execute whenever the db is opened and it will
> be static.  Not ideal, but if we know the limitations we can work with
> it.  I'd like to be able to access this information through a query and
> skip the append step and this is where I feel I must be missing
> something.  Hmmm...  If a store wanted to access only the most recent
> transaction for a certain customer say for billing purposes, how would
they
> do that?  Grouping somehow and then using the Max function?
>
> Thanks,
> Susan
>
> At 09:58 AM 3/28/03, you wrote:
> >Susan,
> >
> >I assume you have a date field on the many side.  If so then
> >Max(datefield) will get the latest date.
> >
> >Charles Wortz
> >Software Development Division
> >Texas Education Agency
> >1701 N. Congress Ave
> >Austin, TX 78701-1494
> >512-463-9493
> >CWortz at tea.state.tx.us
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>



More information about the AccessD mailing list