Largest Value in a table with additional criteria.

[Guest]

Hello,

I have a query looking like this :

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......62%.....03-JUN-03..03-DEC-03
Steve......73%.....02-DEC-03..02-MAY-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........57%.....05-FEB-02..05-FEB-03
Tom........83%.....05-FEB-03..05-FEB-04

And I want it to look like this:

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........83%.....05-FEB-03..05-FEB-04

Basically, I have a historical table with historical data, but I only want
to show the most current due date, or highest date/number in a certain field
for each name.

Thank you,

-Sen.

 

[Marshall Barton]

I have a query looking like this :

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......62%.....03-JUN-03..03-DEC-03
Steve......73%.....02-DEC-03..02-MAY-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........57%.....05-FEB-02..05-FEB-03
Tom........83%.....05-FEB-03..05-FEB-04

And I want it to look like this:

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........83%.....05-FEB-03..05-FEB-04

Basically, I have a historical table with historical data, but I only want
to show the most current due date, or highest date/number in a certain field
for each name.

Assuming that the Date Due field really is a Date type
field:

SELECT T.[Name], T.Score, T.Tested, T.[Date Due]
FROM table As T
WHERE T.[Date Due] = (SELECT Max(X.[Date Due])
FROM table As X
WHERE X.[Name] = T.[Name])

 

[Guest]

Thank you Marshall!

I do have another question though...

Does this work for numerical autonumber values as well? I did try this same
format for another query based off of a "refresh" field that shows like an
update transaction number. So my goal is to get, out of this history of
transactions, the most current transaction for the Name.

Same situation as the one below but this time I'm trying to do it on a
autonumber, but when I try the query format below, all I get is the highest
number (one record) and not the same results I get for the below query.

Thanks!

-Sen.

I have a query looking like this :

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......62%.....03-JUN-03..03-DEC-03
Steve......73%.....02-DEC-03..02-MAY-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........57%.....05-FEB-02..05-FEB-03
Tom........83%.....05-FEB-03..05-FEB-04

And I want it to look like this:

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........83%.....05-FEB-03..05-FEB-04

Basically, I have a historical table with historical data, but I only want
to show the most current due date, or highest date/number in a certain field
for each name.

Assuming that the Date Due field really is a Date type
field:

SELECT T.[Name], T.Score, T.Tested, T.[Date Due]
FROM table As T
WHERE T.[Date Due] = (SELECT Max(X.[Date Due])
FROM table As X
WHERE X.[Name] = T.[Name])

 

[Marshall Barton]

Yes, the concept works for number type fields.

Depending on the contents of the field, it may appear to not
work for Text fields. It actually does work, but I've seen
people complain because a text field with 101 is less than
23.

The problem with doing it on an autonumber field is that you
don't have a "name" field to "group" records by, so you only
find the one record. Think about what you are trying to do
and post back with more details if you need further
assistance.
--
Marsh
MVP [MS Access]

Thank you Marshall!

I do have another question though...

Does this work for numerical autonumber values as well? I did try this same
format for another query based off of a "refresh" field that shows like an
update transaction number. So my goal is to get, out of this history of
transactions, the most current transaction for the Name.

Same situation as the one below but this time I'm trying to do it on a
autonumber, but when I try the query format below, all I get is the highest
number (one record) and not the same results I get for the below query.

I have a query looking like this :

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......62%.....03-JUN-03..03-DEC-03
Steve......73%.....02-DEC-03..02-MAY-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........57%.....05-FEB-02..05-FEB-03
Tom........83%.....05-FEB-03..05-FEB-04

And I want it to look like this:

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........83%.....05-FEB-03..05-FEB-04

Basically, I have a historical table with historical data, but I only want
to show the most current due date, or highest date/number in a certain field
for each name.

Assuming that the Date Due field really is a Date type
field:

SELECT T.[Name], T.Score, T.Tested, T.[Date Due]
FROM table As T
WHERE T.[Date Due] = (SELECT Max(X.[Date Due])
FROM table As X
WHERE X.[Name] = T.[Name])

 

[Guest]

Hmmmm,

This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].

Let me know if any more detail is required...

Thank you!

-Sen.

Yes, the concept works for number type fields.

