Most Recent Date

[Michael Conroy]

I have a table listing 3,000 legal entity IDs each with multiple contracts
and each contract has a different execution date. The only valid contract is
the most recent one. I need a way to find the maximum date for each entity
and flag it for later use. My thinking was to add a boolean column called
"Valid" and running an update query. The problem is I don't know how to get
the maximum date for each entity. I am worried the max date will be the
maximum value for the entire table rather than the maximum for that entity.
Anyway, the first two columns below represent what I have and the valid
column is what I need. As always, any help would be greatly appreciated.

LEID Execution Valid
001 2/2/2002 False
001 3/3/2003 True
002 4/4/2004 True
003 5/5/2005 False
003 6/6/2006 True

 

[Jeff Boyce]

Michael

Create a new query. Add the table, add the LEIDs, add the ExecutionDate.

Make it a Totals query.

GroupBy LEID, Max on ExecutionDate.

You should get a list of each LEID's maximum ExecutionDate.

Of course, "how" depends on "what" ... my suggest above assumes your data is
well normalized, and the table doesn't have multiple "date" fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

What Jeff did not mention that you cannot use a totals query to update a
table. AND you shouldn't.

Why? because the latest execution date will change over time as LEID
contracts are renewed or instituted. If you calculate the value when
needed it will always be correct - if you calculate and update (it can
be done) then you will usually be correct, depending on how frequently
you update the valid field.

If you feel you REALLY have to update, post back for suggestions on how
to do so.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Michael

Create a new query. Add the table, add the LEIDs, add the ExecutionDate.

Make it a Totals query.

GroupBy LEID, Max on ExecutionDate.

You should get a list of each LEID's maximum ExecutionDate.

Of course, "how" depends on "what" ... my suggest above assumes your data is
well normalized, and the table doesn't have multiple "date" fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GeoffG]

Michael:

Here's a two-stage solution:

STAGE 1:

1. Create a new query in design view.
2. Add your existing table to the design.
3. Drag the LEID and Execution fields to the grid.
4. Click the Totals toolbar button (with the sigma
icon).
A "Total" line appears in the grid.
5. In the LEID column, ensure the Total line says
"Group By".
6. In the Execution column, in the Total line, select
"Max".
7. Open the Query menu and select "Make Table Query".
The Make Table dialog appears.
8. In the dialog, enter "Table2" as the table name,
assuming no existing table has this name.
9. Select the "In Current Database" option.
10. Save the query as "Query1".

If you were to open Query1 in SQL view, you'd see
something like:

SELECT Table1.LEID, Max(Table1.Execution) AS
MaxOfExecution INTO Table2
FROM Table1
GROUP BY Table1.LEID;

The above SQL statement assumes Table1 is your existing
table.

11. Run Query1 to create Table2.

Table2 now contains all the LEIDs and Dates that need
to be marked as True in your existing table.


STAGE 2:

1. Open a new query in Design View.
2. Add your existing table and "Table2" to the
design.
3. Drag the LEID field from your exising table to
the LEID field in Table2.
A line will join the two fields.
4. Drag the Execution field from your exisitng table
to the MaxOfExecution field in Table2.
A line will join the two fields.
You should now have two joining lines.
5. Drag the Valid field from your existing table to
the Design grid.
6. Open the Query menu and select Update Query.
7. In the Valid field column of the design grid, in
the "Update To" row , enter True.
8. Save the query as "Query2"

If you were to open Query2 in SQL View, you'd see
something like this:

UPDATE Table1 INNER JOIN Table2 ON (Table1.LEID =
Table2.LEID) AND (Table1.Execution =
Table2.MaxOfExecution) SET Table1.Valid = True;

The above SQL statement assumes Table1 is your existing
table.

9. Run Query2.

The appropriate records should now be marked as True.

Delete Table2.
Regards
Geoff




"Michael Conroy"
message

 

[Michael Conroy]

Gentlemen,
Thank you all for the responses, good advise in each. Normally I would agree
with you John that a dynamic query is preferred, however, this situation is
different. I am going from a many to one relationship. I am grabbing
information from a linked spreadsheet and creating normalized tables. So
instead of having a legal entity listed five times because there are five
contracts, I need a table with one legal entitiy ID, then I need the
information from the most recent contract to populate various fields in the
new table.

Taking the suggestion to use a sum query, (which I didn't consider because I
was using dates, I know they are numbers, but I forgot) I was trying to use
it as a subquery as the criteria for the update query, like this:

UPDATE [tbl Linker] INNER JOIN [tbl Library] ON [tbl Linker].GoldID = [tbl
Library].GoldID SET [tbl Linker].Max = [tbl Library].[Execution]
WHERE (SELECT [tbl Library].GoldID, Max([tbl Library].Execution) AS
MaxOfExecution
FROM [tbl Library]
GROUP BY [tbl Library].GoldID
HAVING (((Max([tbl Library].Execution)) Is Not Null)));

The Where clause was copied from the sum query, but I am getting an error
that says the "subquery returns more than one field without using the EXISTS
word in the FROM clause or the main query. Revise the SELECT statement of the
subquery to request only one field." Excuse me, that's an error message I
have never seen before. Well I read it again and tried the HAVING clause, but
that did not work. I tried adding DISTINCTVALUE to the subquery, again, no
joy. What is this message talking about? Why does it suggest I modify the
subquery when the error is in the main query? Isn't it possible to use a
subquery like this? Is what I am trying to do possible?

My current workaround is to use the sum query as the source for a make table
query. Then I use the table as the source for the update query. It works, but
is seems rediculous. Can't I use the sum query as the criteria for the update
query?

--
Michael Conroy
Stamford, CT

What Jeff did not mention that you cannot use a totals query to update a
table. AND you shouldn't.

Why? because the latest execution date will change over time as LEID
contracts are renewed or instituted. If you calculate the value when
needed it will always be correct - if you calculate and update (it can
be done) then you will usually be correct, depending on how frequently
you update the valid field.

If you feel you REALLY have to update, post back for suggestions on how
to do so.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

You are probably doing this the most efficient way.

Although you can use the DMax Function in an update query. Assuming that
GoldID is a number field.

UPDATE [tbl Linker]
Set [tbl Linker].Max = DMax("Execution","[tbl Library]","GoldID=" & GoldID)

As far as the error in your subquery -
In a WHERE clause or SELECT clause a sub query can return results for only one
field. The subquery in your where clause is trying to return GoldID AND
MaxOfExecution (two fields).

Aside from that I am not sure what you were attempting to accomplish in that
subquery.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Gentlemen,

Taking the suggestion to use a sum query, (which I didn't consider because I
was using dates, I know they are numbers, but I forgot) I was trying to use
it as a subquery as the criteria for the update query, like this:

UPDATE [tbl Linker] INNER JOIN [tbl Library] ON [tbl Linker].GoldID = [tbl
Library].GoldID SET [tbl Linker].Max = [tbl Library].[Execution]
WHERE (SELECT [tbl Library].GoldID, Max([tbl Library].Execution) AS
MaxOfExecution
FROM [tbl Library]
GROUP BY [tbl Library].GoldID
HAVING (((Max([tbl Library].Execution)) Is Not Null)));

 

[Michael Conroy]

John,
Thanks, the table stuff is more steps but it is cleaner and I can follow the
logic easier. However, I would like to sort out the other way of doing it for
future reference.

Regarding the DMax option, GoldID is a text field and a primary key in the
target table [tbl Linker]. I added some quotes around the GoldID field, but
now I am getting the highest date in the entire table put into every legal
entity record.

UPDATE [tbl Linker] SET [tbl Linker].Max = DMax("Execution","[tbl
Library]","GoldID=" & "GoldID");

tbl Linker tbl Library
GoldID Max GoldID Execution
A001 06/07/08 A001 04/05/08
A002 08/08/08 A001 06/07/08
B002 09/08/07
B002 08/08/08

The maximum date for each GoldID has to be associated with the unique list
of GoldIDs in the linker table. What I am getting now is 08/08/08 for all
records in Linker because it is the maximum date in Library. If what I am
asking can't be done this way, just let me know, but I wanted to be sure the
objective was understood. Thanks.

--
Michael Conroy
Stamford, CT

You are probably doing this the most efficient way.

Although you can use the DMax Function in an update query. Assuming that
GoldID is a number field.

UPDATE [tbl Linker]
Set [tbl Linker].Max = DMax("Execution","[tbl Library]","GoldID=" & GoldID)

As far as the error in your subquery -
In a WHERE clause or SELECT clause a sub query can return results for only one
field. The subquery in your where clause is trying to return GoldID AND
MaxOfExecution (two fields).

Aside from that I am not sure what you were attempting to accomplish in that
subquery.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Gentlemen,

Taking the suggestion to use a sum query, (which I didn't consider because I
was using dates, I know they are numbers, but I forgot) I was trying to use
it as a subquery as the criteria for the update query, like this:

