If you use criteria on any field in the table on the outer side of the join,
you are limiting the result to the same as an inner join.
The simplest solution might be to stack 2 queries.
Create a query that returns the address fields you want, but only for the
current address:
SELECT offender_ID, Address, State, Zip FROM address WHERE
address.add_current = True
Then use that query as in input "table" for another query. With the outer
join, it should return all offenders, and only with their current addresses.
If one offender can have multiple current addresses, use GROUP BY in the
first query so it returns only one.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:vs******************************@comcast.com. ..
The following SQL does as I intend, but ...
SELECT offender_id, off_name, inv_ppo_id,
add_note, add_zip_id, it_name AS Purpose
FROM (offender INNER JOIN (investigation
INNER JOIN lu_investigation_type
ON investigation.inv_action_id =
lu_investigation_type.investigation_type_id)
ON offender.offender_id = investigation.inv_off_id)
LEFT JOIN address ON offender.offender_id = address.add_off_id
WHERE inv_ppo_id=[ID] AND inv_complete_date Is Null
When I add this AND clause, I only get the records that have a current
address; I no longer get all the records.
AND address.add_current=True;
What I want is all the records for the offenders - I just don't want
duplicates for more than one address.
How do I get this?
--
Darryl Kerkeslager
