We help IT Professionals succeed at work.
Get Started

Query syntax in Access 2016 from different tables

urjudo asked
Last Modified: 2020-08-11
Hi Experts,
I have a query problem with address that involved with two tables.  one table is Client Address, and other table is Agency Table and the addresses in both tables are base on Client Status.  If Client status is IS the we don't enter the address in Client table, we enter the address in Agency table. The status is on another table ClientTable. Now I have a query need to pull the client address include the agency if the Status is IS, then show the Agency address from the Agency table otherwise show the client address from the Client address table.  Is anyway can do that?

ClientTable: CLStatus = IS

Client addressTable name:    

  example: John Doe    1234 main st     new york   NY   10001     CLStatus: PD   recordID 001
                   Mary Joe Smith       RecordID 002
  (John Doe doesn't have any address in the Agency address table, only in the Client address table)

 agency address table name:

     Example: Mary Smith      PO Box 123        Madison    WI      53965      CLstatus: IS     RecordID 002
     (mary smith doesn't have any address in the Client address table, only in the Agency Address table)

How can I do in the query that show:
CL Name             CLAddress                                       CLCity              ClSt             CLZip
John Doe             1234 Main st                                    New York        NY                10001            recordID 001
Mary Smith          PO box 123  mary Doe Smith       Madison         WI                 53065            recordID 002

 in the same fields: that if ClStatus is IS then show the Agency otherwise show the ClientAddress.  

Here is my Syntax:
CLAddress: IIf([CLStatus] = "IS", [AGYADDR] & ", " & [[Address]![ADRADDR]  but is seems the CLAddress also capture extra data in for the ClStatus IS record if someone enter something on the ClientAddress table (see above) , it will show like the query above, how do I remove the mary doe smith in the field of the query?

Many thanks!
Watch Question
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE