How to edit data in a aggregrate query ?

[Robert]

There is a database that forms a 1:M relationship for Equipment and Lease
Details. In the Lease entity, it keeps the history of the leasee and expiry
date.

I have created an aggregate function to select the Equipment Details with
the latest Lease Date (By using MAX function).

However, I link the Equipment and Lease tables to the Access Front End and
add the aforementioned query as well. I find that the query result is not
editable (I believe that it is because it is the result of aggregate
function). However, end user wants to edit the query result. Is there any
better way to handle it ?

Thanks

 

[Brendan Reynolds]

Aggregate queries are never editable, but you may be able to replace the
aggregate query with a query that uses a sub-query. Something like ...

SELECT EquipmentDetails FROM Equipment INNER JOIN LeaseDetails ON
Equipment.EquipmentID = LeaseDetails.EquipmentID WHERE
LeaseDetails.LeaseDate = (SELECT MAX(LeaseDate) FROM LeaseDetails)

 

[Robert]

Dear Brendan,

Thank you for your advice. I will try it next Monday.

Robert

 

[Robert]

Dear Breadan,

I have followed your suggestion but it only returns only 1 row - Equipment
with Lease information with the largest "Lease Expiry" date.

The SQL is as follow:
SELECT tblPremises.[Record ID], tblPremises.[Expenditure Allocation Code],
, tblPremises.[Premises Address], tblLeases.[Sub-Number],
tblLeases.[Lessee Details], tblLeases.[Phone Number 1], tblLeases.[Phone
Contact Person 1],
tblLeases.[Account Number], tblLeases.[Gross Rent Per Annum],
tblLeases.[Lease Expiry], tblLeases.Insurance, tblLeases.Comments
FROM tblPremises INNER JOIN tblLeases ON tblPremises.[Record ID] =
tblLeases.PremisesID
WHERE tblLeases.[Lease Expiry] = (SELECT (MAX([Lease Expiry])) from
tblLeases)

Is there anything wrong ?

Robert

 

[Dirk Goldgar]

Dear Breadan,

I have followed your suggestion but it only returns only 1 row -
Equipment with Lease information with the largest "Lease Expiry" date.

That sounds like what you asked for: "select the Equipment Details with
the latest Lease Date". But I'm guessing that what you want is every
record from tblPremises joined to the latest record from tblLeases for
that PremisesID. Try this:

SELECT
tblPremises.[Record ID],
tblPremises.[Expenditure Allocation Code],
tblPremises.[Premises Address],
tblLeases.[Sub-Number],
tblLeases.[Lessee Details],
tblLeases.[Phone Number 1],
tblLeases.[Phone Contact Person 1],
tblLeases.[Account Number],
tblLeases.[Gross Rent Per Annum],
tblLeases.[Lease Expiry],
tblLeases.Insurance,
tblLeases.Comments
FROM
tblPremises
INNER JOIN
tblLeases
ON tblPremises.[Record ID] = tblLeases.PremisesID
WHERE
tblLeases.[Lease Expiry] =
(SELECT MAX([Lease Expiry]) from tblLeases T
WHERE T.PremisesID = tblPremises.[Record ID])

 

[Robert]

Dear Dirk,

I am sorry that the query is still returning 1 row. I am using Access 97,
is it the problem ?

Robert

Dear Breadan,

I have followed your suggestion but it only returns only 1 row -
Equipment with Lease information with the largest "Lease Expiry" date.

That sounds like what you asked for: "select the Equipment Details with
the latest Lease Date". But I'm guessing that what you want is every
record from tblPremises joined to the latest record from tblLeases for
that PremisesID. Try this:

SELECT
tblPremises.[Record ID],
tblPremises.[Expenditure Allocation Code],
tblPremises.[Premises Address],
tblLeases.[Sub-Number],
tblLeases.[Lessee Details],
tblLeases.[Phone Number 1],
tblLeases.[Phone Contact Person 1],
tblLeases.[Account Number],
tblLeases.[Gross Rent Per Annum],
tblLeases.[Lease Expiry],
tblLeases.Insurance,
tblLeases.Comments
FROM
tblPremises
INNER JOIN
tblLeases
ON tblPremises.[Record ID] = tblLeases.PremisesID
WHERE
tblLeases.[Lease Expiry] =
(SELECT MAX([Lease Expiry]) from tblLeases T
WHERE T.PremisesID = tblPremises.[Record ID])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dear Dirk,

I am sorry that the query is still returning 1 row. I am using
Access 97, is it the problem ?

No, I made a mistake. Try something like this:

SELECT
tblPremises.[Record ID],
tblPremises.[Expenditure Allocation Code],
tblPremises.[Premises Address],
tblLeases.LeaseID,
tblLeases.[Sub-Number],
tblLeases.[Lessee Details],
tblLeases.[Phone Number 1],
tblLeases.[Phone Contact Person 1],
tblLeases.[Account Number],
tblLeases.[Gross Rent Per Annum],
tblLeases.[Lease Expiry],
tblLeases.Insurance,
tblLeases.Comments
FROM
tblPremises
INNER JOIN
tblLeases
ON tblPremises.[Record ID] = tblLeases.PremisesID
WHERE
tblLeases.LeaseID In
(
SELECT
tblLeases.LeaseID
FROM
(
SELECT
tblLeases.PremisesID,
Max(tblLeases.[Lease Expiry]) AS LastDate
FROM
tblLeases
GROUP BY tblLeases.LeaseID
) AS M
INNER JOIN
tblLeases
ON
(M.LastDate = tblLeases.[Lease Expiry]) AND
(M.PremisesID = tblLeases.PremisesID)
);

Please note that I have assumed a primary key field named "LeaseID" for
tblLeases. The table *must* have a primary key, for this query to work,
but it may be named something else. I looked back through the earlier
messages for one that identified that table's primary key, but I
couldn't find one. Naturally, you should change "LeaseID" if necessary
to the correct field name. If that field is among the other fields
already mentioned in the query, you don't need to include it twice in
the query results; however, you probably do need to include it in the
query results in order for the query to be updatable.

I may have made other mistakes in the above SQL, but a similar query
worked for me in my test.