DMax Function in Query Works But Not As I Want

[dlhappy]

I am trying to get the DMax function to work differently than it is working.

I am retired and do volunteer work for a non-profit organization, as a Membership Chairman.

I have two related tables: tblMembership and tblMembersTrans.

The Membership table has all membership data, such as names, addresses, etc. The member's transactions are entered on the membership form which has all of the data from tblMembership and a sub form that contains, in some cases, for some members, transactions from 1998. One member has pre-paid his dues up to 2016. This later example will be discussed below.

Presently, after posting the transactions on the subform, I enter the year for the LastMembershipYrPaid field in the main form.

However, it seems since this information is already in related table (and query) for MembersTrans, I should merely be able to link the main form's field to a query for the last membership year paid. Please note that the automatically generated transaction IDs do not necessarily coincide with the last membership year transaction. That is, the last TransID for any member is not necessarily for the last year. For example someone might have paid for 2013 in January 2013 and later, upon being advised that they forgot to pay for 2012, might make a payment in February 2013 for the arrearage in 2012.

I tried a query with the following: DMax("MembershipYear","tblMembersTrans Query")

The problem with that is that instead of showing the last year paid for each member, it only showed the single largest number (the latest year) for 2016, for that one member that I mentioned earlier.

Another way of explaining this is to assume that a bank has many, many customers with checking accounts. They want a listing of the largest single deposit made by each customer/account. The Dmax formula, as it is working above, would only show one single deposit: the largest deposit made by the one customer who made the largest deposit of any customer.

Any help in refining the formula, or other suggestion as to how I can automatically obtain query results that would show the last year (largest number in the MembershipYear field) for each member without having to rely on manually typing in the information in the main form, as I do now, would be appreciated.

Thanks.

 

[plog]

You need to use the last argument of the DSum function (http://www.techonthenet.com/access/functions/domain/dsum.php). It is where you define the criteria to use. Most likely you would feed it the MembershipID.

 

[dlhappy]

Thanks,

I tried using this (It's Dmax not DSum that I want, but for syntax questions, I guess it does not matter). Anyway, I tried just this: DMax("MembershipYear","tblMembersTrans","MemberID") and the returned result listed the one member maximum or last membership year was 2016. That is correct for him and it is the highest year in the database, but I want the last or highest year for every member.

Then I tried the criterion as "MemberID=1" thinking that I would get just the record of that member whose MemberID actually was #1 (there is such a member), but instead, I got a listing of 135 (not all) members, and it showed 2013 as the year for all the 135. But what is worse, is that for member #70, it also showed 2013, and that member's last membership year paid is for 2016, so the returned result is not actually getting the last membership year paid.

I tried different numbers, such as =2 and the returned result was 95 members, all showing 2014, and again showing member #70 this time as 2014 instead of 2016.

If I used "MemberID >0<999" then the returned result was just for member # 70 and correctly showed 2016 for that member. But that is not what I want. I want all members listed with the last membership year for which that member paid.

Although it didn't work correctly, I do appreciate your assistance and the time you have spent trying to help me with this issue. Thanks, again.

 

[Galaxiom]

LastmembershipYrPaid should not be a field in any table.

You should not be using DMax at all but rather an aggregate query on MemberTrans. Include only the MemberID (GroupBy) and MembershipYear (Max) fields.

 

[plog]

..but I want the last or highest year for every member.

For that I would follow Galaxiom's advice and create a query and not use DMax.

 

[dlhappy]

LastmembershipYrPaid should not be a field in any table.

You should not be using DMax at all but rather an aggregate query on MemberTrans. Include only the MemberID (GroupBy) and MembershipYear (Max) fields.

For that I would follow Galaxiom's advice and create a query and not use DMax.

Thanks very much. It works now, exactly as I want!!!