Use of "Max" in query grid

[Serious_Practitioner]

Good day, and thank you in advance for any assistance you can provide.

I have a table in an Access 2000 .mdb file, and I've run into something odd
and insolvable, at least for me. The database is for membership information.
This particular table, called tblMembershipInfo, has fields/columns as
follows -

fldMemNum - Member Number
fldActionDate - The date when the entry was made in that row - Data type is
Date/Time, no format specified
fldAction - What the entry is for (new membership, adjustment to exp. date,
renewal, etc.)
fldMembershipType - A code telling me if it's a three month, six month or
one year membership and so forth; there are a number of different codes
fldStartDate - The starting date of the membership - Data type is Date/Time,
no format specified
fldExpDate - The expiration date of the membership - Data type is Date/Time,
no format specified

This table, along with a table of stuff like member names and addresses, is
used to generate a mess of reports, membership cards, promotional lists and
the like. That table is called tblMemberInfo; it is the "one" side of a
"one-to-many" relationship with tblmembershipInfo.

In the past, I've used the "Max" function (on the field called "fldExpDate"
in a query to get people's expiration dates, based on the highest expiration
date for the members from the same table. That query has run reliably for
better than a year and I've checked it a couple of times, at random, against
the contents of the tables.

I now want to get people's expiration dates from the most recent record in
the table for any given member. This seemed simple (famous last words) -
here's the SQL I'm trying now, which comes from the SQL page of the QBE
grid -

SELECT tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, Max(tblMembershipInfo.fldActionDate) AS
MaxOffldActionDate, tblMembershipInfo.fldExpDate
FROM tblMemberInfo INNER JOIN tblMembershipInfo ON tblMemberInfo.fldMemNum =
tblMembershipInfo.fldMemNum
GROUP BY tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, tblMembershipInfo.fldExpDate
ORDER BY tblMemberInfo.fldMemNum;

and the query returns all of the entries in the table (about 700, as some
members have two to five entries), including multiple records for those
member numbers that have multiple records. Again, all I want is the
expiration date from the last entry, based on the most recent Action Date in
the table, for any one of the members. I should wind up with a list of
members about 100-110 names long, each with an expiration date after the
date on which the query is run.

I know that my inexperience is causing me to overlook something. I've
checked randomly selected rows to see that the data is true to the date
type - no spaces, blanks and the like. I've made sure that the data types
agree from one table to the next. but I don't see the cause of this. Can
someone point me in the correct direction?

Many thanks.
Steve E.

[Phil Stanton]

I think that the problem may be including the tblMembershipInfo.fldExpDate
in your query. There will be a different value for each
tblMembershipInfo.fldActionDate

Could be wrong though

Phil

"Serious_Practitioner" <Se************************@att.net> wrote in message
news:Jb*********************@bgtnsc04-news.ops.worldnet.att.net...

Good day, and thank you in advance for any assistance you can provide.

I have a table in an Access 2000 .mdb file, and I've run into something
odd and insolvable, at least for me. The database is for membership
information. This particular table, called tblMembershipInfo, has
fields/columns as follows -

fldMemNum - Member Number
fldActionDate - The date when the entry was made in that row - Data type
is Date/Time, no format specified
fldAction - What the entry is for (new membership, adjustment to exp.
date, renewal, etc.)
fldMembershipType - A code telling me if it's a three month, six month or
one year membership and so forth; there are a number of different codes
fldStartDate - The starting date of the membership - Data type is
Date/Time, no format specified
fldExpDate - The expiration date of the membership - Data type is
Date/Time, no format specified

This table, along with a table of stuff like member names and addresses,
is used to generate a mess of reports, membership cards, promotional lists
and the like. That table is called tblMemberInfo; it is the "one" side of
a "one-to-many" relationship with tblmembershipInfo.

In the past, I've used the "Max" function (on the field called
"fldExpDate" in a query to get people's expiration dates, based on the
highest expiration date for the members from the same table. That query
has run reliably for better than a year and I've checked it a couple of
times, at random, against the contents of the tables.

