Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    SE MI USA
    Posts
    2

    Unhappy Unanswered: Finding the maximum for each...

    Trying to teach myself Access by hacking my way through an application - specifically, a membership list.

    One of the "features" is to have a record for each year in which a member renewed their membership.

    Table as follows - FieldName (Type and Info):

    MemberID (text - part of KEY in this table and foreign key)
    MembershipYear (text - part of KEY)
    MDFRenewed (Y/N)
    TAGVerified (Y/N)
    BikeVerified (Y/N)
    Expired (Y/N)
    DateEntered (Date)
    MemberSince (Date)

    MemberID combined with MembershipYear combine to form the key (ie, MemberIDs are unique, and you can only renew once per membership year)

    Here's the problem I'm stuck on: I want to create a query that only shows me the MOST RECENT membership year for which the MemberID shows a renewal. Unfortunately, I don't know how to get SQL to tell me the maximum MembershipYear value for each MemberID value. Can you help? Muchos gracias, merci beaucoup, bitte, thank you in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    SE MI USA
    Posts
    2

    Re: Finding the maximum for each...

    Ummm, never mind. I figured out how to dio this using DMax Specifically, I added the following criteria to the MembershipYear field in the query:

    DMax("MembershipYear","MembershipStatus")

    If anyone sees a problem with this method, please holler - but it seems to be working with my test data.

    Originally posted by TheJanitor
    Here's the problem I'm stuck on: I want to create a query that only shows me the MOST RECENT membership year for which the MemberID shows a renewal. Unfortunately, I don't know how to get SQL to tell me the maximum MembershipYear value for each MemberID value. Can you help? Muchos gracias, merci beaucoup, bitte, thank you in advance!

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    dmax function sounds right.

    create a select query that show the fields that you would want
    then at the top of the query design menu there is the greek character for summa (sp) it looks like an "E"
    click that button where the date column is on the qbe grid (fileds listed)
    there will be a new line called "Total". Click the down arrow and select max (the last date) and then run the query. As long as the Id are in fact unique, you will return one row for each id , and that row will display the maximum (last) date as the criteria.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •