DMax; also query design philosophy

[Lisa Reber]

This started back on Feb 20, with lots of great
information from John V. It has gotten off the subject of
poor table design to a query using DMax. Here's my
original request:
recently added a payments table that shows
latest contribution, how much money is membership
and how much is support, etc. Have linked the payments
to contacts successfully, but now have a problem with
pulling out the most recent renewal date (membership
date + 365). A member has many payments, thus many
renewal dates, and my reminder notice works off a query
prompting for a range of renewal dates. As I re-read
this, it sounds like it should work, but doesn't.

Hi John - finally got to (had to) get this done. In your

A Query with a criterion on the membership date of
=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID] = " & [MemberID])
will select only the most recent membership record.

Why does this work? I ran it the first time without any
memberID parameters, and got the highest membership date
in all member$ payments, which makes sense. Adding the

, "[MemberID] = " & [MemberID])

optional criteria gave me exactly
what I wanted (THANKS!) each member's most recent
membership payment, and I'm curious. I'd also like to be
able to re-create it myself in the future! Thanks also for
clarifying syntax, below. I guess maybe you have already
answered my question above with the comments about SQL
below. But I'm at the point where I know that I don't know
anything, I just want to.
Second, the way I have this set up is:
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above
query: "Renew or Second Letter" (with membership
date prompt to extract only a range of dates) looking at
qryMaxMemberDate. This third-generation query seems to be
cumbersome, not in itself, but in that it has to drill
down (or up?) to produce the desired results. More a
policy question than procedure, I guess, but it would be
good to do stuff correctly henceforth, after almost three
years of blind guesses.

Regards, Lisa

-----Original Message-----

DMax("[field name]", "[table name]", "<optional criteria>")



.

Not there yet, tho' it's on my mind. So does it need all
the paren's quotes and brackets? thanks one mo' time!

It needs two parentheses: any Function call is followed by a pair of
parentheses containing its arguments. So you need

DMax( <some stuff> )

The "stuff" in this case consists of three text strings. They can be
literal text strings in quotation marks or they can be string
variables; but the first of them must be the name of a Field (the
field you're finding the maximum value *of*); the second of them must
be the name of a Table or a Query (the domain, it's called; where to
find this field).

The third argument is optional; it's a valid SQL WHERE clause without
the word WHERE that selects which records to consider in searching for
the maximum value. It's the trickiest because it's very often built up
piecewise from string constants and VBA variables or form referernces.
For instance to fing the maximum (most recent) date for a particular
customerID in a table of sales to customers, you might have

DMax("[SaleDate]", "[tblSales]", "[CustomerID] = " &
Me![txtCustomerID])

The square brackets around fieldnames and tablenames or control names
are optional *unless* those names contain blanks or special
characters, in which case they are obligatory - I habitually include
them, just as a visual reminder to me that I'm talking about a table
or form object rather than a VBA variable.

 

[John Vinson]

This started back on Feb 20, with lots of great
information from John V. It has gotten off the subject of
poor table design to a query using DMax. Here's my
original request:
recently added a payments table that shows
latest contribution, how much money is membership
and how much is support, etc. Have linked the payments
to contacts successfully, but now have a problem with
pulling out the most recent renewal date (membership
date + 365). A member has many payments, thus many
renewal dates, and my reminder notice works off a query
prompting for a range of renewal dates. As I re-read
this, it sounds like it should work, but doesn't.

You've lost me. What are you in fact doing? What's not working?

 

[Lisa Reber]

John - thanks for the reply, and sorry to lose you. This
wasn't an entirely new post, so I put stuff in for
context. Getting rid of all that - I have a table for
contacts and another table for payments. I send membership
renewal invoices yearly using a query that prompts for a
range of dates - I just sent letters to everybody who
expires in April. Because I now have a separate payments
table, I have one member, many membership dates. The DMax
instructions you gave me:
=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID]
= " & [MemberID])
works great. (Thank you.) Maybe it's my brain that isn't
working at this point, but 1) how does the 'MemberID'
criteria part work? I'd like to be able to reproduce it.
If the best idea is just to copy blindly, I can do that,
I'm just curious.

2) The way I'm getting results is by
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above using DMax query
query: "Renew Letter" (with membership date
prompt) looking at qryMaxMemberDate.
This seems cumbersome - qryRenewLetter has to look at
qryMaxMemberDate which is looking at
qryContactwithPayments. It also (unsurprisingly?) seems to
run slowly. It doesn't seem like you could use DMax and a
date prompt on the same field in a query. Should I put the
field in the query twice and thus get rid of one query?
Again, thank you very much. I've learned a lot from you
(and others) in the past two weeks!

 

[John Vinson]

=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID]
= " & [MemberID])
works great. (Thank you.) Maybe it's my brain that isn't
working at this point, but 1) how does the 'MemberID'
criteria part work? I'd like to be able to reproduce it.
If the best idea is just to copy blindly, I can do that,
I'm just curious.

The third argument to DMax is just a query - a SQL WHERE clause
without the word WHERE.

If you're in a particular record in a Query, and that record happens
to have MemberID equal to 123, Access will concatenate the literal
text string

[MemberID] =

with the value

123

to give a valid SQL WHERE clause

[MemberID] = 123

DMax() will then search PaymentTable for that MemberID and return the
largest value of MembershipDate.

It's confusing because you're using [MemberID] in two different senses
in the same function call; the first one is a field in PaymentTable,
making up part of the WHERE clause; the second is the *value* of that
field in the Query, outside of the DMax() function.

2) The way I'm getting results is by
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above using DMax query
query: "Renew Letter" (with membership date
prompt) looking at qryMaxMemberDate.
This seems cumbersome - qryRenewLetter has to look at
qryMaxMemberDate which is looking at
qryContactwithPayments. It also (unsurprisingly?) seems to
run slowly. It doesn't seem like you could use DMax and a
date prompt on the same field in a query. Should I put the
field in the query twice and thus get rid of one query?
Again, thank you very much. I've learned a lot from you
(and others) in the past two weeks!

Could you post the SQL of the three queries? I think they should be
collapsible into two, but I'm not sure what they ARE so I can't say
how.

 

[Guest]

-----Original Message-----

=DMax("[MembershipDate]", "[PaymentTable]", "[MemberID]
= " & [MemberID])
works great. (Thank you.) Maybe it's my brain that isn't
working at this point, but 1) how does the 'MemberID'
criteria part work? I'd like to be able to reproduce it.
If the best idea is just to copy blindly, I can do that,
I'm just curious.

The third argument to DMax is just a query - a SQL WHERE clause
without the word WHERE.

If you're in a particular record in a Query, and that record happens
to have MemberID equal to 123, Access will concatenate the literal
text string

[MemberID] =

with the value

123

to give a valid SQL WHERE clause

[MemberID] = 123

DMax() will then search PaymentTable for that MemberID and return the
largest value of MembershipDate.

It's confusing because you're using [MemberID] in two different senses
in the same function call; the first one is a field in PaymentTable,
making up part of the WHERE clause; the second is the *value* of that
field in the Query, outside of the DMax() function.

2) The way I'm getting results is by
Contacts Table
Payments Table
Query: "Contacts with Payment info" looking at both tbls
qryMaxMemberDate looks at above using DMax query
query: "Renew Letter" (with membership date
prompt) looking at qryMaxMemberDate.
This seems cumbersome - qryRenewLetter has to look at
qryMaxMemberDate which is looking at
qryContactwithPayments. It also (unsurprisingly?) seems to
run slowly. It doesn't seem like you could use DMax and a
date prompt on the same field in a query. Should I put the
field in the query twice and thus get rid of one query?
Again, thank you very much. I've learned a lot from you
(and others) in the past two weeks!

Could you post the SQL of the three queries? I think they should be
collapsible into two, but I'm not sure what they ARE so I can't say
how.


.

Yikes - now I know why I run screaming when I get
into 'View SQL'. Anyway, Payments with contact Information
query is:

SELECT DISTINCTROW [BMHV Contacts].ContactID, [Last Name]
& ", " & [First Name] & " " & [MI] AS Donor, [BMHV
Contacts].Prefix, [BMHV Contacts].[First Name], [BMHV
Contacts].MI, [BMHV Contacts].[Last Name], [BMHV
Contacts].Suffix, [BMHV Contacts].Spouse, [BMHV Contacts].
[Address 1], [BMHV Contacts].[Address 2], [BMHV
Contacts].City, [BMHV Contacts].State, [BMHV Contacts].
[Zip Code], [BMHV Contacts].Phone, [BMHV Contacts].Email,
Payments.PaymentID, Payments.PaymentAmount,
Payments.PaymentDate, Payments.PaymentMethod,
Payments.CheckNumber, Payments.[Entry Date],
Payments.CreditCardType, Payments.CreditCardNumber,
Payments.CardholdersName, Payments.CreditCardExpDate,
Payments.[Member Amt], Payments.[Member Amt as Text],
Payments.MemberDate, Payments.[Membership Types],
[MemberDate]+"365" AS [Renew Date], Payments.[Send
Thanks], Payments.[New Member?], Payments.Contact,
Payments.[Cash Contrib], Payments.Project, [BMHV
Contacts].Phone, [BMHV Contacts].Email
FROM [BMHV Contacts] INNER JOIN Payments ON [BMHV
Contacts].ContactID = Payments.ContactID
GROUP BY [BMHV Contacts].ContactID, [Last Name] & ", " &
[First Name] & " " & [MI], [BMHV Contacts].Prefix, [BMHV
Contacts].[First Name], [BMHV Contacts].MI, [BMHV
Contacts].[Last Name], [BMHV Contacts].Suffix, [BMHV
Contacts].Spouse, [BMHV Contacts].[Address 1], [BMHV
Contacts].[Address 2], [BMHV Contacts].City, [BMHV
Contacts].State, [BMHV Contacts].[Zip Code], [BMHV
Contacts].Phone, [BMHV Contacts].Email,
Payments.PaymentID, Payments.PaymentAmount,
Payments.PaymentDate, Payments.PaymentMethod,
Payments.CheckNumber, Payments.[Entry Date],
Payments.CreditCardType, Payments.CreditCardNumber,
Payments.CardholdersName, Payments.CreditCardExpDate,
Payments.[Member Amt], Payments.[Member Amt as Text],
Payments.MemberDate, Payments.[Membership Types], Payments.
[Send Thanks], Payments.[New Member?], Payments.Contact,
Payments.[Cash Contrib], Payments.Project, [BMHV
Contacts].Phone, [BMHV Contacts].Email
ORDER BY [BMHV Contacts].[Last Name];

qryMaxMemberDate is:
SELECT [Payments with contact info].Prefix, [Payments with
contact info].[First Name], [Payments with contact
info].MI, [Payments with contact info].[Last Name],
[Payments with contact info].Suffix, [Payments with
contact info].Spouse, [Payments with contact info].
[Address 1], [Payments with contact info].[Address 2],
[Payments with contact info].City, [Payments with contact
info].State, [Payments with contact info].[Zip Code],
[Payments with contact info].Phone, [Payments with contact
info].Email, [Payments with contact info].MemberDate,
[Payments with contact info].[Member Amt], [Payments with
contact info].[Renew Date]
FROM [Payments with contact info]
WHERE ((([Payments with contact info].MemberDate)=DMax
("[MemberDate]","[Payments with contact info]","[ContactID]
=" & [ContactID])));

and Renew letter query is:
SELECT qryMaxMemberDate.Prefix, qryMaxMemberDate.[First
Name], qryMaxMemberDate.MI, qryMaxMemberDate.[Last Name],
qryMaxMemberDate.Suffix, qryMaxMemberDate.Spouse,
qryMaxMemberDate.[Address 1], qryMaxMemberDate.[Address
2], qryMaxMemberDate.City, qryMaxMemberDate.State,
qryMaxMemberDate.[Zip Code], qryMaxMemberDate.Phone,
qryMaxMemberDate.Email, qryMaxMemberDate.[Member Amt],
qryMaxMemberDate.MemberDate, qryMaxMemberDate.[Renew Date]
FROM qryMaxMemberDate
WHERE (((qryMaxMemberDate.MemberDate) Between [Type the
beginning membership date:] And [Type the ending
membership date:]))
ORDER BY qryMaxMemberDate.[Last Name];

It works well enough, it just seems wrong. Thanks!