Max Aggregate Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Max Aggregate Query

Katherine Bradshaw

4 years ago

How can I create a max aggregate query to pull up the MOST RECENT among a number of membership payments made by each householdID, but retain the paymentID for that MaxOfPaymentDate to be able to run additional queries without getting duplicates? This is a key function I need to figure out to manage benefits and renewal / member card mailings for annual nonprofit membership payments.

I have the following tables:

HouseholdT with HouseholdID, address, etc.

MemberPaymentT with:
  PaymentID
  HouseholdID (foreign key)
  PaymentDate
  ExpiryDate (last day of month 1 year from payment date)
  MemberLevelID (foreign key)
  PaymentTypeID (foreign key)
  GiftMembership yes/no
  SpecialCase yes/no
  Notes

MemberLevelT with:
  MemberLevelID
  MemberAmount (standardized payment levels)
  MemberLevelName
  MemberDiscount

PaymentTypeT with PaymentTypeID, type (cash, credit, etc.)

I need to be able to run a number of queries based on the paymentID of the MOST RECENT of a household's payments, like:
  1. Each household's current member discount, based on their most recent member payment.
  2. Contact list with payment details for households whose most recent PayentDate was more than 3 months ago AND whose ExpiryDate is within a user-specified date range.
  3. Contact list with payment details and member benefits for households whose most recent PaymentDate is within a user-specified date range.

I had a previous thread on this where I thought maybe I needed a DMAX, but that fizzled.

	Adam Schwanz @Reply 4 years ago
Link to old thread HERE So clarify exactly what you need, we can just code the whole thing instead of messing with query functions. You need your list of households (just your table), their most recent payment (dmax), there current member discount (dlookup) Then what is a contact list? You didn't mention any fields/tables with that information. What fields do you need for #2 and #3?

Katherine Bradshaw OP @Reply

4 years ago

Thanks for linking the previous thread, Adam.

I'm not 100% sure I know all the queries I will need at this point.

The main thing would be if I could generate a "MaxOfPaymentDate" table with the following columns that I can pull a new version on every time I need a new member list or need to send member letters:

Household ID
MaxOfPaymentDate (for each household, as of the date the query is run)
PaymentID for that one particular max payment date for that household

That last data point is the one I'm struggling to figure out how to generate.

If I've got those three things in a list without duplication, I think I could figure out queries on that "MaxOfPaymentDate" table to bring in information from other tables, such as address (the "contact list" info that I didn't list in the HouseholdT in my original post, like address, city, state, zip, phone, email), payment amount, and member discount.

Adam Schwanz @Reply

4 years ago

So make a normal query, bring in HouseHoldT, bring in all the fields (*).

Make a new expression
MaxPayment: DMAX("PaymentDate","MemberPaymentT","HouseHoldID=" & HouseholdID)

Make another expression
MaxPaymentID: DLOOKUP("PaymentID","MemberPaymentT","PaymentDate=#" & [MaxPayment] & "# and HouseholdID=" & HouseholdID)

Another expression
MLID: DLOOKUP("MemberLevelID","MemberPaymentT","PaymentID=" & [MaxPaymentID])

Make another expression
MDiscount: DLOOKUP("MemberDiscount","MemberLevelT","MemberLevelID=" & [MLID])

This should get you a list of your households, the most recent payment date, the paymentID of that payment date, and the membership discount with only needing to bring in the householdT which I assume is all unique values anyways, won't need to group. See if this gets you anywhere close to what you need.

	Adam Schwanz @Reply 4 years ago
If that works somewhat for you, please make sure to watch DLookup and DMax if you havent

Katherine Bradshaw OP @Reply

4 years ago

Wow. Yes, Adam, that worked!!!
I just have a little tweaking to do to deal with null values, and I have to figure out how to deal with one special case where we let a member pay a little differently. (I really wish we wouldn't do that!) But this is what I need!

Thank you so much for this help! This query has been holding me up for weeks.

Thanks for those links as well. The extended cut of the Dlookup video was most helpful. I'm glad I joined as a Silver member to be able to watch that.

	Adam Schwanz @Reply 4 years ago
Glad to hear it. NZ Function might help you on handling null values.

	Katherine Bradshaw OP @Reply 4 years ago
Better yet, I put in criteria on the MaxPayment expression `>=CDate(Date()-730)` This eliminated anyone without a membership payment (no more null values), as well as older payments that won't be relevant to my queries.

Katherine Bradshaw OP @Reply

4 years ago

Here's another dilemma I've run into, trying to use this "MAXQ" as I've modified it.

I created an underlying MemberPaymentNotGiftQ query to eliminate gift membership payments before running MAXQ. I then substituted "MemberPaymentNotGiftQ" for places where "MemberPaymentT" previously appeared.

I added another expression:
Mexpiry: DLookUp("ExpiryMonth","MemberPaymentNotGiftQ","PaymentID=" & [MaxPaymentID])

So far, so good.

Then I used the MAXQ to try to generate a "CurrentMemberQ"
LastName
FirstName
Mdiscount
Mexpiry

I want to enable a user-specified range of expiration dates in this query, so I added the criteria to the

Mexpiry:

>=CDate([Enter First Day of Month, 2 Months Ago])

I got a syntax error. I realized when I tried to sort the CurrentMemberQ without the criteria that Access doesn't know Mexpiry is a date field.

Would I need to Cdate the Mexpiry alias in the MAXQ to fix this? Or in the CurrentMemberQ somehow? I've not hit the right combo / format yet.

	Adam Schwanz @Reply 4 years ago
Sorry missed that you added more to this convo. Did you figure it out Katherine? You could do something like >=DateAdd("m",-2,[Enter Date]) That would give you 2 months prior to the date entered. You may need to cdate it, not sure

Katherine Bradshaw OP @Reply

4 years ago

Hi Adam. I hadn't until just now. I stepped back from this for a week to let my brain untangle.

I went back to my MAXQ and verified that Access was thinking "Mexpiry" was not a date (as well as "MaxPayment").
So I changed the expression to:
Mexpiry: Cdate(DLookUp("ExpiryMonth","MemberPaymentNotGiftQ","PaymentID=" & [MaxPaymentID]))
When I ran the query, Access recognized the field as a date.

Then I ran the CurrentMemberQ query, but was still getting error messages - until I realized that I hadn't saved the MAXQ yet. Once I saved it, everything seemed to work as I needed it to!

Thanks for the alternate criteria suggestion. That one might work if I wanted to go back two months, but I'm wanting to go back to the first day of the month two months ago, just in case of data entry errors. (I have not yet figured out how to set my default expiration date to "the last day of the month one year from today". Richard posted a tip on LDOM, but I haven't had the time yet to figure it out.)