I now want to get people's expiration dates from the most recent record in
the table for any given member. This seemed simple (famous last words) -
here's the SQL I'm trying now, which comes from the SQL page of the QBE
grid -

SELECT tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, Max(tblMembershipInfo.fldActionDate) AS
MaxOffldActionDate, tblMembershipInfo.fldExpDate
FROM tblMemberInfo INNER JOIN tblMembershipInfo ON tblMemberInfo.fldMemNum
= tblMembershipInfo.fldMemNum
GROUP BY tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, tblMembershipInfo.fldExpDate
ORDER BY tblMemberInfo.fldMemNum;

and the query returns all of the entries in the table (about 700, as some
members have two to five entries), including multiple records for those
member numbers that have multiple records. Again, all I want is the
expiration date from the last entry, based on the most recent Action Date
in the table, for any one of the members. I should wind up with a list of
members about 100-110 names long, each with an expiration date after the
date on which the query is run.

I know that my inexperience is causing me to overlook something. I've
checked randomly selected rows to see that the data is true to the date
type - no spaces, blanks and the like. I've made sure that the data types
agree from one table to the next. but I don't see the cause of this. Can
someone point me in the correct direction?

Many thanks.
Steve E.

[Serious_Practitioner]

Hi, Phil -

Thank you.

I guess I didn't understand the meaning of the term "Max". I thought I'd get
the row with the highest Action date for each member, and then get the Exp
date from that. I followed your suggestion - I took the ExpDate out and I
now get the most recent Action dates - I'll make another query to extract
the most recent Exp Date. Thank you for helping me move on.
Steve E.

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:42***********************@ptn-nntp-reader02.plus.net...

I think that the problem may be including the tblMembershipInfo.fldExpDate
in your query. There will be a different value for each
tblMembershipInfo.fldActionDate

Could be wrong though

Phil

"Serious_Practitioner" <Se************************@att.net> wrote in
message news:Jb*********************@bgtnsc04-news.ops.worldnet.att.net...

Good day, and thank you in advance for any assistance you can provide.

I have a table in an Access 2000 .mdb file, and I've run into something
odd and insolvable, at least for me. The database is for membership
information. This particular table, called tblMembershipInfo, has
fields/columns as follows -

fldMemNum - Member Number
fldActionDate - The date when the entry was made in that row - Data type
is Date/Time, no format specified
fldAction - What the entry is for (new membership, adjustment to exp.
date, renewal, etc.)
fldMembershipType - A code telling me if it's a three month, six month or
one year membership and so forth; there are a number of different codes
fldStartDate - The starting date of the membership - Data type is
Date/Time, no format specified
fldExpDate - The expiration date of the membership - Data type is
Date/Time, no format specified

This table, along with a table of stuff like member names and addresses,
is used to generate a mess of reports, membership cards, promotional
lists and the like. That table is called tblMemberInfo; it is the "one"
side of a "one-to-many" relationship with tblmembershipInfo.

In the past, I've used the "Max" function (on the field called
"fldExpDate" in a query to get people's expiration dates, based on the
highest expiration date for the members from the same table. That query
has run reliably for better than a year and I've checked it a couple of
times, at random, against the contents of the tables.

I now want to get people's expiration dates from the most recent record
in the table for any given member. This seemed simple (famous last
words) - here's the SQL I'm trying now, which comes from the SQL page of
the QBE grid -

SELECT tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, Max(tblMembershipInfo.fldActionDate) AS
MaxOffldActionDate, tblMembershipInfo.fldExpDate
FROM tblMemberInfo INNER JOIN tblMembershipInfo ON
tblMemberInfo.fldMemNum = tblMembershipInfo.fldMemNum
GROUP BY tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, tblMembershipInfo.fldExpDate
ORDER BY tblMemberInfo.fldMemNum;