UPDATE [tbl Linker] INNER JOIN [tbl Library] ON [tbl Linker].GoldID = [tbl
Library].GoldID SET [tbl Linker].Max = [tbl Library].[Execution]
WHERE (SELECT [tbl Library].GoldID, Max([tbl Library].Execution) AS
MaxOfExecution
FROM [tbl Library]
GROUP BY [tbl Library].GoldID
HAVING (((Max([tbl Library].Execution)) Is Not Null)));

 

[John Spencer]

You were close. You did need to add quotes, you just got them in the wrong place.


UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID=""" & GoldID & """");

Or if GOLDID never has an apostrophe in it

UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID='" & GoldID & "'");



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Michael Conroy]

John,
That did it, thanks a lot. I realize aggregate formulas are not preferred,
but it saves a lot of queries and temporary tables in this case. You should
have seen one concatenation I had, so close.

"GoldID=""" & GoldID & "");"

Thanks again for all your efforts.
--
Michael Conroy
Stamford, CT

You were close. You did need to add quotes, you just got them in the wrong place.


UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID=""" & GoldID & """");

Or if GOLDID never has an apostrophe in it

UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID='" & GoldID & "'");



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,
Thanks, the table stuff is more steps but it is cleaner and I can follow the
logic easier. However, I would like to sort out the other way of doing it for
future reference.

Regarding the DMax option, GoldID is a text field and a primary key in the
target table [tbl Linker]. I added some quotes around the GoldID field, but
now I am getting the highest date in the entire table put into every legal
entity record.

UPDATE [tbl Linker] SET [tbl Linker].Max = DMax("Execution","[tbl
Library]","GoldID=" & "GoldID");

tbl Linker tbl Library
GoldID Max GoldID Execution
A001 06/07/08 A001 04/05/08
A002 08/08/08 A001 06/07/08
B002 09/08/07
B002 08/08/08

The maximum date for each GoldID has to be associated with the unique list
of GoldIDs in the linker table. What I am getting now is 08/08/08 for all
records in Linker because it is the maximum date in Library. If what I am
asking can't be done this way, just let me know, but I wanted to be sure the
objective was understood. Thanks.

 

[Michael Conroy]

I hate to bother you again, but I am using this code in a form module and the
quotes are raising a problem when I set it to a string variable.

strSQL = "UPDATE [tbl Linker] " & _
"SET [tbl Linker].Max = DMax("Execution","[tbl Library]","GoldID = """ &
GoldID & """");"

All I did was add a quote at the end to cover the semicolon and removed one
from in front of the right paren. Now it says it's looking for and end of
statement at Execution so I put double quotes around the DMax options.

strSQL = "UPDATE [tbl Linker] " & _
"SET [tbl Linker].Max = DMax(""Execution"",""[tbl Library]"",""GoldID = """
& GoldID & """);"

Then it said the GoldID variable was not defined so I set it as a string and
it executed but the results were null. This is driving me nuts. Your code
worked in a query but I can't get it into my strSQL line to run. I hate to
ask for more help but could you take another look at this. Thanks
--
Michael Conroy
Stamford, CT

You were close. You did need to add quotes, you just got them in the wrong place.


UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID=""" & GoldID & """");

Or if GOLDID never has an apostrophe in it

UPDATE [tbl Linker]
SET [tbl Linker].Max =
DMax("Execution","[tbl Library]","GoldID='" & GoldID & "'");



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,
Thanks, the table stuff is more steps but it is cleaner and I can follow the
logic easier. However, I would like to sort out the other way of doing it for
future reference.

Regarding the DMax option, GoldID is a text field and a primary key in the
target table [tbl Linker]. I added some quotes around the GoldID field, but
now I am getting the highest date in the entire table put into every legal
entity record.

UPDATE [tbl Linker] SET [tbl Linker].Max = DMax("Execution","[tbl
Library]","GoldID=" & "GoldID");

tbl Linker tbl Library
GoldID Max GoldID Execution
A001 06/07/08 A001 04/05/08
A002 08/08/08 A001 06/07/08
B002 09/08/07
B002 08/08/08

The maximum date for each GoldID has to be associated with the unique list
of GoldIDs in the linker table. What I am getting now is 08/08/08 for all
records in Linker because it is the maximum date in Library. If what I am
asking can't be done this way, just let me know, but I wanted to be sure the
objective was understood. Thanks.

 

[John Spencer]

Oh yes, quotes inside a string need to be doubled. That gets TOUGH to do
sometimes. I often have to experiment to get it all correct.

I THINK the following is correct, but I may have messed it up.

strSQL = "UPDATE [tbl Linker] " & _
"SET [tbl Linker].Max = " & _
"DMax(""Execution"",""[tbl Library]"",""GoldID = """ &
GoldID & """)"


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================