DMAX for ALL Customers - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DMAX for ALL Customers

Katherine Bradshaw

4 years ago

I've finished lessons for Access Expert up to lesson 15. A search of this site shows me I won't be learning DMAX until Expert 28. Unfortunately, I've got a problem I need to solve now that might require dmax, and I can't figure it out.

Here's what I have, and what I need to do:

HouseholdT - has all contact info
MemberPaymentT - has all payment date and expiry date

I need to send an annual membership renewal notice to anyone with an expiry date in the past three months who hasn't already paid.

I figure if I can get a list of ONLY each household's most recent payment, then filter that list of payments to include only those with expiration dates in a 3-month span (which the user would be prompted to enter with "between [Start Date] and [End Date]" criteria), that should work.

I've been going round and round trying different queries with <= or BETWEEN criteria, but I can't figure out how to isolate the most recent payment (which could be a year ago if they are due, or a month ago if they are paid up).

I thought maybe a DMAX would help, but that only shows me the handful of households who happened to have paid on the last date that shows up in the queries.

Can I write a DMAX that would give me a list of every household showing ONLY their most recent payment? Or is there another solution for a novice like myself that I'm just not thinking of?

Thank you!

	Adam Schwanz @Reply 4 years ago
You should be able to use a Aggregate Query and select MAX.

	Alex Hedley @Reply 4 years ago
Untested, what does: `SELECT TOP 1 * FROM MemberPaymentT ORDER BY PaymentDate DESC GROUP BY HouseholdID` Assumed field names

Katherine Bradshaw OP @Reply

4 years ago

Adam, the aggregate query max isn't working for me. It's still showing all the payments. I also tried using the "return" option in the ribbon and typed in "1". That just returned one result, not the "top 1 for each household" I was hoping for.

Alex, would I type that into the criteria for PaymentDate in the query? Or somewhere else? I've tried typing what you suggested, but I'm getting a syntax error - something about needing parentheses in a subquery.

	Adam Schwanz @Reply 4 years ago
Are you grouping by the memberID? Like this image, would get the partID, throw them all together, then only give me the highest price out of all of those. It would work the same way for you.

	Adam Schwanz @Reply 4 years ago

	Katherine Bradshaw OP @Reply 4 years ago
Adam, yes, I tried it like that, but I'm still getting 300 records, and I should have about half that. (I tried a screenshot, but it's too blurry.)

	Katherine Bradshaw OP @Reply 4 years ago
OH WAIT!!! I was trying to group by PAYMENT ID, so of course it was showing all the payments. I just corrected it to HOUSEHOLD ID, and it works now!!!! Thank you for that screenshot. It helped my brain click!

	Adam Schwanz @Reply 4 years ago
Glad to hear it's working now :).

Katherine Bradshaw OP @Reply

4 years ago

Oh, rats. I'm still running into hiccups.

I tried a second query with the "max payment" query and the MemberPaymentT as the data sources to be able to bring additional payment info in, joined all three ways (I had to do a manual join), and I kept getting 300 results again, every time.

I decided to see what would happen if I brought in the HouseholdT and the MemberPaymentT and let that join set up, and then add in the payment date from the "max payment" query, with a criteria of <=CDate(Date()-100) to eliminate the most recent payments. Which worked! BUT, it's showing duplicates of some of the records.

	Richard Rost @Reply 4 years ago
You generally need to limit the records with criteria BEFORE aggregating them. Make a query with criteria, then use THAT result set to create your aggregate.

Katherine Bradshaw OP @Reply

4 years ago

Thank you so much for that tip, Richard. I will keep that in mind as I continue my education.

However, that solution will give me the wrong results. If I filter out the newer payments first, I'll wind up sending "please pay your annual membership" letters to people who just paid.

Here's a data example that shows what I'm trying to do every month:

Name - membership payment date(s)
James - 10/2/20
Leonard - 10/15/20
Spock - 10/18/20, 10/12/21
Nyota - 11/3/20, 11/6/21
Montgomery - 11/6/20
Pavel - 11/13/20, 11/20/21

I filter by most recent payment, and I get

James - 10/2/20
Leonard - 10/15/20
Spock - 10/12/21
Nyota - 11/6/21
Montgomery - 11/6/20
Pavel - 11/20/21

Spock, Nyota, and Pavel paid their annual memberships recently, so I don't want to send them renewal letters, so that's where I did the <=Date()-100 (but wound up with seemingly random duplication of some records)

James - 10/2/20
Leonard - 10/15/20
Leonard - 10/15/20
Montgomery - 11/6/20

	Adam Schwanz @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
I don't have a date field, but you should be able to do it just like that, is that how you tried it?

	Richard Rost @Reply 4 years ago
Ok. I see. Still use 2 queries. Calculate max payment first then feed those results to a 2nd query that limits by date.

Katherine Bradshaw OP @Reply

4 years ago

Adam,
I've tried a where function, but it didn't work for me. It seems like my trouble is, I do the MAX aggregation on PaymentDate from my MemberPaymentsT, but then later I need more information from that MemberPaymentsT and it's like Access forgets about the MAX filter for the people selected for the list, because it's bringing in duplicates. Maybe it's my joins? Brief description below, and I'll add screenshots.

Q1 selects the max PaymentDate.MemberPaymentT
Q2 selects only those MaxOfPaymentDate.Query1 that are more than 100 days ago. So far so good!

Q3 selects the range of expiration dates with user input ExpiryMonth.MemberPaymentT criteria BETWEEN [Enter first day of month, two months ago] AND [Enter last day of current month] and brings in more membership info from MemberPaymentT. It's in Query 3 where data is getting pulled in that should have been knocked out by the Query1 MAX function.

Q4 then brings in the HouseholdID.Query3, and adds in mailing address.

	Katherine Bradshaw OP @Reply 4 years ago

	Katherine Bradshaw OP @Reply 4 years ago

	Katherine Bradshaw OP @Reply 4 years ago

	Katherine Bradshaw OP @Reply 4 years ago

Katherine Bradshaw OP @Reply

4 years ago

I've got a couple of places in my database where I want to filter for "most recent payment" that also has "an expiration date within a user-entered timeframe" - all data within the MemberPaymentT. I keep bumping into Access "forgetting" that first "max payment date" aggregate query when I try to use anything else in that MemberPaymentT in another query.

How can I force Access to ONLY select from among the records returned with that MAX aggregate query?
I've tried changing joins in the query, but so far have had no luck, or SQL outer join error messages.
Maybe the paymentID is necessary in these steps somewhere that I've missed?

	Richard Rost @Reply 4 years ago
I don't see a problem with what you've got there, but it's hard to troubleshoot without seeing the data and being able to play with it.

	Adam Schwanz @Reply 4 years ago
Richard this post continued HERE if you can maybe combine them?

	Richard Rost @Reply 4 years ago
No way. They're long enough. LOL

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

Next Unseen

New Feature: Comment Live View