In my Access database, I have three tables, as follows:

Tracts - information about tracts.  Each tract has a key.
Leases - information about leases.  Each lease has a key.

In addition, I have an Ownership table, which contains records linking
Tracts and Leases.  Not all Tracts have a record in the Ownership
table, and some (most) tracts have more than one.

What I want is to create something like an update query that will plug
information into a field in the tract table.  The information I want
to plug in is this (sorry, this gets a little complex): for the
ownership records connected to that tract, look up each lease, and in
the Leases table, find their expiration dates, and what is the
earliest one?

In other words, if I had this information:

Tracts Table:

tract id    tract size     earliest exp date
--------------------------------------------
1               450           
2               300
3               240

Leases Table:

lease id    expiration date
---------------------------
a                 01/02/2003
b                 04/06/2004
c                 12/31/2002


Ownership Table:

ownership key     tract id      lease id     percentage
-------------------------------------------------------
1                     1             a            20
2                     1             b            40
3                     2             a            10
4                     2             c            90

...then I want the result of my query to be making the Tracts Table
like this:

tract id    tract size     earliest exp date
--------------------------------------------
1               450           01/02/2003
2               300           12/31/2002
3               240           

Please ask for clarification if this makes sense.  Obviously this is a
simplification of the actual data I'm working with.

By the way, I have experience with select and update queries, and can
read SQL but not generally generate it.  I'm reasonably
computery/logical/technical, though.

Thanks in advance!

Tam

---

Clarification of Question by tamkins-ga on 14 Feb 2003 14:25 PST

Um, I did mean "Please ask for clarification if this does NOT make sense."  Heh.

---

Request for Question Clarification by hammer-ga on 14 Feb 2003 15:50 PST

If the tract record already has an expiration date, do you want it
overwritten if an earlier date is found? Or do you only wnat to affect
tract records where expiration date is blank?

- Hammer

---

Request for Question Clarification by hammer-ga on 14 Feb 2003 16:16 PST

Also, what version of Access?

- Hammer

---

Clarification of Question by tamkins-ga on 19 Feb 2003 08:03 PST

I want it to overwrite where there is already an expiration date.

Also, I'm using Access 2000. 

More than accomplishing this specific goal, I am using this problem as
an example so that I can learn to do this general kind of thing, which
I need to do a lot of different kinds of.  I may post more questions
along the same lines later, if I can't figure out the general pattern.

Thanks again, and sorry I took so long to clarify.

Paste this into a new module and save. When you run the routine, it
will set each expriation date in tblTracts to the earliest found
expiration date. I'll be out of town for a few days, but I'll answer
any clarification requests when I get back.

Note: The Answer box may wrap lines. Unwrap them after pasting them
into Access.

- Hammer

------------------------------------

Option Compare Database
Option Explicit

Public Sub SetExpirationDate()
Dim cnn As ADODB.Connection
Dim rstDates As ADODB.Recordset
Dim strSQL As String
 
    Set cnn = New ADODB.Connection
    Set cnn = CurrentProject.Connection
    Set rstDates = New ADODB.Recordset
     
    ' Open a recordset with the earliest expiration date for each
tract id
    strSQL = "SELECT [tblOwnership].[tract id],
Min([tblLeases].[expiration date]) AS EarliestDate " & _
        "FROM tblOwnership " & _
        "INNER JOIN tblLeases ON [tblOwnership].[lease
id]=[tblLeases].[lease id] " & _
        "GROUP BY [tblOwnership].[tract id] " & _
        "ORDER BY [tblOwnership].[tract id];"
    rstDates.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    If Not rstDates.BOF And Not rstDates.EOF Then
        rstDates.MoveFirst
        Do Until rstDates.EOF
            cnn.Execute "UPDATE tblTracts SET tblTracts.[earliest exp
date] = #" & rstDates!EarliestDate & "# WHERE tblTracts.[tract id] = "
& rstDates![tract id] & ";"
            rstDates.MoveNext
        Loop
    End If
    
End Sub

---

Request for Answer Clarification by tamkins-ga on 20 Feb 2003 15:50 PST