and the query returns all of the entries in the table (about 700, as some
members have two to five entries), including multiple records for those
member numbers that have multiple records. Again, all I want is the
expiration date from the last entry, based on the most recent Action Date
in the table, for any one of the members. I should wind up with a list of
members about 100-110 names long, each with an expiration date after the
date on which the query is run.

I know that my inexperience is causing me to overlook something. I've
checked randomly selected rows to see that the data is true to the date
type - no spaces, blanks and the like. I've made sure that the data types
agree from one table to the next. but I don't see the cause of this. Can
someone point me in the correct direction?

Many thanks.
Steve E.

[Serious_Practitioner]

Hi, Phil -

Thank you.

I guess I didn't understand the meaning of the term "Max". I thought I'd get
the row with the highest Action date for each member, and then get the Exp
date from that. I followed your suggestion - I took the ExpDate out and I
now get the most recent Action dates - I'll make another query to extract
the most recent Exp Date. Thank you for helping me move on.
Steve E.

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:42***********************@ptn-nntp-reader02.plus.net...

I think that the problem may be including the tblMembershipInfo.fldExpDate
in your query. There will be a different value for each
tblMembershipInfo.fldActionDate

Could be wrong though

Phil

"Serious_Practitioner" <Se************************@att.net> wrote in
message news:Jb*********************@bgtnsc04-news.ops.worldnet.att.net...

Good day, and thank you in advance for any assistance you can provide.

I have a table in an Access 2000 .mdb file, and I've run into something
odd and insolvable, at least for me. The database is for membership
information. This particular table, called tblMembershipInfo, has
fields/columns as follows -

fldMemNum - Member Number
fldActionDate - The date when the entry was made in that row - Data type
is Date/Time, no format specified
fldAction - What the entry is for (new membership, adjustment to exp.
date, renewal, etc.)
fldMembershipType - A code telling me if it's a three month, six month or
one year membership and so forth; there are a number of different codes
fldStartDate - The starting date of the membership - Data type is
Date/Time, no format specified
fldExpDate - The expiration date of the membership - Data type is
Date/Time, no format specified

This table, along with a table of stuff like member names and addresses,
is used to generate a mess of reports, membership cards, promotional
lists and the like. That table is called tblMemberInfo; it is the "one"
side of a "one-to-many" relationship with tblmembershipInfo.

In the past, I've used the "Max" function (on the field called
"fldExpDate" in a query to get people's expiration dates, based on the
highest expiration date for the members from the same table. That query
has run reliably for better than a year and I've checked it a couple of
times, at random, against the contents of the tables.

I now want to get people's expiration dates from the most recent record
in the table for any given member. This seemed simple (famous last
words) - here's the SQL I'm trying now, which comes from the SQL page of
the QBE grid -

SELECT tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, Max(tblMembershipInfo.fldActionDate) AS
MaxOffldActionDate, tblMembershipInfo.fldExpDate
FROM tblMemberInfo INNER JOIN tblMembershipInfo ON
tblMemberInfo.fldMemNum = tblMembershipInfo.fldMemNum
GROUP BY tblMemberInfo.fldMemNum, tblMemberInfo.fldFirstName,
tblMemberInfo.fldLastName, tblMembershipInfo.fldExpDate
ORDER BY tblMemberInfo.fldMemNum;

and the query returns all of the entries in the table (about 700, as some
members have two to five entries), including multiple records for those
member numbers that have multiple records. Again, all I want is the
expiration date from the last entry, based on the most recent Action Date
in the table, for any one of the members. I should wind up with a list of
members about 100-110 names long, each with an expiration date after the
date on which the query is run.

I know that my inexperience is causing me to overlook something. I've
checked randomly selected rows to see that the data is true to the date
type - no spaces, blanks and the like. I've made sure that the data types
agree from one table to the next. but I don't see the cause of this. Can
someone point me in the correct direction?

Many thanks.
Steve E.