done it!

SELECT Hirer.Hirer_ID, Hirer.Hirer_First_Name, Hirer.Hirer_Surname,
Hirer.Hirer_Address, Hirer.Hirer_DOB, accounts.Account_number,
Account_Type.Account_Type, account_status.Account_Status,
Clients.Client_Name, Agents.Agents_Name, Vehicle.Vehicle_Registration,
hirer.Hirer_Accessed_by
From hirer left outer join Accounts on (hirer.hirer_ID =
Accounts.hirer_ID)
left outer join account_status on (Accounts.account_status_ID =
account_status.account_status_ID)
left outer join Account_Type on (Accounts.Account_Type_ID =
Account_Type.Account_Type_ID)
left outer join Vehicle on (Accounts.Account_ID = Vehicle.Account_ID)
left outer join Clients on (Accounts.Client_ID = Clients.Client_ID)
left outer join Agents on (Accounts.Agents_id = Agents.Agents_id)
join Arrangement on (Accounts.Account_ID = Arrangement.Account_ID)
left join Arrangement AS a1 ON
Arrangement.Arrangement_Entered_into_Date <
a1.Arrangement_Entered_into_Date
AND
Accounts.Account_ID = a1.Account_ID
WHERE a1.Account_ID IS NULL AND Account_Status.Account_Con_Live='1'
AND
Arrangement.Arrangement_Met='0' AND CURDATE() >
Arrangement.Arrangement_Expires_Date