Depending on the contents of the field, it may appear to not
work for Text fields. It actually does work, but I've seen
people complain because a text field with 101 is less than
23.

The problem with doing it on an autonumber field is that you
don't have a "name" field to "group" records by, so you only
find the one record. Think about what you are trying to do
and post back with more details if you need further
assistance.
--
Marsh
MVP [MS Access]

Thank you Marshall!

I do have another question though...

Does this work for numerical autonumber values as well? I did try this same
format for another query based off of a "refresh" field that shows like an
update transaction number. So my goal is to get, out of this history of
transactions, the most current transaction for the Name.

Same situation as the one below but this time I'm trying to do it on a
autonumber, but when I try the query format below, all I get is the highest
number (one record) and not the same results I get for the below query.

Senexis wrote:
I have a query looking like this :

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......62%.....03-JUN-03..03-DEC-03
Steve......73%.....02-DEC-03..02-MAY-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........57%.....05-FEB-02..05-FEB-03
Tom........83%.....05-FEB-03..05-FEB-04

And I want it to look like this:

Name.....Score.....Tested.....Date Due
Bob........87%.....05-FEB-02..05-FEB-03
Steve......75%.....05-MAY-03..05-MAY-04
Tom........83%.....05-FEB-03..05-FEB-04

Basically, I have a historical table with historical data, but I only want
to show the most current due date, or highest date/number in a certain field
for each name.


Assuming that the Date Due field really is a Date type
field:

SELECT T.[Name], T.Score, T.Tested, T.[Date Due]
FROM table As T
WHERE T.[Date Due] = (SELECT Max(X.[Date Due])
FROM table As X
WHERE X.[Name] = T.[Name])

 

[Marshall Barton]

This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].

Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Guest]

Well these are linked tables, but none the less I did check the type fields,
and they are all what I said they were just to make sure. I even tried
making another field in the source query called refresher_1 which was really
([refresher]*1) , and although it was the same data, it was no longer an
autonumber field (at least it didn't show "(autonumber)" as the next record).
And then tried the same format on that, but still did not work.

Any other thoughts? :-S

This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].

Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Marshall Barton]

What linked tables? All I can see is the thing named
Pqry_PRIMARY_Status

What do you see when you open Pqry_PRIMARY_Status directly
from the db window? Are there really different values in
the [login_id] field?
--
Marsh
MVP [MS Access]

Well these are linked tables, but none the less I did check the type fields,
and they are all what I said they were just to make sure. I even tried
making another field in the source query called refresher_1 which was really
([refresher]*1) , and although it was the same data, it was no longer an
autonumber field (at least it didn't show "(autonumber)" as the next record).
And then tried the same format on that, but still did not work.

Any other thoughts? :-S

This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].

Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Guest]

Under the table object for Access, the main table is really a "Linked ODBC"
from a SQL table. What I did was created a primary query that I base all my
other queries, forms, reports, etc. on. So Pqry_PRIMARY_Status is the main
query by which I run all other queries off. As far ast he [login_id] field,
this is a text field for the individual's name.

What linked tables? All I can see is the thing named
Pqry_PRIMARY_Status

What do you see when you open Pqry_PRIMARY_Status directly
from the db window? Are there really different values in
the [login_id] field?
--
Marsh
MVP [MS Access]

Well these are linked tables, but none the less I did check the type fields,
and they are all what I said they were just to make sure. I even tried
making another field in the source query called refresher_1 which was really
([refresher]*1) , and although it was the same data, it was no longer an
autonumber field (at least it didn't show "(autonumber)" as the next record).
And then tried the same format on that, but still did not work.

Any other thoughts? :-S

Senexis wrote:
This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].


Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Marshall Barton]

Unless there is some funny interaction with ODBC/SQL Server,
I just don't see how that query can return a single row when
there are different values in the loginID field.

All I can suggest is that you should fall back to basic
debugging techniques of breaking the query down to its
component parts and check each one to see if you determine
where things are going wrong.
--
Marsh
MVP [MS Access]

Under the table object for Access, the main table is really a "Linked ODBC"
from a SQL table. What I did was created a primary query that I base all my
other queries, forms, reports, etc. on. So Pqry_PRIMARY_Status is the main
query by which I run all other queries off. As far ast he [login_id] field,
this is a text field for the individual's name.