Katherine Bradshaw OP @Reply

4 years ago

OK, maybe it's not working. I compared my previous, less-complicated CurrentMemberQ query (which produced some duplicates) with the results of the current CurrentMemberQ query, and I've found 60 people who should have been on the list, but are not.

I'm going to have to put this away again for another day. I wind up getting completely confused when I try to troubleshoot things. Because I can't get onto a computer that has the Access program on it during a time when it's quiet enough for me to concentrate, I probably won't be able to look at this again until next Saturday. :-(

Katherine Bradshaw OP @Reply

4 years ago

OK, I'm getting a Null value error when I try to sort the MaxPayment parameter. Maybe this is the source of the overall problem? I tried a NZ function, but still getting the error.

MaxPayment: Nz(CDate(DMax("PaymentDate","MemberPaymentNotGiftQ","HouseholdID=" & [HouseholdID])),0)

Criteria: >=CDate(Date()-730)

If I delete both the Nz function and the CDate function, it runs, and I can sort it, but Access doesn't recognize it as a date.

If I delete the criteria, I get a bunch of errors "Syntax error in the date in query expression 'PaymentDate=## and HouseholdID='

	Kevin Robertson @Reply 4 years ago
Try putting the NZ after CDate. `MaxPayment: CDate(Nz(DMax("PaymentDate","MemberPaymentNotGiftQ","HouseholdID=" & [HouseholdID]),0))`

Katherine Bradshaw OP @Reply

4 years ago

Thanks for that tip, Kevin!

If I run a cropped version of the query with just HouseholdT*, the MaxPayment as written, and the MaxPaymentID Dlookup function, it runs beautifully. No null error values, MaxPayment is recognized as a date, and I can sort everything just fine.

However, if I try this trick with the full, complex query, Access gets real laggy and locks up. I've tried restarting the program, compact & repair, restarting the computer. Seem to be too much going on in the long query?

Maybe if I try breaking this into a couple of queries I can get things to work. I'll take a lunch break and try another whack at the problem this afternoon.

Katherine Bradshaw OP @Reply

4 years ago

YES! I think I can do what I need to do by pulling in multiple queries.

I used the "short" query with HouseholdT*, the MaxPayment as written, and the MaxPaymentID Dlookup function.

I had to convert the MaxPaymentID to an integer with a Cint() function so I could later match that number with the PaymentID in other tables in a second query.

I hope I'm not premature, but it might just be time for a celebratory beverage!

Scott Axton @Reply

4 years ago

Katherine -
I didn't study all of both posts in detail in depth, so forgive me if I'm missing something.
I just read through this and one thing popped out at me.  You said, "
However, if I try this trick with the full, complex query, Access gets real laggy and locks up."

Two things to consider:

1. Sometimes you can get the query too complex and Access can't keep up with all the calculations if certain
items need calculated in order to have the results used in the next calculation. (Clear as mud?)

2. That can also affect your speed.  Access is trying to perform the calculations on all the records to give you the final results you want.

So...  Sometimes you are better off using two (or more) queries to get to the end results.
The first query would bring back a smaller sub-set of records to be performing the calculations on.
You then use Query1 as the data source for Query2.

	Scott Axton @Reply 4 years ago
Be careful of converting data too. I would not CDate() or CInt() stuff unless you absolutely need to. Every conversion will count up clock ticks for you. Slowing things down especially if it isn't truly needed. I would make sure of the data going into the query first.

	Scott Axton @Reply 4 years ago
By the way - CInt() may work for you now but depending on how big your database gets your code could break in the future. Since AutoNumbers are of data type Long Integer (2,147,483,647) they could exceed the Integer limit of 32,767. If you need to convert you should use CLng() to "future proof" your code.

Katherine Bradshaw OP @Reply

4 years ago

Thank you for your replies, Scott.

Yes, splitting into two queries did seem to fix the lagginess problem. (Also helped me better understand exactly what I was doing.)

I wound up needing to convert those two fields back to a date and a number, since with the Dmax and Dlookup, Access was seeing them both as alphanumeric fields. Because of the subsequent queries I need to perform, I need them registering as dates and numbers.

Thanks for the "future proofing" tip on Clng() !
I wasn't sure which one to use.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View