Today I was splitting an address out of a table in order to replace the functionality of a single embedded address to multiple addresses. I had a Job table with the fields:

Job Table fields: Job_ID, Address, city_job, state_job, zip_job

And I am going to be removing those and replacing with a new table:

Job Addresses Table fields: JobAddress_ID, Job_ID, Street, City, State, Zip, AddressActive

Part of the requirement is to automatically populate a Purchase Order for that job with the address if there is only ONE address for the job.

I already had the address formatted the way I wanted it in a dropdown on the Purchase Order form, and it already populates the address with that, but it should NOT populate anything if there is more than one address.

So by modifying the query in the rowsource of the dropdown, I was able to only populate my row source with an address if there was exactly one address, otherwise it leaves it blank.

I did this with a subquery using SQL GROUP BY and HAVING clauses. Here’s what my sub-query looks like:

SELECT
  Job_ID,
  First(Street) & ', ' & First(City) & ', ' & 
    First(State) & '  ' & First(Zip) AS [Job Address],
  Count(*)
FROM
  job_addresses
WHERE
  AddressActive=True
GROUP BY
  Job_ID
HAVING
  Count(*) = 1

From there I was able to use a LEFT JOIN to insert it into the existing query joining all records from Job Details on only the records where the subquery Job_ID existed. Other Job Detail records would have a blank [Job Address] if it had no addresses defined or more than 1 address defined.