What linked tables? All I can see is the thing named
Pqry_PRIMARY_Status

What do you see when you open Pqry_PRIMARY_Status directly
from the db window? Are there really different values in
the [login_id] field?

Well these are linked tables, but none the less I did check the type fields,
and they are all what I said they were just to make sure. I even tried
making another field in the source query called refresher_1 which was really
([refresher]*1) , and although it was the same data, it was no longer an
autonumber field (at least it didn't show "(autonumber)" as the next record).
And then tried the same format on that, but still did not work.

Any other thoughts? :-S

:

Senexis wrote:
This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].


Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Guest]

Marshall,

I tried the following SQL :

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;


This returns more records than just the one, but this time it's a faction of
the number of individuals out there. Did I do this one right, or should I
just scrub it?

I'm now going straight to the table as you can see by the above SQL,
[refresher] is still an autonumber field however I noticed something that was
different, and I don't know if it makes any difference or not, but it's also
a primary key field, along with two other fields.

I also tried performing this subquery max format on an altered refresher
field by making it both text and numeric, but either didn't get anywhere or
it just froze the DB up. There are over 40 thousand records in this table.

Is there another way this could be done? Or is there somewhere you would
suggest to research doing subqueries for max values?

Thanks again!

-Sen.

Unless there is some funny interaction with ODBC/SQL Server,
I just don't see how that query can return a single row when
there are different values in the loginID field.

All I can suggest is that you should fall back to basic
debugging techniques of breaking the query down to its
component parts and check each one to see if you determine
where things are going wrong.
--
Marsh
MVP [MS Access]

Under the table object for Access, the main table is really a "Linked ODBC"
from a SQL table. What I did was created a primary query that I base all my
other queries, forms, reports, etc. on. So Pqry_PRIMARY_Status is the main
query by which I run all other queries off. As far ast he [login_id] field,
this is a text field for the individual's name.

What linked tables? All I can see is the thing named
Pqry_PRIMARY_Status

What do you see when you open Pqry_PRIMARY_Status directly
from the db window? Are there really different values in
the [login_id] field?


Senexis wrote:

