Hello, I've got some tables like this, and i can't get the query:

tblPlace
-------------
PlaceID
OwnerID
Name
Address
InsertDate

tblContracts
---------------
ContractID
PlaceID
RentPersonID
StartDate
EndDate

The Query should return:

tblPlace.PlaceID
Status (If exists a contract that is not over status is Ocupied, if not is Open)

One of this:
tblPlace.InsertDate (if doen't exist any contract)
tblContract.StartDate (if contract is not over)
tblContract.EndDate (if contract is over)

tblContract.RentPesonID (and the name is tblRentPeson)

Thanks for any info...

SELECT PlaceID, IIF([tblContracts].[EndDate]<Now(),"Ocupied", "Open") AS [Status], Min([tblPlace].[InsertDate]) As [Insert], Min([tblContract].[StartDate]) As [Start], Min([tblPlace].[EndDate]) As [End], tblContract.RentPersonID FROM tblContracts GROUP BY PlaceID, IIF([tblContracts].[EndDate}<Now(),"Ocupied", "Open")


(I haven't tested this, so there may be a typo somewhere.


Steven


I am a loud man with a very large hat. This means I am in charge
Of course - there was an obvious typo

Hi! I try using such statement but can only get three column(placeID, status and date)

SELECT tblContracts.PlaceID,
IIf(Max([tblContracts].[EndDate])>=Now(),'Occupied','Open') AS Status,
IIf(status='Open',Max([tblcontracts].[endDate]),Max([tblcontracts].[startdate])) AS dDate
FROM tblContracts
GROUP BY tblContracts.PlaceID;
Union
SELECT tblPlace.PlaceID, 'Open' AS Status, tblPlace.InsertDate AS dDate
FROM tblContracts RIGHT JOIN tblPlace ON tblContracts.PlaceID = tblPlace.PlaceID
WHERE (((tblContracts.PlaceID) Is Null));


can anyone pls kind tell me how to get the results showing rentpersonID as one of the result?

thanks!

snowbelly.