Hammer, could you post that answer again, with more comments in the
code?  I need to understand it better so that I'll know how to
implement similar things in the future.  I'll be happy to tip for the
extra time.  (I do have a little VB coding experience, but not in
Access.)

---

Clarification of Answer by hammer-ga on 23 Feb 2003 08:13 PST

No problem! It also gives me an opportunity to add the section I left
out about cleaning up at the end.

Here is the commented module:

Option Compare Database
Option Explicit

Public Sub SetExpirationDate()
' This routine opens a recordset. The recordset contains one
' record for each tract. Each record contains the tract id and
' the earliest expiration date available for that tract. Once
' we have that information, we loop through the recordset
' setting the earliest exp date field in tblTracts.

' We will use ADO recordsets (the recommended choice for
' Access 2000), so we need to declare an ADO Connection and
' an ADO Recordset
Dim cnn As ADODB.Connection
Dim rstDates As ADODB.Recordset

' We also need a string variable.
Dim strSQL As String
 
    ' Set the ADO variables using New, because it's more
    ' efficient that way
    Set cnn = New ADODB.Connection
    Set rstDates = New ADODB.Recordset
    ' Use the same connection as our current database
    ' uses anyway
    Set cnn = CurrentProject.Connection
     
    ' Open a recordset with the earliest expiration date for each
tract id
    ' The string being assigned to strSQL is a query. You can
duplicate this
    ' query in Query Design View. It is a Totals query. This means
that, instead
    ' of showing every record, it shows a summary of the records. In
this case,
    ' we have told it to show only the lowest (Min) expiration date
for each tract.
    strSQL = "SELECT [tblOwnership].[tract id],
Min([tblLeases].[expiration date]) AS EarliestDate " & _
        "FROM tblOwnership " & _
        "INNER JOIN tblLeases ON [tblOwnership].[lease
id]=[tblLeases].[lease id] " & _
        "GROUP BY [tblOwnership].[tract id] " & _
        "ORDER BY [tblOwnership].[tract id];"
    
    ' Run the query specified above.
    rstDates.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    
    ' Check for BOF (beginning of file) and EOF (end of file) to
    ' prevent an error if the query returns no records.
    If Not rstDates.BOF And Not rstDates.EOF Then
        ' Move to the first record in the recordset
        rstDates.MoveFirst
        ' Loop until we have done all the records
        Do Until rstDates.EOF
            ' For each record, run an UPDATE query that finds the
record in
            ' tblTracts that matches the tract id in this record, and
sets
            ' earliest exp date to the expiration date in this record.
            cnn.Execute "UPDATE tblTracts SET tblTracts.[earliest exp
date] = #" & rstDates!EarliestDate & "# WHERE tblTracts.[tract id] = "
& rstDates![tract id] & ";"
            ' Move to the next record.
            rstDates.MoveNext
        Loop
    End If
    
    ' Clean up our variables, so we do not leak memory
    rstDates.Close
    Set rstDates = Nothing
    Set cnn = Nothing
    strSQL = ""
    
End Sub

-----------------

Here is the query that we are running to get the earliest expiration
date for each tract. You can paste it into the Query Designer and run
it to see what it produces.

SELECT [tblOwnership].[tract id], Min([tblLeases].[expiration date])
AS EarliestDate
FROM tblOwnership INNER JOIN tblLeases ON [tblOwnership].[lease
id]=[tblLeases].[lease id]
GROUP BY [tblOwnership].[tract id]
ORDER BY [tblOwnership].[tract id];

-----------------------------
Additional Resources:

MVPS Access FAQ
Use Variables in SQL behind forms
http://www.mvps.org/access/forms/frm0001.htm


Microsoft Knowledge Base
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrworkingwithrecords.asp
This is the beginning of one section of the relevant information. To
be honest, I've found Access Help and MSKB to be utterly useless at
learning how to do this. It is so cluttered with irrelevant technical
information, it is very difficult to use. You may want to buy a good
Access tutorial. Go to the computer bookstore and flip through the
available beginning Access books until you find one that makes sense
to you.

Please let me know if you need further clarification.

- Hammer

Thank you so much :-)  Awesome!