Well these are linked tables, but none the less I did check the type fields,
and they are all what I said they were just to make sure. I even tried
making another field in the source query called refresher_1 which was really
([refresher]*1) , and although it was the same data, it was no longer an
autonumber field (at least it didn't show "(autonumber)" as the next record).
And then tried the same format on that, but still did not work.

Any other thoughts? :-S

:

Senexis wrote:
This is how my SQL statement looks like now :

SELECT T.course_key, T.login_id, T.refresher, T.idp_status, T.Complied_date,
T.Due_Date
FROM Pqry_PRIMARY_Status AS T
WHERE (((T.refresher)=(SELECT Max(X.[refresher])
FROM Pqry_PRIMARY_Status As X
WHERE X.[login_id] = T.[login_id])));


The [login_id] is the name, and the [refresher] is the autonumber field.
The autonumber [refresher] field is how the historical table keeps track of
what's the most current record, I can't change the autonumber bit, but even
when I try to group sort by login_id and then by say the [complied_date] (a
date field, just as in the previous SQL example) I still only get one record.

None the less, I would very much like to group sort by [login_id] and then
by the autonumber field [refresher].


Other than some excess parenthesis, that looks good to me.

I guess the next step is to investigate the source
table/query, Pqry_PRIMARY_Status, to make sure that our
query is getting the data you think it's supposed to get.

 

[Marshall Barton]

I tried the following SQL :

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;


This returns more records than just the one, but this time it's a faction of
the number of individuals out there. Did I do this one right, or should I
just scrub it?

I'm now going straight to the table as you can see by the above SQL,
[refresher] is still an autonumber field however I noticed something that was
different, and I don't know if it makes any difference or not, but it's also
a primary key field, along with two other fields.

I also tried performing this subquery max format on an altered refresher
field by making it both text and numeric, but either didn't get anywhere or
it just froze the DB up. There are over 40 thousand records in this table.

Is there another way this could be done? Or is there somewhere you would
suggest to research doing subqueries for max values?

The query looks logical to me, but it's unclear where the
status comes into it. Unfortunately, 40K records is too
many to visually verify the query's results.

Is there some way you can operate on a copy of the table
that only has a few dozen representative records? If so, it
would allow you to check the source data to see exactly what
results to expect from the query.

As for your remark about refresher being an autonumber field
that is only part of a compound primary key, I don't see how
that can either make sense or interfere with the query.
It's worth some time thinking about why a unique field would
be part of a compound index though.

 

[Guest]

Marshall,

I may have found another dimension to this problem... So far we've down
what looks like to me as a subquery with one critria, "login_id". This takes
the highest [refresher] value out of a group of the same [login_id].

I really need to have the higest [refresher] value out of the [course_key]
out of the [login_id]. So taking the bottom SQL statement into
consideration, how do we make it return the highest [refresher] value for
each [course_key] value under each [login_id]?

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;


Thank you again!

-Sen.

I tried the following SQL :

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;


This returns more records than just the one, but this time it's a faction of
the number of individuals out there. Did I do this one right, or should I
just scrub it?

I'm now going straight to the table as you can see by the above SQL,
[refresher] is still an autonumber field however I noticed something that was
different, and I don't know if it makes any difference or not, but it's also
a primary key field, along with two other fields.

I also tried performing this subquery max format on an altered refresher
field by making it both text and numeric, but either didn't get anywhere or
it just froze the DB up. There are over 40 thousand records in this table.

Is there another way this could be done? Or is there somewhere you would
suggest to research doing subqueries for max values?

The query looks logical to me, but it's unclear where the
status comes into it. Unfortunately, 40K records is too
many to visually verify the query's results.

Is there some way you can operate on a copy of the table
that only has a few dozen representative records? If so, it
would allow you to check the source data to see exactly what
results to expect from the query.

As for your remark about refresher being an autonumber field
that is only part of a compound primary key, I don't see how
that can either make sense or interfere with the query.
It's worth some time thinking about why a unique field would
be part of a compound index though.

 

[Marshall Barton]

I don't know where the course key is coming from, but if
it's in tbl_TRAINING, then I think all you need is to add
another criteria to the subquery:

SELECT T.course_key, T.login_id, T.refresher,
T.idp_cmplt_dt AS Complied_date,
T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]
AND T.course_key = X.course_key)
AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher
--
Marsh
MVP [MS Access]

I may have found another dimension to this problem... So far we've down
what looks like to me as a subquery with one critria, "login_id". This takes
the highest [refresher] value out of a group of the same [login_id].

I really need to have the higest [refresher] value out of the [course_key]
out of the [login_id]. So taking the bottom SQL statement into
consideration, how do we make it return the highest [refresher] value for
each [course_key] value under each [login_id]?

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;

I tried the following SQL :

SELECT T.course_key, T.login_id, T.refresher, T.idp_cmplt_dt AS
Complied_date, T.idp_req_cmplt_dt AS Due_Date
FROM tbl_TRAINING AS T
WHERE T.refresher=(SELECT Max(X.[refresher])
FROM tbl_TRAINING X
WHERE X.[login_id] = t.[login_id]) AND ((T.idp_status) Not Like "Unscheduled")
ORDER BY T.course_key, T.login_id, T.refresher;


This returns more records than just the one, but this time it's a faction of
the number of individuals out there. Did I do this one right, or should I
just scrub it?

I'm now going straight to the table as you can see by the above SQL,
[refresher] is still an autonumber field however I noticed something that was
different, and I don't know if it makes any difference or not, but it's also
a primary key field, along with two other fields.

I also tried performing this subquery max format on an altered refresher
field by making it both text and numeric, but either didn't get anywhere or
it just froze the DB up. There are over 40 thousand records in this table.

Is there another way this could be done? Or is there somewhere you would
suggest to research doing subqueries for max values?

The query looks logical to me, but it's unclear where the
status comes into it. Unfortunately, 40K records is too
many to visually verify the query's results.

Is there some way you can operate on a copy of the table
that only has a few dozen representative records? If so, it
would allow you to check the source data to see exactly what
results to expect from the query.

As for your remark about refresher being an autonumber field
that is only part of a compound primary key, I don't see how
that can either make sense or interfere with the query.
It's worth some time thinking about why a unique field would
be part of a compound index though.