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 > >