Expression to identify the greatest dates for a one-to-many relati

[Guest]

I'm trying to produce a database will be used to track all the contracting
agreements we have with our clients. At the heart of the database are two
tables (tblFarms and tblContracts) which are related one to many
respectively. Each farm may have many contracts but only one of the contracts
for each farm will be current. The current contract can be identified as
having the greatest TermDate (Data Type: Date/Time, Short Date). I need a
query that produces a dataset with a calculated column of True/False value
that identifies the current contract for each farm.

The ultimate aim is to identify those contracts that are current (TermDate
is greatest out all all the contracts for a particular farm), that has
expired (tblContracts.TermDate < Now()) and where we are the current
contractors (tblFarms.FarmCurrent? = True) i.e. the contract and farm are
current but the agreement is out-of-date. This bit I can manage with the
exception of the TermDate = Greatest bit, for this I am asking your help.

I've only been using Access for 2 weeks but what I've managed to produced so
far is already proving useful (I'm also dead chuffed with it and myself)

Thanks in advance,

Russ.

 

[Guest]

**This or something close to it will get you all expired contracts
select tblFarms.farmid, Max(tblFarms.FarmName), tblContracts.ContractID,
Max(tblContracts.TermDate)
from tblFarms left join tblContracts on tblFarms.farmid = tblContracts.farmid
where tblFarms.FarmCurrent = True
Group BY tblFarms.farmid, tblContracts.ContractID
HAVING Max(tblContracts.TermDate) <= Now()

**This or something close to it will get you the most recent expired contracts
select tblFarms.farmid, Max(tblFarms.FarmName),
Max(tblContracts.ContractID), Max(tblContracts.TermDate)
from tblFarms left join tblContracts on tblFarms.farmid = tblContracts.farmid
where tblFarms.FarmCurrent = True
Group BY tblFarms.farmid
HAVING Max(tblContracts.TermDate) <= Now()

**Reverse the test in the HAVING clause to get the flip side

I usually have to play with the syntax of these a bit till I get exactly
what I am looking for.
Besides left join there is right join and outer join
All columns not listed in the Group By clause must have some sort of total
function.
Max(), Min(), Sum(), Count(), etc.

The Where clause looks at individual rows retreived from the database.
The Having clause looks at the total rows.

I'm glad you like Access. If I had a choice I would be using Delphi or
PowerBuilder or even C++ as a better alternative.

 

[Guest]

Russell,

There are a couple of ways to go about this. Assuming that your Contracts
table contains a FarmID field, you could do the following. This will only
give you those contracts that are the most current.

1. SELECT * FROM tblContracts C1
WHERE tblContracts.TermDate = DMAX("TermDate", "tblContracts", "FarmID
= " & C1.FarmID)

Another method, which may be more along the lines that you are looking for
would be to create a query (qryLatestContract) that looks like:

SELECT FarmID, MAX(TermDate) as LatestContractDate
FROM tblContracts
GROUP BY FarmID

Then, use this in a new query as follows.

SELECT tblContracts.*, _
IIF(Isnull(qryLatestContract.LatestContractDate), "No", "Yes") as
LatestContract
FROM tblContracts
LEFT JOIN qryLatestContract
ON tblContracts.FarmID = qryLatestContract.FarmID
AND tblContracts.TermDate = qryLatestContract.LatestContractDate
ORDER BY FarmID, TermDate DES

When you use the LEFT JOIN between tblContracts and qryLatestContract, you
are telling JET to select all of the contracts and match them up with the
data in qryLatestContract, but to display all the values from tblContracts,
even if there is no match in qryLatestContract. The IIF(ISNULL(....))
construct above checks to see if there is a match and if so, identifies that
as the latestcontract for a particular farm.

HTH
Dale

 

[Guest]

Steve and Dale,
Thanks for your help. I used Steve's 2nd method to solve the problem and
tweaked qryLatestContract to pull through some more data that I needed in the
final dataset and the 2nd query to include the other data testing I required.
Its probably a little inelegantly but I guess I'll revisit it once I've got
the whole database app working and I've taught myself more SQL. The other
suggested solutions made Access prompt me for values for various fields, not
sure why.

Seems like I'm on a very steep learning curve.

Thanks,
Russ.