Selecting the hghest value in a one-to-many relationship

[JWCrosby]

I have a table (tblTransactions) set up in a one-to-many relationship with
another table (tblHistory).

tblHistory has the following fields:
LinkField (text field that is the link to tblTransactions)
Year (number, e.g., 2007, 2006, 2004, etc.)
Grade (text, e.g., “Sâ€, “Eâ€, “Iâ€, etc.)
Class (text, e.g., “Medâ€, “PPâ€, “P1â€, etc.)

I have a form that displays several fields in tblTransactions. I want to
create a subform that shows only the most recent Year’s data in tblHistory.
Seems the Max function would be needed, but I’m having trouble writing the
query to give me that information and would appreciate any help.

Thanks in advance.

Jerry

 

[Golfinray]

Insted of using Max, in the query that runs the subform put as the criteria
Between 1/1/2007 and 1/1/2008 or what ever dates you want.

 

[JWCrosby]

Sorry, but that won't work (well, it would work, but it wouldn't give me what
I need). The year field is just a 4-digit number, it's not formatted as a
date. For some students (the subject behind the tblTransactions table) their
most recent year in the history table may be 2006, or 2005, or 2007. I need
to select the most recent year for which they have a history record, which
would be the greatest value in the year field.

That clarify any?

Jerry

 

[JWCrosby]

This may be a duplicate response; my browser burped when submitting this the
first time.

I don't believe your suggestion will give me what I want. The year field is
just a 4-digit number field and is not formatted as a number. Every student
with a record in tblTransactions can have many records in tblHistory, one for
each year they participated in the program (with one of the fields being the
year field). I want the subform to show the information for the most recent
year of participation. For some, that year might be 2007, or it could be
2006, or maybe even 1999. It will vary. Seems to me the Max function is
what I need somehow.

Maybe that clarifies it some.

 

[Michael Gramelspacher]

I have a table (tblTransactions) set up in a one-to-many relationship with
another table (tblHistory).

tblHistory has the following fields:
LinkField (text field that is the link to tblTransactions)
Year (number, e.g., 2007, 2006, 2004, etc.)
Grade (text, e.g., “S”, “E”, “I”, etc.)
Class (text, e.g., “Med”, “PP”, “P1”, etc.)

I have a form that displays several fields in tblTransactions. I want to
create a subform that shows only the most recent Year’s data in tblHistory.
Seems the Max function would be needed, but I’m having trouble writing the
query to give me that information and would appreciate any help.

Thanks in advance.

Jerry

Create a textbox in the main form with a default value of
=DMax("[Year]","tblHistory") and if you wish, may it a unbound combo box with a
row source of SELECT DISTINCT tblHistory.Year
FROM tblHistory;

the main form is linked to the subform via
lLink Child Fields: LinkField; Year
Link Master Fields: LinkField,Text1 (or whatever you named the text or combo
box)

L

 

[John W. Vinson]

I have a table (tblTransactions) set up in a one-to-many relationship with
another table (tblHistory).

tblHistory has the following fields:
LinkField (text field that is the link to tblTransactions)
Year (number, e.g., 2007, 2006, 2004, etc.)
Grade (text, e.g., “S”, “E”, “I”, etc.)
Class (text, e.g., “Med”, “PP”, “P1”, etc.)

I have a form that displays several fields in tblTransactions. I want to
create a subform that shows only the most recent Year’s data in tblHistory.
Seems the Max function would be needed, but I’m having trouble writing the
query to give me that information and would appreciate any help.

Thanks in advance.

Jerry

One way that might help is to actually have all the years in the subform, but
just display the most recent (in a single-form view subform), or the most
recent on top (in a continuous or datasheet subform). To do so just sort the
subform's recordsource descending by year.