Is there anything wrong with the subquery ?

[Robert]

Hi,

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

I have created an aggregate function to select the Premises 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 AS it is an aggregate function. In this way, I have
changed to using subquery.

To my disappointment, the query only returns only 1 row - Premises
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 ?

 

[Pieter Wijnen]

You get what you ask for...
WHERE tblLeases.[Lease Expiry] = (SELECT (MAX([Lease Expiry])) from
tblLeases)
Will restrict to 1 record... (or only leases expiring on the last date
entered)

alas MS Access does not support multiple fields in sub-queries (at least not
in older releases)
simplest thing to do would be to make a max query containing
SELECT A.PremisesID, MAX( A.[Lease Expiry]) AS MaxExpiry FROM tblLeases A
GROUP by A.PremisesID

You can then Use a WHERE EXISTS Clause to Join to your query

SELECT P.[Record ID], P.[Expenditure Allocation Code],
P.[Premises Address], L.[Sub-Number],
L.[Lessee Details], L.[Phone Number 1], L.[Phone Contact Person 1],
L.[Account Number], L.[Gross Rent Per Annum],
L.[Lease Expiry], L.Insurance, L.Comments
FROM tblPremises P INNER JOIN tblLeases L ON P.[Record ID] =
L.PremisesID
WHERE EXISTS (SELECT 'X' FROM qryMaxLeasePerPremise M
WHERE M.PremisesID=L.PremisesID AND M.MaxExpiry =L.[Lease Expiry])


HTH

Pieter

 

[Robert]

Dear Pieter,

You are right, what you mention is what I am after.

I have created a MaxQuery as follow
SELECT tblLeases.PremisesID, Max(tblLeases.[Lease Expiry]) AS
[MaxOfLeaseExpiry]
FROM tblLeases GROUP BY tblLeases.PremisesID;

I have also created the required query as follow BUT
1) It doesn't return records with Lease Expiry is NULL
2) It is still not editable

SELECT tblPremises.[Record ID],
tblPremises.Category, tblPremises.[Premises Address], tblLeases.[Lessee
Details], tblLeases.[Legal Descriptor], tblLeases.[Phone
Number 1], tblLeases.[Phone Contact Person 1], tblLeases.[Phone Number 2],
tblLeases.[Phone Contact Person 2], tblLeases.[Fax Number],
tblLeases.[Account Number], tblLeases.[Gross Rent Per Annum],
tblLeases.[Payment Interval], tblLeases.[Payment Amount], tblLeases.[Lease
Commence], tblLeases.[Lease Expiry], tblLeases.[Option Date 1],
tblLeases.[Payment of Outgoings], tblLeases.Class, tblLeases.Amount,
tblLeases.Insurance, tblLeases.[Special Conditions], tblLeases.Comments
FROM MaxQuery INNER JOIN (tblPremises INNER JOIN tblLeases ON
tblPremises.[Record ID] = tblLeases.PremisesID) ON MaxQuery.PremisesID =
tblLeases.PremisesID
WHERE (((tblLeases.[Lease Expiry])=[MaxQuery]![MaxOfLease Expiry]))
ORDER BY tblPremises.[Record ID];

I am using Access 97. Would it be a problem ?

Robert

You get what you ask for...
WHERE tblLeases.[Lease Expiry] = (SELECT (MAX([Lease Expiry])) from
tblLeases)
Will restrict to 1 record... (or only leases expiring on the last date
entered)

alas MS Access does not support multiple fields in sub-queries (at least
not in older releases)
simplest thing to do would be to make a max query containing
SELECT A.PremisesID, MAX( A.[Lease Expiry]) AS MaxExpiry FROM tblLeases A
GROUP by A.PremisesID

You can then Use a WHERE EXISTS Clause to Join to your query

SELECT P.[Record ID], P.[Expenditure Allocation Code],
P.[Premises Address], L.[Sub-Number],
L.[Lessee Details], L.[Phone Number 1], L.[Phone Contact Person 1],
L.[Account Number], L.[Gross Rent Per Annum],
L.[Lease Expiry], L.Insurance, L.Comments
FROM tblPremises P INNER JOIN tblLeases L ON P.[Record ID] =
L.PremisesID
WHERE EXISTS (SELECT 'X' FROM qryMaxLeasePerPremise M
WHERE M.PremisesID=L.PremisesID AND M.MaxExpiry =L.[Lease Expiry])


HTH

Pieter

Hi,

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

I have created an aggregate function to select the Premises 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 AS it is an aggregate function. In this way, I have
changed to using subquery.

To my disappointment, the query only returns only 1 row - Premises
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 ?